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

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

SYMPTOMS

When you edit an attached SQL Server table that does not have a unique index defined, Microsoft Access beeps and displays one of the following messages in the status bar.

In Microsoft Access 1.0 and 1.1

   Form is read only.

In Microsoft Access 2.0, 7.0, and 97

   This recordset is not updatable.

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 attached to Microsoft Access 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>]...)

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

STATUS

This behavior is by design.

REFERENCES