Software

Ask something
Discussion Locked

Reset Access Autonumber after deletion

0 helpful
7 replies

by martinpr

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In previous versions of Access the counter on a Autonumber field could be reset to the next highest number, following a deletion of the most recent record, by compacting the database.
In Access 2000 this does not appear to work. the only way I have found is to import the table into a new database. Not a very practical solution. Any sugestions?

7 replies

by alysondunsford

the programming behind autonumbers is easily explained - access will sort the table by the autonumber field, look for the highest number and add one - it does not spend the time and effort looking for "gaps" in that sequence. by default you cannot therefore change that unless of course you create an numeric field and code it yourself!

by martinpr

While a good explanation of the working of the autonumber system this does not answer the question. I need to know if, when a new record is entered then deleted by accident or on purpose, is there is a way to reset the counter BEFORE A FURTHER RECORD IS ADDED? In versions prior to Access 2000 this could be done by compacting the database!

by karpi

Hello,

delete the records in the table and compact the database, then the counter should start with 1.

(Extras/Database utilities (?)/ Repair and compact database)

HTH
Karpi

by martinpr

This works but I do not want to delete all records, just 1 or more of the HIGHEST numbers.
For example:
The last autonumber assigned ID is 60. Two new records are entered and are assigned IDs of 61 and 62. If record 61 and 62 are deleted the next entered record will of course still be 63. However, in Access 97 or earlier versions, compacting the database after a deletion resets the counter and a new entry would again be assigned an ID of 61. In 2000 and XP, even after compacting, the next assigned ID will still be 63. Is there a way to duplicate the 97 action in 2000 and XP?

by dklippert

Delete the AutoNumber field. Save the table, put the field back again.
There should be no meaning to an Autonumber field.
Be sure that the Autonumber is not being used as a foreign key in any other context.

This discussion is closed to new replies.