Question:
I am designing a database as a correspondance register.Each bit of inwards and outwards mail is given a unique number.
At present I have set up a counter field in Access that feedsmy VB GUI.
This works ok until you delete a record. It then leaves that old number out. To further explain, I will might have a lettersnumbered 1,2,3,4,5,6,7,8,9,10, etc. If I delete number 6 for what ever reason it completely vanishes, number and all. When I add a new record it goes to 11.
Is there a way that the database can reset the number counter.
Answer:
Typically, counters are not re-used for database integrity purposes. If you do want to reuse the deleted values, you will have to do it manually by changing the counter field to just a numeric field, and then searching the dataset for the first available position for a new record. There really is no automated way to do it, as the behavior you are describing is a feature and not a bug.
One other way to reset the counter is to perform the following steps:
- Copy the table to another table, such as “Customers-New”
- Open up the new table, then delete all of the data in it
- Close the database and compact it
- Open the old table, copy all of the data to the clipboard
- Open the new (empty) table, and paste the data into it.
This will cause the autonumber field to start from 1 all over again.
