BUG: You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2003 (884185)


The information in this article applies to:

  • Microsoft Office Access 2003

SYMPTOMS

When you try to insert a new record in a table that has an Autonumber field, you may receive the following error message:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
Note The table may not have any relationships or any indexes.

This problem occurs in Microsoft Office Access 2003.

CAUSE

This problem may occur when the following conditions are true:
  • The Access database was compacted.
  • The table contains an Autonumber field that is not correctly reseeded.
  • You install Microsoft Jet 4.0 Database Engine Service Pack 8 (SP8).
  • You append data from linked table 1 to linked table 2 by using the Current Database option instead of the Another Database option. Then you delete the record from linked table 1 and reappend the same record from linked table 2 by using the Current Database option.

RESOLUTION

Recreate the Append queries in the dbFrontEnd.mdb database. To do this, click Append Query on the Query menu in query design view, and then click Another database.

WORKAROUND

To work around this problem, you must compact the database and then reset the Autonumber field seed. To compact the database, follow these steps:
  1. Start Access 2003.
  2. Open the Access database.
  3. If you see the Security Warning dialog box, click Open.
  4. On the Tools menu, click Database Utilities, and then click Compact and Repair Database.
  5. If you see the Security Warning dialog box, click Open.
To reset the Autonumber field seed, use one of the following methods.

Method 1: Use a Data Definition query

  1. In the Database window, click Queries under Objects.
  2. Click New, click Design View, and then click OK.
  3. In the Show Table dialog box, click Close.
  4. On the Query menu, click SQL Specific, and then click Data Definition.
  5. In the Data Definition Query window, type the following:

    ALTER TABLE TableName ALTER COLUMN AutoNumFieldName COUNTER(iMaxID,1);

    Note TableName is a placeholder for the name of table. AutoNumFieldName is a placeholder for the name of the Autonumber field. iMaxID is a placeholder for the current maximum value in the field plus 1.
  6. On the Query menu, click Run.

Method 2: Run Visual Basic for Applications code

  1. In the Database window, click Modules under Objects, and then click New.
  2. Paste the following code in the Visual Basic Editor.
    Sub ResetAuto()

Dim iMaxID As Long 
Dim sqlFixID As String

  iMaxID = DMax("<AutonumberFieldName>", "<TableName>") + 1
                       
  sqlFixID = "ALTER TABLE <TableName> ALTER COLUMN <AutonumberFieldName> COUNTER(" & <iMaxID> & ",1)"
        
  DoCmd.RunSQL sqlFixID

End Sub
    Note <AutonumberFieldName> is a placeholder for the name of the Autonumber field. <TableName> is a placeholder for the name of table.
  3. On the Run menu, click Run Sub/UserForm.
Note You must close the table before you use either method. You do not have to save the query or the module after you successfully use either method.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to reproduce the behavior

  1. Create two new blank databases, and name them dbBE1.mdb and dbBE2.mdb.
  2. In the dbBE1.mdb database, create a new table that is named Table1 that contains the following two fields:
    • Field1: Autonumber
    • Field2: Text
  3. Add the following 6 records to Table1.
    Field1Field2
    1A
    2B
    3C
    4D
    5E
    6F
  4. In the dbBE2.mdb database, create a new table that is named tblArchive that contains the following two fields:
    • Field1: Number
    • Field2: Text
  5. Create a new blank database and name it dbFrontEnd.mdb.
  6. In the dbFrontEnd.mdb database, create a new link table to the Table1 table in the dbBE1.mdb database.
  7. In the dbFrontEnd.mdb database, create another new link table to the tblArchive table in the dbBE2.mdb database.
  8. In the dbFrontEnd.mdb database, create a new append query based on Table1 in design view.
    1. Add all fields from Table1 to the design grid.
    2. On the Query menu in query design view click Append query.
    3. In the Append To box, type tblArchive as the table name.
    4. Click Current Database, and then click OK.
    5. In the Criteria row for Field1, type 3.
    6. Run the new query. You see that record 3 is appended to the tblArchive table.
  9. Delete record 3 from Table1.
  10. In the dbFrontEnd.mdb database, create a new append query that is based on the tblArchive table in design view.
    1. Add all fields from the tblArchive table to the design grid.
    2. On the Query menu in query design view, click Append query.
    3. In the Append To box, type Table1 as the table name.
    4. Click Current Database, and then click OK.
    5. In the Criteria row for Field1, type 3.
    6. Run the new query. You see that record 3 is appended back to the Table1 table.
  11. Open Table1, and then try to add a new record. You receive the error message that is mentioned in the "Symptoms" section.

REFERENCES

For more information about Microsoft Jet 4.0 Database Engine Service Pack 8, click the following article number to view the article in the Microsoft Knowledge Base:

829558 Information about Jet 4.0 Service Pack 8

Modification Type:MajorLast Reviewed:7/15/2005
Keywords:kberrmsg kbcorrupt kbBug kbtshoot kbprb KB884185 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.