SQL view shows different result in SSMS and Access

[Zakraket]

I have a problem with an Accessclient for which I am migrating the backend to SQL-server. The database contains contact-data amongst other, in particular a table "contacts" (relaties) and a table "contact_adresses" (relatie_adressen) in which for a contact multiple adresses are stored

relatieID  naam
1          Jan
2          Piet

AdresID RelatieID AdresType
1       1         StreetX
2       1         PostboxY

I have this view in SQL-server:

SELECT RelatieID, Naam, AdresType FROM relaties
LEFT OUTER JOIN relatie_adressen on relaties.relatieID = relatie_adressen.relatieID

A simple left join to display all contacts and all adresses for all contacts if they exist.

The view when opened in SSMS displays
1 Jan StreetX
1 Jan PostboxY
2 Piet null

When I link the view to Access the linked view displays
1 Jan StreetX
1 Jan StreetX
2 Piet null

How is it possible that Access displays different results than the view on SQL-server retrieves (and sends to Access...)??

Of course, when I create the same query in the Accessclient on linked tables I get the same result as SSMS, but then it becomes extremely slow (100x slower) on the dataset (>100.000 contacts) so that is not an option.
Goal of migrating to SQL was to speed up the client.

The view (and separate tables) are connected via 32-bit ODBC (SQL Server Native Client 11 driver), and the problem is the same on my laptop (SQL2008r2, Access2013) as on the production/testenvironment (SQL2012, Access 2010)

 

[JHB]

It is not solving you problem, but what happen if you include the "AdresID" in the select statement?

 

[Zakraket]

I solved the problem: it's caused by the fact that I selected the RelationID as UID in the prompt that you get when you link a table/view.

When I cancel that prompt (and don't tell the system there is a UID in the dataset, which technically there isn't since the RelationID isn't unique in this set) I get the proper data!

 

[Galaxiom]

When I cancel that prompt (and don't tell the system there is a UID in the dataset, which technically there isn't since the RelationID isn't unique in this set) I get the proper data!

That isn't really a solution. Your table should have a key to work properly.

Can you make a composite key using multiple fields?

 

[Zakraket]

I didn't mention this explicitly, but my table have proper PK/UID (RelatieID, AdresID).

Its the VIEW that I need to link with ODBC, and when I tell Access "RelatieID" is a UID for the view - while linking the view - it goes wrong.
RelatieID is the UID for the TABLE Relaties, not for the view vw_Relaties_Adressen, because in the view RelatieID is not unique if people have multiple adresses.

Ofcourse, when I link the tables and tell Access the UID is RelatieID for table Relaties and AdresID for table Adressen there occur no problems.

It's a solution, because now Access displays the same data for the linked view as SQL-server (SSMS) does for the view