Two methods to reset Auto number to zero in MS Access
In Microsoft Access, I use two methods to reset auto number to zero which also has the primary key. First, I just use it when I finish a program and ready to publish it. Usually, I do not reference the auto-number to act like a serial number. Instead, I use other methods like DMax or DCount functions. Please visit our post “Create Row Numbers on a Form or Report” to learn more about it.
Moreover, we do not care much about the numbers displayed in Auto-Number field, whether it starts from 1 or 1000. However, the main purpose of Auto-Number field is to use it as a reference only.
Method# 1 to reset auto-number to zero
Lots of websites have already mentioned this VBA Code. Just create a button and associate this VBA code to it. However, it will reset the Auto-Number to Zero in case the table is not associated with other tables (through relationships). Of course, you will have to delete all records from the table before using this VBA code.
|
1 |
CurrentDb.Execute "ALTER TABLE yourTable ALTER COLUMN myID COUNTER(1,1)" |
Method# 2 to reset auto-number to zero
Method# 1 will only reset the autonumber if the table is not associated with other tables. However, what about if the table has a relationship with other tables. The first method will not work. Instead, I use the below VBA codes. The downloadable example is created with this Method 2. So, you can download it and check it yourself.
|
1 2 3 4 |
DoCmd.RunSQL "DELETE * FROM TABLE-NAME" DoCmd.RunSQL "DELETE * FROM MAIN-TABLE-NAME" DoCmd.RunSQL "INSERT INTO MAIN-TABLE-NAME(ID) VALUES (0)" DoCmd.RunSQL "DELETE * FROM MAIN-TABLE-NAME" |
Above VBA code has at least four steps. First, you will need to delete all records from the table associated with the main table that has the primary key. Second, you will need to delete all records from the main table that has the primary key. Third, insert the value “0” to the primary key field in the main table. Finally, just delete all records from the main table. That is it. You are done!
May be it seems complicated but actually it is very easy. To get a real example, just download our file and check the VBA codes.
For more pro MS Access Programs, please visit our home page here.
To use Microsoft Access Programs.
You’ll need to have Microsoft Office installed including the bundle of MS Access or to download the free Microsoft Access 2016 run-time.