Microsoft KB Archive/209807

= ACC2000: Remote ODBC Tables Are Read-Only Without a Unique Index =

Article ID: 209807

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209807



This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you edit an attached SQL Server table that does not have a unique index defined, Microsoft Access beeps and displays the following message on the status bar:

This recordset is not updateable.



CAUSE
Microsoft Access requires that a unique index be defined for each table. Database tables from products such as ORACLE, Sybase, Ingres, and DB2 that are linked to Access by using ODBC are read-only unless they have a unique index. Views and synonyms are also read-only without a unique index.



RESOLUTION
You can use Microsoft SQL Server to define a unique index for each table with the SQL command CREATE UNIQUE INDEX. The basic syntax for this command is: CREATE UNIQUE INDEX index_name ON [[database.]owner.]table_name. (column_name [, column_name...) You can also run this command with an SQL pass-through query in Access.

After you create the unique index for the table, relink the table in Access to implement the change.

