6

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
Community
  • 1
  • 1
xecaps12
  • 5,316
  • 3
  • 27
  • 42

2 Answers2

5

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:

"select unique identifier" window

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.

Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • I knew it'd be that simple. I just refreshed the table when I added the ID. Not deleting it doesn't give the Unique Record dialog. Thanks. – xecaps12 Sep 08 '11 at 16:10
  • Christian you wrote: "..if I don't select anything...Access screws up the displayed data...Apparently Access is not able to display the view correctly without a unique identifier." I find exactly the opposite; if I select a column as the unique identifier, I get the correct # of records for that identifier, but the rest of the columns with that identifier have the same value as the 1st row. If I relink to my view & DO NOT specify a unique identifier, I see all the correct values in the other columns. This is bothersome, and I can't find this behavior officially documented anywhere. – Brian Battles Sep 11 '18 at 17:54
1

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • It also helps knowing how access treats the data if you don't select unique identifier, or not enough of one. I had just selected the type field the first time not knowing access would roll the results up by the selected values. I just assumed it would make querying the view slower by not selecting a truly unique value, not change the results. – xecaps12 Sep 08 '11 at 16:32
  • Yes, that surprised me. Without a unique key, I would expect Access to treat the data source as read-only, but *not* return a different row set. I don't understand why that happened. Still it's interesting that problem went away after you re-created, rather than refreshed, the link. – HansUp Sep 08 '11 at 16:43
  • 1
    Access generally treats everything as read-only if it has no key...but additionally, it screws up the displayed row set of views (only view, no issue with tables that I know of) if no key or the wrong key is selected. I don't understand it either, but I run into it quite often at work, because we have a few power users (controllers, mostly) who have MDBs with linked SQL Server views and tables so that they can create and run custom queries. And nearly every time when they link a new view into their MDB... – Christian Specht Sep 08 '11 at 21:03