ACC: Exporting to SQL Server Does Not Create Indexes (90149)


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
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.

REFERENCES

For more information about creating indexes on SQL Server database tables, see the SQL Server "System Administrator's Guide," or pages 67-72 in the SQL Server "Language Reference."

For more information about updating linked tables, search the Help Index for "Attach Table," or "Attached Tables" and display the available information, or ask the Microsoft Access 97 Office Assistant.
Modification Type:MajorLast Reviewed:5/9/2003
Keywords:kbprb kbusage KB90149
©2002 Microsoft Corporation. All rights reserved.