This is going to be a very short and sweet (and saweeeet!!! that you don’t have to delete) post. Have you ever wanted to reset the counter on a autonumber field in Access, but don’t want to delete the table and start over again? SQL Magic to the rescue!
Before running the SQL statement below, you should understand that by re-numbering an index, you will lose access to data in any referenced table. DON’T DO THIS UNLESS YOU KNOW WHAT YOU ARE DOING!
Reset MS Access Autonumber Count
Open up a new query in Access. Switch to SQL view and run:
ALTER TABLE [tablename] ALTER COLUMN [fieldname] COUNTER(1,1);