There are a couple of different ways you can go about resetting an AutoNumber field in a Microsoft Access table.
The first method uses the Compact and Repair Database utility built into Access. To run it, select Database Tools from the menu and then select Compact and Repair Database.
The second method is to run VBA code to reset the AutoNumber field. Let’s assume you have a table named myTable with an AutoNumber field named ID. The VBA code would look like the following:
' Reset AutoNumber
CurrentDb.Execute "ALTER TABLE myTable ALTER COLUMN ID COUNTER(1,1)"Some important notes before running the above code. You want to make sure the table is empty. Any referential integrity will be lost.
Bottom line, make sure you don’t need to use or care about the value of the AutoNumber field before resetting it.