ACC2000: Remote ODBC Tables Are Read-Only Without a Unique Index (209807)

The information in this article applies to:

Microsoft Access 2000

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.

REFERENCES

For more information about remote ODBC tables in Microsoft Access, click Microsoft Access Help on the Help menu, type import sql database tables in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Modification Type:	Major	Last Reviewed:	6/28/2004
Keywords:	kbprb kbusage KB209807