Data Management

WordArounds for Access' AutoNumber data type


Access’s AutoNumber data type automatically generates and inserts a unique consecutive value for each record, as you enter data. The process is internal; you don’t do anything but set the data type. There are a few inherent behaviors that you should consider when using an AutoNumber data type:

  • You can’t change an existing AutoNumber value.
  • When you delete a record, Access doesn’t reuse the deleted AutoNumber value.
  • If you delete all of the data in the table, Access doesn’t reset the AutoNumber seed value. The seed value is the value Access uses to determine the next AutoNumber value. For instance, if the last AutoNumber value is 102 before you delete all the records, Access will generate the value 103, not 1, when you enter the first "new" record.
  • AutoNumber always begins with the value 1 and increases the next value by 1 for each record. If you want to start with a value other than 1, you have to force the issue.

Now, there’s no workaround for the first two behaviors. You’re stuck with those and for good reason. It doesn’t make sense to automatically generate values and then change them. If you want to change values, you need to use another data type and an expression to generate your values. As for missing values, use skipped AutoNumber values to spot missing or delete data. If Access reused deleted AutoNumber values, you’d never know that someone had deleted data. This is a behavior you don’t want to bypass—use it to your advantage.

If you delete all the data in a table, but you reuse the table, you have a choice between maintaining the AutoNumber sequence and restarting the sequence with the value 1. The good news is that both tasks are easy. To maintain the sequence, simply delete all the old records and start entering new data. To reset the AutoNumber to 1, delete all the old data and then run Compact and Repair (on the Tools menu; depending on the version, the command may be on the Database Utilities menu).

The last problem is more difficult to work around. If you want to start an AutoNumber sequence with a value other than 1, do the following:

  1. Create a new table with one Numeric field, using the AutoNumber field’s name from the AutoNumber table. For instance, if the AutoNumber field’s name is EmployeeID, the Numeric field in the single-field table must also be EmployeeID.
  2. In the single-field table (step 1), enter the value you want the next AutoNumber value in the existing table to be. Save the single-field table.
  3. Base an append query on the single-field table, appending it to the AutoNumber table.
  4. Run the append query.

When you open the AutoNumber table, the number you entered into the single-field table will be in the AutoNumber field in the first available record. Enter data to complete that record. Doing so updates the internal seed value for the AutoNumber field. This trick works on an empty table or a table full of data.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

1 comments
jsmdatawork
jsmdatawork

I use this routine to empty a table then reset the autonumber to 1, but you could reset it to any number. The nice thing about it is it will do this on the fly. Private Sub DeleteData() Dim db As DAO.Database Dim Rec As DAO.Recordset Dim a As Long Set db = CurrentDb() Set Rec = db.OpenRecordset("YourTable", dbOpenDynaset) ?Make sure there are records to delete If Not Rec.EOF Then Rec.MoveFirst Do Until Rec.EOF Rec.Delete Rec.MoveNext Loop End If ?Set a to the number before the number you want to start with ?Set it to 0 if you want to start at 1 ?This will start the count at 15 a = 14 Rec.AddNew 'Set your autonumber field to a Rec![ID] = a ?Make sure you use data that matches Field(1) Rec.Fields(1) = "a" ?Update it then delete it Rec.Update Rec.MoveFirst Rec.Delete Rec.Close Set Rec = Nothing End Sub

Editor's Picks