Thursday, November 20, 2008

How to reset AutoNumber in Microsoft Access table?

After testing my own application, now it's time for deploying it. 
Well, there are many junk records being added in the table, and now it's time to reset everything.
The AutoNumber field value in Access does not automatically reset when you delete the rows in the table. Finally, I've found the solution here.

1. Delete the AutoNumber field from the table.
2. Click Queries on the left pane, and double-click Create query in Design view on the right pane.
3. In the Show Table dialog box, select the table. Then click Add to add the table and Close the dialog box.
4. Double-click all the fields in the table to select the fields.
5. Select the required Sort order, if there's any.
6. Click Make-Table Query on the Query menu. Type the new table name in the Table Name text box and then click OK.
7. Click Run on the Query menu.
8. A dialog box appears with the text that follows: You are about to append # row(s) into a new table. Click Yes to insert the rows.
9. Click Close on the File menu. Click No to close the Make-Table Query window to discard saving the query.
10. Click Tables on the left pane. Right-click on the new table and then click Design view.
11. In the Design view for the table, add an AutoNumber field with the same field name that you deleted in step 1. Then save the table.
12. Close the Design view window.
13. Delete the original table, and rename new table name to the original table name.
Now the AutoNumber has started from 1.

For more information, please refer to Microsoft Help and Support.

1 comment:

  1. why don't you continue posting?
    I love your posts