ACC2000: Table in a Microsoft Access Project Cannot Be Updated

• Microsoft Access 2000

SYMPTOMS

You find that you cannot delete, update, or insert new records in a table in an Access project.

CAUSE

The table does not have a unique index.

RESOLUTION

To work around this behavior, check that the table has a unique index. If the table does not have a unique index, create a unique index by using a stored procedure such as the one in the "How to Correct the Behavior" section that follows step 4 of the "Steps to Reproduce Behavior" section later in this article.

MORE INFORMATION

CAUTION: Following the steps in this example will modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and perform these steps on a copy of the project.

Steps to Reproduce Behavior

1. Open the sample Access project, NorthwindCS.adp.



2. In NorthwindCS.adp, create the following stored procedure, and then run it:




Create Procedure "MakeTestTable"

As

SELECT * INTO tblTest FROM customers

CREATE INDEX tblTest_Index ON tblTest (customerid)

return

3. In the Database window, click Tables, and then press F5 to refresh the Tables list.



4. Open the tblTest table in Datasheet view. Note that when you open the table in Datasheet view, you cannot edit or add records.

How to Correct the Behavior

Create Procedure "FixTestTable"

As

DROP INDEX tblTest.tblTest_Index

CREATE UNIQUE INDEX tblTest_Index ON tblTest (customerid)

return

Additional query words: prb

Keywords : kbusage kbdta AccessCS OdbcSqlms
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb