Why does linked view give different results from MS Access vs SQL Manager?

Question

I created a view on SQL Server 2000 and linked the view to my Access 2007 database via ODBC. Opening the view from Access showed what looked like a duplicate value. I updated my view to include the ids of the data in question and it was duplicated.

It looks like access is displaying the first record for each type for every row of that type. This question mentioned looking at the Order By or Top clauses, but I'm not using either of them in my view. The view links 8 tables together and the only thing I can think of is some of the tables I have in my view, I don't have linked in Access. I am going to link those tables also just to try it, but I didn't think I needed them in Access.

I've tired searching Google for answers, but I haven't had much luck besides the question I mentioned. Is this behavior normal for linked views like this?

This is my result set from sql manager:

id    Type          Dim1
----  ------------  ------
3111  Pipe          480   
3112  Fittings      0
3113  Pipe          1080
3114  Fittings      0
3115  Fittings      0
3116  Fittings      0
3117  Pipe          216

And in access I see this:

id    Type          Dim1 
----  ------------  ------
3111  Pipe          480
3112  Fittings      0
3111  Pipe          480
3112  Fittings      0
3112  Fittings      0
3112  Fittings      0
3111  Pipe          480

Answer 1

You don't need all the 8 tables linked in Access...the view is enough, Access doesn't need to know about the underlying tables.

I think you have a different problem, I experienced similar issues like yours as well.
When I link a SQL Server view in Access via ODBC, a window pops up that wants me to select a unique identifier (read: primary key) from the columns in the view:

If I don't select anything (or some wrong columns, which do not identify a unique record), Access screws up the displayed data like in your example.
Apparently Access is not able to display the view correctly without a unique identifier.

Solution for your problem:
As I understand your view, the id is unique, right?
If yes, just delete the view, re-link it via ODBC, and when the "select unique identifier" window pops up, you need to select the id column.

Answer 2

When you create an ODBC link in Access to a SQL Server table or view, Access stores meta information about that SQL Server data source. If you later change the SQL Server table structure or view definition, Access' cached information does not get reliably updated to match ... even if you "refresh" the link. It's safer to delete the link and re-create it anew to guarantee that Access' cached information is consistent with the revised data source.

Seems you discovered this fact with Cristian's help. I'm emphasizing this point for the benefit of other readers. This seems to be a fairly common gotcha, but is easily avoided when you understand what's going on under the covers.