Restart Auto Number from zero.

[rglaeser]

I created a database program in Access and used Auto Number for the ID field. While testing the program I populated the fields with info. Now I want to have the ID numbers restart from zero. Can this be done?

[ADezii]

I created a database program in Access and used Auto Number for the ID field. While testing the program I populated the fields with info. Now I want to have the ID numbers restart from zero. Can this be done?

You simply cannot resequence an AutoNumber Field midstream. You can recreate the exact structure of the Table (CTRL+C ==> CTRL+V ==> Structure only), delete the AutoNumber Field in the new Table, create a new AutoNumber Field in the new Table, then Append all Records to the new Table. The AutoNumber Field in the new Table will start at 1 and will be consecutive without any gaps.

[pks00]

if u empty the table then perform a compact/repair, u should find u should be able to start from 1 again

[haytekph]

Another solution:
1. Open your table in design mode.
2. Delete the autonumber field.
3. Rename your table (e.g. table_old)
3. Delete your records (make sure this is your intended purpose--to restart autonumber field to zero and not re-sequencing from 1 to number of records).
4. Create a make-table query.
5. Use the current table (table_old) and assign the original name of your table as your target file created in the make-table query.
6. Close the make-table query (you may opt not to save it)
7. Open the original table you've created.
8. Add the field with autonumber
9. You may now add new records with autonumber starts with 1.
Hope it's clear....

P.S.
You may want to back-up your old table just in case you need to restart the procedure....fo r safety purposes.

-ROM