Why does Access want you to identify a primary key on linked tables?

Supporting users who use MS Access as a front-end for viewing some Oracle tables at our intstitution. Typical use is to write ad-hoc queries with the Access querybuilder, and also they LOVE Access because it lets them build printable reports without having to submit to the lengthy & expensive programming-request process through our tech support department.

When creating a link to an external table, they are sometimes asked to identify the primary key of the table. Of course, how can they possibly have any idea what the primary key field(s) would be?

My question is this: Why does Access want to know what the linked table's primary key field(s) is? Indexing should be happening in the source db, shouldn't it?

We had an issue recently when users started moving to Access 2007 that was finally "fixable" in two ways: 1) new ODBC connection, or 2) identifying a different primary key column for the linked table. My tech support tried to insist that I would have to modify my practice by telling my users to select the correct primary key field. I contended that it shouldn't matter. But I can't dismiss the thought that MS Access wouldn't be asking if it didn't matter for something. Just not sure what that might be.

Note: the "issue" is described in this post: http://stackoverflow.com/questions/3346067/access-2007-to-oracle-10g-linked-table-query-with-flawed-results-but-no-error

Thanks -- that's good advice. I get the point (I think) that the PK Access wants is just to make it editable, and otherwise I don't need it. That's what I thought...just wanted to make sure. As for your thoughts on the DSN -- sounds like it would be a good idea generally, but my users will link to their own tables frequently -- our institution has hundreds, if not thousands of tables to choose from, and I can't (or at least, really don't want to) setup links for all of them just in case they might be used. Would be tough to keep them up-to-date anyway, as tables are added/modified.

dave 2010-07-30 22:08:13

Consider giving your users a file DSN for creating their links. Also they should drop and re-create their links for any remote tables whose structure changes because Access stores meta information about the ODBC source when the link is created, and it doesn't get updated when the table definition changes. Are your Oracle DBAs on board with all this?

HansUp 2010-07-31 00:14:37

Thanks. Yeah -- I understand all that stuff -- mostly, I just wondered why Access asks for a PK on linked tables -- if there was more too it than I had thought previously. For some reason, Access 2007 has issues with our old ODBC driver (which still works for previous versions of Access), causing strange results from queries. Our tech support who wrote the driver many years ago is convinced it's OK, and the problem is caused by users choosing the wrong PK when they link the table. My job is to convince them that it's time to review their programming. :-) Anyway... Thanks for the feedback

dave 2010-07-31 20:16:30

Do the tables actually have PKs? If not, then that's the problem more than anything else! If they do, and your ODBC driver is not giving Jet/ACE the right table metadata (including, but not limited to, what the PK is), then it's an error in the design of the ODBC driver, however well it may have worked in the past with previous versions of Access.

David-W-Fenton 2010-08-02 00:05:16

The question was "Why does Access want to know what the linked table's primary key field(s) is?". So marking this the answer (thanks HansUp) -- it's necessary to make the linked tables editable, but not necessary for read only. Lots of other (if un-related) great comments about good table design, what users should know, etc. Thanks for those too!.

dave 2010-08-02 16:33:51

Access needs to know the PK so that it can update the right records. If there is no PK it will try to do a composite WHERE clause (I think), but in doing that, Nulls can really screw things up (Jet/ACE is not always smart enough to use Is Null instead of =Null). Yes, of course, if it's read-only, none of this is necessary, but I don't know that Access is built for that kind of flexibility. I suspect it's going to assume read/write and require the PK.

David-W-Fenton 2010-08-02 19:50:45

They know what the table means, because word-of-mouth, somebody says "you get a list of employees from such-and-such table". Those who do not know how to join tables don't join tables. Those who do, do. Joins do not necessarily require any knowledge of primary keys. In a parent-child relationship, sure, but there are many other scenarios in which you might want to join tables. Our users range from very skilled to zero skill, there are about 2000 of them in total, and only about 43 of them are what you might want to call a "techie". The rest are managers or secretaries, etc.

dave 2010-08-02 21:57:46

ansaurus

tags:

views:

answers:

Why does Access want you to identify a primary key on linked tables?

related questions