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

Original KB number:   884185

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.

Cause

This problem occurs when the Autonumber field has been incorrectly seeded.

Resolution

There are multiple methods that may resolve this problem.

Method 1: Perform Compact and Repair

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. Click the Database Tools ribbon tab and then click Compact and Repair Database from within the Tools group.

   Note

   Previous versions of Access may have the Compact and Repair Database option located elsewhere, see the documentation for your specific version of Access to locate this option.

To manually reset the Autonumber field seed, use one of the following methods.

Method 2: Use a Data Definition query

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

1. On the Create tab, click Query Design in the Queries 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);
   

   Note

   <TableName> 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.

Method 3: Run Visual Basic for Applications code

1. On the Create tab, click the down arrow under Macro, and then click Module.

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

Steps to reproduce the behavior

1. Create a new blank database

2. 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.

   Field1	Field2
   1	A
   2	B
   3	C
   4	D
   5	E
   6	F

4. Delete the record where Field1 has the value 3.

5. On the Create tab, click Query Design in the Queries group.

6. In the Show Table dialog box, click Close.

7. On the Design tab, click SQL view in the Results group.

8. Type the following in the Query1 window:

   INSERT INTO Table1 (Field1, Field2) SELECT 3 AS Field1, "C" AS Field2;
   

9. On the Design tab, click Run in the Results group.

10. Open Table1, and then try to add a new record. You receive the error message that is mentioned in the "Symptoms" section.