Microsoft KB Archive/90149

= ACC: Exporting to SQL Server Does Not Create Indexes =

Article ID: 90149

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q90149



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



SYMPTOMS
Microsoft Access allows you to export data to SQL Server back ends. However, Microsoft Access does not build the index on export. This may create potential problems and confusion. For example, if you then link (attach) the table, updates are not allowed and the data is displayed as read-only in Datasheet or Form view.



CAUSE
Microsoft Access can update a linked SQL Server table only if the table has a unique index. However, Microsoft Access does not build indexes on the SQL Server table when it exports information.



RESOLUTION
In Microsoft Access versions 1.0, 1.1 and 2.0, you can create indexes on exported SQL Server tables manually. After the indexes are created, link (or re-link) the tables.

In Microsoft Access 7.0 and 97, you are asked to optionally create a unique index when linking to a SQL Server table that has no unique index.

NOTE: If you select a field or fields that do not have unique values, this will not be detected until you try to update a record and are prevented from doing so. At that time, you may delete the link and re-link the SQL Server table, specifying a different field(s) for the unique index.

