AutoNumber explained

AutoNumber is a type of data used in Microsoft Access tables to generate an automatically incremented numeric counter. It may be used to create an identity column which uniquely identifies each record of a table. Only one AutoNumber is allowed in each table.

The data type was called Counter in Access 2.0.^[1]

Forms

There are three forms in which AutoNumbers can be generated:^[2]

start value plus increment: AutoNumbers generated by this mechanism start with the start number and increment with the increment value, checking for collision with existing table rows.

random: AutoNumbers generated by this mechanism are assigned using a pseudo-random number generator that generates long integers and checks for collisions with existing table rows.

replication IDs: AutoNumbers generated by this mechanism are IDs generated in a manner to make it highly improbable that collisions will occur. They are Microsoft Globally Unique Identifiers, and the probability of collision is low until the year AD 3400.^[3]

The default size of an AutoNumber is a 4-byte (long) integer. This is the size used for start+increment and random AutoNumbers. For replication ID AutoNumbers, the FieldSize property of the field is changed from long integer to Replication ID.

If an AutoNumber is a long integer, the NewValues property determines whether it is of the start+increment or random form. The values that this property can take are "Increment" and "Random".^[4]

Use

The default AutoNumber type is a start+increment counter, with a start value of 1 and an increment of 1. Although in many instances such an AutoNumber field will appear as if it contains the row count, it does not. Deletion of rows from a table, for example, does not cause AutoNumber fields to be re-numbered, but instead leaves "holes" in the numbering. Similarly, if a transaction to add a row to a table is begun but later aborted, the AutoNumber assigned for that row will not be re-used.

The default start+increment form with the start value of 1 and increment of 1 is not suitable for all circumstances. There are reasons to choose each form, and trade-offs in doing so.

The default start and increment values might reveal information about a table that it is desired not to reveal to people viewing individual table rows. For example, using an AutoNumber field for a customer ID might reveal information that it is desirable not to reveal to, say, customer number 6. This is one example of occasion where the start value of an AutoNumber field is raised, so that customer number 6 has, say, AutoNumber field value 10006.

Using random values is desirable in cases where it would be unfortunate if it were possible to guess the next values assigned to new rows in the table. This usage is rare, however.

A common problem with AutoNumber fields is encountered if tables are replicated. If multiple users are using multiple replicas of the table, then it is likely that they will end up assigning the same values to AutoNumber fields in new rows that they add, causing replication conflicts when the replicas are merged.

This problem is addressed in two ways. First, it is possible to use Replication IDs for such AutoNumbers. Such replication IDs, being GUIDs, will be unique across replicas, with a low probability of collision. Second, when Access creates table replicas, it automatically changes AutoNumbers of the start+increment form to the random form.

Manipulation of counters using DDL

The following Data Definition Language (DDL) query creates an AutoNumber field with a start value and an increment:CREATE TABLE Table1 (Field1 COUNTER ([beginning_number], [increment_number]), [...]);

This query resets the counter:ALTER TABLE Table1ALTER COLUMN Field1 COUNTER(beginning_number, increment_number);

An alternative method of resetting the counter is to drop column re-add it (this has the side effect of renumbering existing rows in the table):ALTER TABLE Table1 DROP COLUMN Field1;ALTER TABLE Table1 ADD Field1 COUNTER;

References

1. Book: Prague, Cary . Michael Irwin . amp . Access 2002 Bible. New York. Wiley Publishing, Inc. 109.
2. Book: 636 - 638. Office 2007: The Missing Manual. Chris Grover . Matthew MacDonald . Emily A. Vander Veer . amp . O'Reilly. 2007. 9780596514228.
3. Web site: KBID 170117: How to use GUID fields in Access from Visual C++. Microsoft KnowledgeBase. 2006-01-09. Microsoft. Microsoft.
4. Web site: Microsoft Access Visual Basic reference: NewValues Property. MSDN. Microsoft. Microsoft. 2009. 2009-07-05.

Further reading

• Web site: .NET Framework Developer's Guide: Retrieving Identity or Autonumber Values. MSDN. Microsoft. Microsoft. 2009. 2009-07-05.

External links

• Set Autonumber field using VBA