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

ID: Q90100

The information in this article applies to:

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 linked to Microsoft 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 a SQL pass-through query in Microsoft Access.

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

STATUS

This behavior is by design.

REFERENCES

For more information about updating remote ODBC tables in Microsoft Access, search the Help Index for "SQL databases," and then "Import or link SQL database tables or data from other ODBC data sources", or ask the Microsoft Access 97 Office Assistant.

Keywords : kbusage OdbcOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb