ACC2002: Access May Choose an Unexpected Index as the Primary Key (292047)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q292047
This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 2000 version of this article, see 207745.

SYMPTOMS

When you link a table from an ODBC data source, such as Microsoft SQL Server or ORACLE, and that table contains more than one unique index, Microsoft Access may select the wrong index as the primary key.

CAUSE

When you link a table from an ODBC data source, the Microsoft Jet database engine makes a call to SQLStatistics, an ODBC API function that is used to identify the first unique index to select as the primary key. SQLStatistics returns index information in the following order: Clustered, Hashed, Non-clustered, or other indexes. In addition, each index is listed alphabetically within each group.

NOTE: All indexes created within ORACLE are treated as non-clustered indexes. Therefore, the order of the index is determined by the name, rather than by the type.

RESOLUTION

To ensure that the Jet database engine properly selects the desired index as the primary key when you link the table from your ODBC back end, rename the index so that it appears first, alphabetically.

NOTE: In SQL Server version 6.x, this behavior only occurs if you are using non-clustered unique indexes.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. In a Microsoft SQL Server utility such as ISQL/w, run the following commands:
    CREATE TABLE tblPKTest
 (
  ID int Primary Key Nonclustered,
  Fname varchar(50) null,
  Lname varchar(50) null,
  Field3 varchar(50) null
 )
 go
CREATE UNIQUE INDEX aaa on tblPKTest(Fname,Lname)
  2. Start Microsoft Access, and then create a new database.
  3. On the File menu, point to Get External Data, and then click Link Tables.
  4. In the Link dialog box, click ODBC Databases in the Files of type box.
  5. In the Select Data Source dialog box, click the data source name that points to your SQL Server database, and then click OK. Supply any necessary logon information, and then click OK.
  6. In the Link Tables dialog box, select the tblPKTest table that you created in Step 1, and then click OK.
  7. Open the linked tblPKTest table in Design view, and click Yes to the prompt that you cannot modify all properties of a linked table. Note that the primary key is not the ID field as expected, but the combined index of the Fname and Lname fields.
The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kb3rdparty kbprb KB292047
©2003 Microsoft Corporation. All rights reserved.