Microsoft Office Tutorials and References
In Depth Information
Resetting an AutoNumber with an Append query
Resetting an Autonumber with an Append query
Access automatically allocates AutoNumbers. Although they
follow in sequence, gaps can occur when records are deleted.
Also, if you compact and repair a database, it will reset the
AutoNumber sequence to match the next largest AutoNumber
in your table. That is, if you delete the largest AutoNumber, with
a value of 555 (the next AutoNumber would be 556), and then
compact and repair the database, the next AutoNumber will
start counting from 555.
You can use the technique described here to either add back a
missing number or reset the counting to start from a specific
value—for example, when you want the numbering to start at a
larger initial value. This involves creating a rather odd query that
does not append from a table but instead appends a specific
value into your target table.
Reset an Autonumber
1 Start with a new query in design view, but click Close when
prompted with Show Table. This will leave you with a blank query
2 Click Append.
3 Select the table to which to append, and click OK.
4 Type a new value for the AutoNumber.
5 Select the AutoNumber field in the target table.
6 Run the query. This adds a new product with an ID of 9999,
allowing new records to start counting at 10000. (You can delete the
record 9999 later.)