ACC: Exporting to SQL Server Does Not Create Indexes

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

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 exporting 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, attach (or re-attach) the tables.

In Microsoft Access 7.0 and 97 you are asked to optionally create a unique index when linking to an 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.