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

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

Back to the top

Access 2007

Back to the top

Access 2003

Back to the top

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.

Back to the top

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.

Back to the top

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.

Back to the top

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

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

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.

Back to the top