Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking SQL Server tables (Access 97/2000)

    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 ([img]/forums/images/smilies/cool.gif[/img] 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

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking SQL Server tables (Access 97/2000)

    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.
    ([img]/forums/images/smilies/cool.gif[/img] 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!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Linking SQL Server tables (Access 97/2000)

    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.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •