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);
jim says
definitely saweeeet!!!
stefan says
Microsoft Access 2016 – not working 😉
Terri says
MS Access 2016 – works after first deleting relationships, which is why you lose any relation to other tables.
chuks Edwin says
Awesome, worked perfectly
Edx says
thank you! you just saved me another 10 hours of muddling about!
KLiK Infotech says
cool it worked in stand alone table, don’t know about others
Lynn says
any way to get the autonumber to reset based on values w/in the table? e.g. a number of rows have the same date and i want the autonumber to start over when the date changes. the autonumber in this example is not a primary key.
Henning says
Dear Ryan, works perfecty in Access 2013; thank you so much.
francesco says
ALTER TABLE [tablename] ALTER COLUMN [fieldname] COUNTER(1,1);
IT DOESN’ T WORK