Microsoft Access–Reseed Auto Number

I was developing a small database using Microsoft Access 2010. In that database, I use Auto Number on some of the tables. During the development of the database, I’ve entered a lot of test data into those tables that have auto number.

Then it comes the time to clean up the test data, so that the database can be used for the live data. Deleting the data from the tables is pretty straightforward. However, when I delete the data it does not reset any of the auto number columns that I have. In SQL Server, we could use the “Truncate” command to delete the data in the table and reset the identity column (the auto number) in the table. Or use DBCC CHECKIDENT to reseed the identity column. However, we could not use those in Microsoft Access.

So to reseed auto number in Microsoft Access, you can do the following:

1. Delete the data from the table
2. Run the “Compact and Repair Database Tools”. It can be found under the “Database Tools” on Microsoft Access ribbon menu.