Access database renumber automatically numbered fields

How to reset the AutoNumber field value in Access. AutoNumber fields are not automatically reset when you delete some or all rows from a table in Access. To reset AutoNumber field values and refresh the AutoNumber values in the referenced table, you must perform some tasks manually.
Note: You must back up the database before you perform the following steps.
Reset an AutoNumber field in a single table
To reset the AutoNumber field value, you can use Method 1 or Method 2.
Method 1
You can reset the AutoNumber field value so that it corresponds to a field in the table. To do this, follow these steps:
1. Remove the AutoNumber field from the primary table. Note the AutoNumber field name.
2. Click Query in the left pane. In the right pane, double-click Create query in Design view.
3. In the Show Table dialog box, select the primary table. Click Add, and then click Close.
4. Double-click the field you want in the table view of the main table to select the field.
5. Select the sort order you want.
6. On the Query menu, click Make Table Query. Type a new table name in the Table Name text box, and then click OK.
7. On the Query menu, click Run.
8. A dialog box with the following text appears: You are about to paste the # row into the new table. "Click Yes to insert the row."
9. On the File menu, click Close. Click No to close the Make Table Query window.
10. Click Table in the left pane. Right-click the new table, and then click Design View. 11. In table Design view, add the AutoNumber field that is the same as the field name that you removed in step 1. Add the AutoNumber field to the new table, and then save the table.
12. Close the Design view window.
13. Rename the primary table. Renames the new table name to the primary table name.
Method 2
You can also reset AutoNumber field values by using method, and then follow these steps:
1. Remove the AutoNumber field from the primary table. Note the AutoNumber field name.
2. Copy the structure of the primary table and create a new table.
3. Click Query in the left pane. In the right pane, click Create Query in Design view.
4. In the Show Table dialog box, select the primary table. Click Add, and then click Close.
5. To select a field, double-click the field you want. Do this in the table view of the main table for all fields except the AutoNumber field.
6. On the Query menu, click Append Query. This will change the query type.
7. From the table name list, select the new table that you created in step 1. Click OK.
8. On the Query menu, click Run.
9. A dialog box with the following text appears: "You are preparing to append a # line." "Click Yes to append the row to the new table."
10. On the File menu, click Close. Click No to close the Append Query window.
11. Click Table in the left pane. Right-click the new table, and then click Design View.
12. In table Design view, add the AutoNumber field that is the same as the field name that you removed in step 1. 　Add the AutoNumber field to the new table, and then save the table. 13. Close the Design view window.
14. Rename the primary table. Renames the new table name to the primary table name.
Reset the AutoNumber field in a table with a reference table
The following steps explain how to reset an AutoNumber field for a table that has a reference table. If you have more than one reference table, you must perform the following steps for each reference table.
1. Deletes the relationship between the tables.
2. Set the AutoNumber field of the primary table to the numeric data type. Deletes the primary key.
3. Create a new field in the main table with the data type AutoNumber. Save the table.
4. Create a new field in the reference table that has a data type of number. Save the table.
5. To create an update query that updates the new field in the referenced table to the new AutoNumber field in the primary table, follow these steps:
A. Click query in the left pane. In the right pane, click Create Query in Design view. This creates a new query.
B. In the Show Table dialog box, select the primary table and the reference table. Click Add to add the primary table and the reference table. Click Close.
C. Click the field in the main table that was previously linked to the referenced table. Use the drag-and-drop feature to place the field on a previously linked field in the reference table. This creates a join that is based on the original link field between the two tables.
D. On the Query menu, click Update Query.
E. Double-click the new field in the reference table to add it to the field list. (www.3lian.com)
F. In the update To field, type [primary table name]. [New AutoNumber field] to update the new field values in the referenced table.
G. On the Query menu, click Run.
H. A dialog box with the following text appears: You are preparing to update the # line. "Click Yes to append the row to the new table."
I. On the File menu, click Close. Click No to close the Update query window.
6. Delete the original link field from the main table and the reference table.
7. Rename the new AutoNumber field to the original name.
8. Recreate the primary key and the relationship between the tables. This procedure resets the AutoNumber field and then updates the reference table with the correct key value.