Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual Basic
Expertise: Beginner
Oct 1, 1996



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Using Counters

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 feeds my 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 letters numbered 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.

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.
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date