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?
This question is related to
ms-access
I always use below approach. I've created one table in database as Table1 with only one column i.e. Row_Id Number (Long Integer) and its value is 0
INSERT INTO <TABLE_NAME_TO_RESET>
SELECT Row_Id AS <COLUMN_NAME_TO_RESET>
FROM Table1;
This will insert one row with 0 value in AutoNumber column, later delete that row.
In Access 2007 - 2010, go to Database Tools and click Compact and Repair Database, and it will automatically reset the ID.
In addition to all the concerns expressed about why you give a rat's ass what the ID value is (all are correct that you shouldn't), let me add this to the mix:
If you've deleted all the records from the table, compacting the database will reset the seed value back to its original value.
For a table where there are still records, and you've inserted a value into the Autonumber field that is lower than the highest value, you have to use @Remou's method to reset the seed value. This also applies if you want to reset to the Max+1 in a table where records have been deleted, e.g., 300 records, last ID of 300, delete 201-300, compact won't reset the counter (you have to use @Remou's method -- this was not the case in earlier versions of Jet, and, indeed, in early versions of Jet 4, the first Jet version that allowed manipulating the seed value programatically).
I am going to Necro this topic.
Starting around ms-access-2016, you can execute Data Definition Queries (DDQ) through Macro's
ALTER TABLE <Table> ALTER COLUMN <ID_Field> COUNTER(1,1);
Open Query
actionQuery Name
field; View
and Data Mode
settings are not relevant and can leave the default valuesI think the only ways to do this is outlined in this article.
The article explains several methods. Here is one example:
To do this in Microsoft Office Access 2007, follow these steps:
Delete the AutoNumber field from the main table.
You are about to paste # row(s) into a new table.
Click Yes to insert the rows.Source: Stackoverflow.com