Microsoft KB Archive/169777

From BetaArchive Wiki
< Microsoft KB Archive

Article ID: 169777

Article Last Modified on 1/19/2007


  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition

This article was previously published under Q169777

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


When you link (attach) 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.


When you link a table from an ODBC data source, the Microsoft Jet database engine makes a call to SQLStatistics, an ODBC API function 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 type.


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

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


This behavior is by design.


Steps to Reproduce 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
          CREATE UNIQUE INDEX aaa on tblPKTest(Fname,Lname)
  2. Start Microsoft Access and create a new database.
  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 to connect to your SQL Server database, and then click OK. Supply any necessary log on information, and click OK.
  6. In the Link Tables dialog box, select the tblPKTest table created in Step 1, and then click OK.
  7. Open the linked tblPKTest table in Design view; click Yes to the prompt that you cannot modify all properties of a linked table. Note that the PrimaryKey is not the ID field as expected, but the combined index of the Fname and Lname fields.


ORACLE is manufactured by Oracle Corporation, a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.

Additional query words: indexes

Keywords: kb3rdparty kbprb KB169777