Status bar displays an "Recordset is not updateable" message when you try to update a linked table in Access 2000 (304179)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q304179
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

In Datasheet view of a table, in Datasheet view of a query, or in a form, when you try to update or add records, the status bar displays the following message,

This Recordset is not Updateable.

CAUSE

The table that you are updating is a linked table that does not have a primary key or a unique index. Or, the query or the form is based on a linked table that does not have a primary key or a unique index.

RESOLUTION

To add the primary key or a unique index, use either of the following methods.

Using SQL Server Enterprise Manager

  1. Click Start, click Programs, click Microsoft SQL Server, and then click Enterprise Manager.
  2. Locate the linked table in Access.
  3. Right-click the table, and then click Design Table.
  4. To set a primary key, skip to step 10. To set a unique index, continue with the next step.
  5. Right-click the field that you want to set as a unique index, and then click Indexes\Keys.
  6. In the Properties dialog box, make sure that the Indexes\Keys tab is selected. Click New.
  7. Click to select the Create UNIQUE check box.
  8. Click Index.
  9. Click Close to close the Properties dialog box. Skip to step 11.
  10. Right-click the field that you want to set as a primary, and then click Set Primary Key.
  11. Close the table. When you are prompted to save the changes, click Yes.
  12. Start Microsoft Access.
  13. Open the database that contains the linked table.
  14. On the Tools menu, point to Database Utilities, and then click Linked Table Manager.
  15. Click to select the check box for the table that you updated. Click OK.
  16. Click OK to the message:

    All selected linked tables were successfully refreshed.

You can now update and add new records to the table.

Using Microsoft Access

  1. Start Microsoft Access.
  2. Open the database.
  3. Select the table that you cannot update.
  4. On the Edit menu, click Delete.
  5. On the File menu, point to Get External Data, and then click Link Tables.
  6. In the Link dialog box, under Files of type, click ODBC Databases().
  7. In the Select Data Source dialog box, select the correct data source, and then click OK.
  8. In the Link Tables dialog box, select the table that you deleted in step 4, and then click OK.
  9. In the Select Unique Record Identifier dialog box, select a field as the unique index, and then click OK.
You can now update and add new records to the table.

REFERENCES

For additional information about how the UniqueTable property affects data access pages, click the article number below to view the article in the Microsoft Knowledge Base:

263650 ACC2000: How to Use the UniqueTable Property of a Data Access Page

For more information about the UniqueTable property, click Microsoft Access Help on the Help menu, type uniquetable property in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about when data is or is not updateable, click Microsoft Access Help on the Help menu, type when can I update data from a query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MinorLast Reviewed:8/12/2004
Keywords:kbDatabase kbinfo kberrmsg kbdta kbprb ocsso KB304179
©2004 Microsoft Corporation. All rights reserved.