Saturday, July 05, 2014

Auto Number

Don't be smart



There should not be any "intelligence" in an AutoNumber field. It is meant as an index field and not anything else.

If the need should arise to reset the field, if your table does NOT contain any records, simply compacting the database again will set the Autonumber field back to 1.

Another way would be to delete the AutoNumber field and re-insert it in the table.

Here's a long way to start at a specific number.
  1. Create your table with an AutoNumber type field, but don't enter any records.
  2. Create another table with only a single Long Integer Number type field.
  3. This field must have same name as the AutoNumber field in the first table.
  4. Enter one record in the second table that is a number one less than the required start of the AutoNumber for the first table.
  5. Now create an append query to append the record in the second table to the first table and run the query.
You can now delete the second table and begin entering your data into the first table.
Also: Access AutoNumber Reset
"This is some sample code that shows how to programmatically reset all AutoNumber fields in an Access Database to a correct value (whether it be 0 or the max value + 1). In addition, it contains code for Compacting and Repairing an MS Access Database. This is perfect for people who are working with a complicated Access Database and have experienced AutoNumber bugs!
And: Creating an AutoNumber field from code  



See all Topics

No comments: