Unique Record Identifier for MS SQL Server Tables (BE) Linked to Access FE

[Don]

We are in the initial stages of migrating the backend of our MS Access
database to a MS SQL Server. In setting up a test FE for testing, I have
noticed on several occasions when I have replaced a link to a table in the
Access BE with an ODBC link to the SQL Server that Access FE asks to "Select
a Unique Record Identifier". The form that pops up indicates this is to
"ensure data integrity and to update records" and allows up to 10 fields to
be selected to create a unique identification.

First, is this a result of using ODBC to link the table instead of linking
directly to a MS Access backend? In general, the request makes sense, but I
want to better understand what the motivation is and the technical
requirements.

Second, would a better way of making a unique identifier for each record to
create a new field of type uniqueidentifier in the table? Basically a
serial number for each record.

Any suggestions and comments will be greatly appreciated!

Thanks!

Don

 

[Joe Fallon]

Don,
The Unique Record Identifier is used whenever Access cannot detect the
Primary Key (or Unique index) of the linked table.

This means that 1 or more of your tables in SQL Server do not have PKs.
This is bad.

Every table in SQL Server should always have a PK and a timestamp field in
order to interact best with Access.

The timestamp is a data type, but it is not Date/Time!!

The timestamp is changed to a unique value whenever a row is modified.

If you add it to the table, then Access will use it "silently" to check to
see if anyone has changed the record since it was downloaded. (In other
words, the timestamp field does not need to be part of the SELECT
statement.)

Access compares the timestamp it downloaded to the current one and then
allows the update.

When there is no timestamp field, Access has to check *every* field in that
row to see if the data has changed. Not only is this slow, it often fails
due to decimal data type inaccuracies.

Also, check your table to see if you have a boolean field that is type
"Bit". If you have any Bit fields, they MUST have a default value and MUST
NOT be null.

See the following MS KB article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;278696

For a Microsoft Access 2000 version of this article, see:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280730

 

[Don]

Joe,

You confirmed what I suspected and then some. I am going to go back and
define Primary Keys where none exist and add time stamps.

Thanks!

Don