Linking SQL Server tables (Access 97/2000)

Topic

New Reply

Occasionally, when linking SQL Server tables to a new database I get the following dialogue box:

Select Unique Record Identifier
To ensure data integrity and to update records, you must choose a field or fields that uniquely identify each record. Select up to ten fields.

The dialogue box offers a list of fields to be selected.
I assume this question is asked because, for the tables where this happens, whoever set up the SQL Server database did not give these tables a primary key. Unfortunately I am creating reports from a quite a large, complex database and cannot be absolutely sure which fields may or may not idetify the records in any table uniquely. Does anybody know what might be the pros and cons of (a) pressing Esc and ignoring this dialogue box, or ( having a stab at what fields I think might define the records uniquely but getting it wrong?
A quick experiment suggests that queries based on linked tables where I have pressed Esc and chosen to ignore run faster.

Ian

Reply | Quote

Replies

Ian:

(a) Ignoring the dialogue box may make it difficult or impossible to run an update query against the linked table. However, it sounds as though you’re only pulling information from the SQL tables for reports, so probably not a great concern there. I’ve not run any tests to see if there is a performance hit, though, for not properly indentifying the key fields — there may well be.
( Again, I don’t think much would happen if you’re not running update or delete queries against the linked SQL tables.

While for your purposes I don’t think ID’ing the key fields in the linked tables is essential, I find it helpful when designing queries against linked tables, no matter the data source, and for being able to reverse engineer just what the heck I was thinking when I revisit projects months later! Best of luck!

Reply | Quote

As Shane suggests, you may be perfectly fine if you don’t want to update any records in the queries. You may be correct in assuming that the SQL Server designer didn’t put a primary key on some of the tables, but you may also be dealing with what is actually a view in SQL Server. In older versions of SQL Server you couldn’t have an index on a view – 2000 does support it. What happens when you respond yes to that question is that Access actually builds it’s own index, which does speed up the process of finding a given record. However if you are pulling a large recordset based on some other criteria, then the index doesn’t really help, and you are likely to find it takes longer because ODBC decides to pull the entire table in and run the query in Access rather than letting SQL Server do the selection – not something I’ve ever actually confirmed but seems likely based on my understanding of how the SQL Server ODBC driver works.

Reply | Quote