Link to home
Create AccountLog in
Avatar of vbnetcoder
vbnetcoder

asked on

in my VBA code I want to be able to delete all the records from a table and then set the ID back to 1 for the next time records are added to the table. What is the best way to do that?

in my VBA code I want to be able to delete all the records from a table and then set the ID back to 1 for the next time records are added to the table. What is the best way to do that?
ASKER CERTIFIED SOLUTION
Avatar of irudyk
irudyk
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
1. Doing results in bloat.  Access cannot reuse the space that was allocated to the table so when you append new records, they go into newly acquired free space.  The only way to recover the unused space is by compacting the database.
2. Autonumbers have no meaning.  Their sole purpose is to provide a unique identifier for each row in the table.  It doesn't matter whether the ID is 1 or 89043367.
It might be simpler just to roll your own row numbering - for example by using my RowNumber function here:

Sequential Rows in Microsoft Access