How to reset Access 2007 autonumber at certain row

Question

I have an Access 2007 database schema where the central table has a primary key which is an auto number. I have to delete records I input in the foreign table to re-input them and so want to delete the parent reference from the central table. What I want to know is how can I set the cebtral table autonumber to start counting again from before I input the records the first time? That is before I input the records the autonumber was at 2380 but now is at 2523, if I delete all after 2380 how do I get the auto number to restart again at 2381 when I insert the records again

Gord Thompson Gord Thompson · Accepted Answer · 2015-08-07T18:24:51

After you delete the rows from the specified table you need to do a "Compact and Repair Database" operation on the database file. That will have the effect of resetting the next AutoNumber value to (the_current_largest_AutoNumber_value + 1).

For details on how to do a "Compact and Repair Database" in Access 2007, see the "Manually compact and repair a database" topic in the following Microsoft Support article:

Help prevent and correct database file problems by using Compact and Repair

How to reset Access 2007 autonumber at certain row

1 Answers