ACC2002: @@IDENTITY Returns Incorrect Identity Column Value

When you enter new data into a Microsoft Access database, your entry is immediately replaced by incorrect values.

-or-

When you enter new data, you receive the following error message:

This behavior occurs because the @@IDENTITY function is used to resynchronize data after new records are added. This function returns the last identity column value created in the current session. When you add a new record, the Cursor Engine uses the @@IDENTITY function to resynchronize all records and ensure that all data is available.

To work around this issue, use one of the following methods.

Manually Refresh the Records

To view the data that you have just entered, click Refresh on the Records menu. By doing so, you update your display with the most current data.

Automatically Refresh the Records

Alternatively, you can use a form to add records and use the form's AfterInsert event to refresh the form. By doing so, the cursor is repositioned on the first record after the record is added. You can use the GoToRecord method to move to a new record.

1. In the database window, click the table where the problem is occurring to select it.
2. On the Insert menu, click Form.
3. Click AutoForm: Datasheet in the New Form dialog box, and then click OK.
4. On the View menu, click Design View.
5. On the View menu, click Code to display the Visual Basic Editor.
6. Type or paste the following code in the Visual Basic Editor:

   Private Sub Form_AfterInsert()
       ' Refresh the form after records are added
       Me.Refresh
   
       ' OPTIONAL: This code will place the cursor on
       ' the first record.  Use the GoToRecord method
       ' to move the cursor to a new record.
       DoCmd.GoToRecord , , acNewRec
   End Sub
   					

7. On the Visual Basic Editor File menu, click Close and Return to Microsoft Access.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

If the current session adds records to other tables, for example, by using a trigger, the @@IDENTITY function may return the value inserted into the other table and therefore display incorrect records for the current table.

Steps to Reproduce the Problem

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

1. Start the SQL Query Analyzer on any Microsoft SQL Server computer, and then type or paste the following T-SQL code:

   USE Northwind
   GO
   
   -- Create the first table (TableXX)
   CREATE TABLE TableXX
   (
   IDCol int NOT NULL IDENTITY (1,1) Primary Key,
   F1 Char(25)
   )
   GO
   
   -- Add three records into TableXX
   INSERT INTO TableXX(F1) VALUES ('Blue')
   INSERT INTO TableXX(F1) VALUES ('Green')
   INSERT INTO TableXX(F1) VALUES ('Pink')
   GO
   
   -- Create the second table (TableYY)
   CREATE TABLE TableYY
   (
   IDCol int NOT NULL IDENTITY (1,1) Primary Key,
   F1 Char(25)
   )
   GO
   
   -- Create INSERT TRIGGER for TableXX
   -- to add a record to TableYY when
   -- records are entered in TableXX
   CREATE TRIGGER TrigOnTblXX ON dbo.TableXX
   FOR INSERT
   AS
   INSERT TableYY VALUES(' ')
   GO
   					

   This procedure creates two tables, TableXX and TableYY, inserts three rows of data into TableXX, and creates an insert trigger on TableXX that inserts one row into TableYY every time that a new row is inserted into TableXX.
   
   
2. Start Microsoft Access and open a new Access project based on the same Northwind database that you used in step 1.
3. Open TableXX in Datasheet view.
4. Add a new record with the value Red in the F1 field and press ENTER.
   
   Note that the value Red is replaced with the value Blue, and its corresponding IDCol value is 1 instead of 4.
5. Add another record with the value Purple in the F1 field.
   
   Note that when you press ENTER, your entry is replaced with the value Green and the IDCol value is changed to 2.

In this example, the @@IDENTITY function returns the value of the identity column inserted into TableYY by the insert trigger on TableXX. In this case, the value is 1 because there were no records in TableYY when the value was entered.

Note that this issue affects only the way data is displayed and has no bearing on the integrity of the data stored in the underlying table.

For more information about the @@IDENTITY function, see SQL Server Books Online at the following MSDN Web site: