You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2007 or in Access 2003

Article translations Article translations
Article ID: 884185 - View products that this article applies to.
Expand all | Collapse all

On This Page

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 2007 or 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

Access 2007

Re-create the Append queries in the dbFrontEnd.accdb database. To do this, click Append in the Query Type group on the Design tab, and then click Another Database.

Access 2003

Re-create 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.
  2. Open the Access database.

    Note If you see the Security Warning dialog box, click Open.
  3. Access 2007
    • Click the Microsoft Office Button, point to Manage, and then click Compact and Repair Database.
    Access 2003
    • On the Tools menu, click Database Utilities, and then click Compact and Repair Database.
  4. 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

Open the database that has the table (back-end database) in Access 2007

  1. On the Create tab, click Query Design in the Other group.
  2. In the Show Table dialog box, click Close.
  3. On the Design tab, click SQL view in the Results group.
  4. Type the following in the Query1 window:

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

    NoteTableName is a placeholder for the name of the 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.
  5. On the Design tab, click Run in the Results group.

Access 2003

  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 The placeholder TableName represents the name of the table. The placeholder AutoNumFieldName represents the name of the Autonumber field. The placeholder iMaxID represents 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. Access 2007
    • On the Create tab, click the down arrow under Macro, and then click Module.
    Access 2003
    • 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 The placeholder <AutonumberFieldName> represents the name of the Autonumber field. The placeholder <TableName> represents the name of the 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 in Access 2003

  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 (Primary Key)
    • Field2: Text
  3. Add the following six records to Table1.
    Collapse this tableExpand this table
    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

Properties

Article ID: 884185 - Last Review: September 17, 2011 - Revision: 7.0
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
Keywords: 
kberrmsg kbcorrupt kbbug kbtshoot kbprb KB884185

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com