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