How to reset an AutoNumber field value in Access.
The AutoNumber field value in Access does not automatically reset when you delete some rows or all rows in a table. To reset the AutoNumber field value and to refresh the AutoNumber value in the referenced table, you must manually perform some tasks.
Note You must back up your database before you perform the steps that follow.
Back to the top
Reset an AutoNumber field in a single table
To reset an AutoNumber field value, you can use either Method 1 or Method 2.
Method 1
You can reset an AutoNumber field value so it corresponds with one of the fields in the table. To do this in Microsoft Office Access 2003 and in earlier versions, follow these steps:
Delete the AutoNumber field from the main table.
Make note of the AutoNumber field name.
Click Queries on the left pane. Double-click Create query in Design view on right pane.
In the Show Table dialog box, select the main table. Click Add and then click Close.
Double-click the required fields in the table view of the main table to select the fields.
Select the required Sort order.
On the Query menu, click Make-Table Query. Type the new table name in the Table Name text box and then click OK.
On the Query menu, click Run.
A dialog box appears with the text that follows: You are about to append # row(s) into a new table. Click Yes to insert the rows.
On the File menu, click Close. Click No to close the Make-Table Query window.
Click Tables on the left pane. Right-click the new table and then click Design View.
In the Design view for the table, add an AutoNumber field with the same field name that you deleted in step 1. Add this AutoNumber field to the new table and then save the table.
Close the Design view window.
Rename the main table name. Rename the new table name to the main table name.
To do this in Microsoft Office Access 2007, follow these steps:
Delete the AutoNumber field from the main table.
Make note of the AutoNumber field name.
Click the Create tab, and then click Query Design in the Other group.
In the Show Table dialog box, select the main table. Click Add, and then click Close.
Double-click the required fields in the table view of the main table to select the fields.
Select the required Sort order.
On the Design tab, click Make Table in the Query Type group. Type the new table name in the Table Name box, and then click OK.
On the Design tab, click Run in the Results group.
The following message appears:
You are about to paste # row(s) into a new table.
Click Yes to insert the rows.
Close the query.
Right-click the new table, and then click Design View.
In the Design view for the table, add an AutoNumber field that has the same field name that you deleted in step 1. Add this AutoNumber field to the new table, and then save the table.
Close the Design view window.
Rename the main table name. Rename the new table name to the main table name.
Method 2
Alternatively, to reset an AutoNumber field value by using Method 2, follow these steps:
In Access 2003 and in earlier versions, delete the AutoNumber field from the main table.
Make note of the AutoNumber field name.
Copy the structure of the main table and then create a new table.
Click Queries on the left pane. Click Create query in Design view on right pane.
In the Show Table dialog box, select the main table. Click Add and then click Close.
To select the fields, double-click the required fields. Do this for all the fields except for the AutoNumber field in the Table view of the main table.
On the Query menu, click Append Query.
This changes the query type.
From the Table Name list, select the new table that you created in step 2. Click OK.
On the Query menu, click Run.
A dialog box appears with the text that follows: You are about to paste # row(s). Click Yes to insert the rows.
On the File menu, click Close. Click No to close the Append Query window.
Click Tables on the left pane. Right-click the new table and then click Design View.
In the Design view for the table, add an AutoNumber field with the same field name that you deleted in step 1. Add this AutoNumber field to the new table and then save the table.
Close the Design view window.
Rename the main table name. Rename the new table name to the main table name.
In Access 2007, follow these steps:
Delete the AutoNumber field from the main table.
Make note of the AutoNumber field name.
Copy the structure of the main table, and then create a new table.
Click the Create tab, and then click Query Design in the Other group.
In the Show Table dialog box, select the main table. Click Add, and then click Close.
To select the fields, double-click the required fields. Do this for all the fields except for the AutoNumber field in the Table view of the main table.
On the Design tab, click Append in the Query Type group. This changes the query type.
From the Table Name list, select the new table that yo