Menu
I have table in MS Access that has an AutoNumber type in field ID
After inserting some rows, the ID has become 200
Then, I have deleted the records in the table. However, when I tried to insert a new row, I see that the ID starts with 201
How can I force the ID to restart with 1, without having to drop the table and make new a new one?
885
Answer: In older versions of Access, you can reset the counter on an AutoNumber field by compacting the database. When this is done, the counter will be reset to the last number used in the table. If you wish to return the counter back to 1, delete all of the records in the table and then perform the compact.
For new tables, AutoNumber begins with 1, and is incremented by 1 with each new row. However, we occasionally want to start the AutoNumber at a number higher than 1. To do this, create an Append query to insert the value of the ID one less than your desired starting number (or Seed value).
In the Data Type field, click the drop-down arrow and click AutoNumber. Under Field Properties, in New Values, click Increment to use incrementing numeric values for the primary key, or click Random to use random numbers.
In Access 2010 or newer, go to Database Tools and click Compact and Repair Database, and it will automatically reset the ID.
160
You can use:
CurrentDb.Execute "ALTER TABLE yourTable ALTER COLUMN myID COUNTER(1,1)" I hope you have no relationships that use this table, I hope it is empty, and I hope you understand that all you can (mostly) rely on an autonumber to be is unique. You can get gaps, jumps, very large or even negative numbers, depending on the circumstances. If your autonumber means something, you have a major problem waiting to happen.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
