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);
francesco
ALTER TABLE [tablename] ALTER COLUMN [fieldname] COUNTER(1,1);
IT DOESN’ T WORK
Henning
Dear Ryan, works perfecty in Access 2013; thank you so much.
Lynn
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.
KLiK Infotech
cool it worked in stand alone table, don’t know about others
Edx
thank you! you just saved me another 10 hours of muddling about!
chuks Edwin
Awesome, worked perfectly
Terri
MS Access 2016 – works after first deleting relationships, which is why you lose any relation to other tables.
stefan
Microsoft Access 2016 – not working 😉
jim
definitely saweeeet!!!