How to Reset the ID of AutoNumber field on Table
Per picture below, a Customer table has a Customer_ID field as a primary key and its data type is set to AutoNumber. That means the Customer_ID is automatically assigned by MS Access to each record and it is incremented by one (1). The first record will start with one on the Customer_ID field. If we delete some records then the next Customer_ID will continue from the one that is deleted.
For some reasons that you want to start the Customer_ID number from one (1) on your new database, you can use the method below to reset the number of AutoNumber field to start with number one (1). For example, you want to use the same structure of table from my sample download file and start a new ID number with one(1), you can follow the step below.
Step 1. Copy Table
Under the Navigation Pane that shows all tables,
• Click on the table you want to copy. For this example, click table tbl Customer
• Right-hand click to bring up a short-cut menu of table
• Select Copy as shown in the picture below
Step 2. Paste Table
• Click anywhere under the Navigation Pane section
• Right-hand click to bring up the short-cut menu of table
• Select Paste as shown in the picture below
Step 3. Paste Table As window
After selecting Paste from Step 2, the Paste Table As window will open.
• For this example, we can leave a default name of new table as Copy of tbl Customer.
• Select Structure Only under the Paste Options
• Click OK to close this window
The new table Copy of tbl Customer is displayed under the Tables section of Navigation Pane.
Step 4. Rename Table
The new table Copy of tbl Customer will have no record. It is a blank table with same structure from the old table as shown in the picture below. When we enter a new record on this new table, the Customer_ID will automatically start will one (1).
In order to use a new table that will work with the current database, we need to replace the old table with the new table by the following steps:
– We need to delete the old table (tbl Customer) first.
– Click on table Copy of tbl Customer
– Right-hand click to bring up the short cut menu of table
– Select Rename as shown in the picture below
– Change table name from Copy of tbl Customer to tbl Customer
Step 4. Using New Table
When we add a new record to the new table, the Customer_ID will start with one(1) again as shown in the example below. This table will work with all forms or commands in the database because it has a same name as the old one.