In computer programming, a data type is a classification identifying one of various types of data, such as floating-point, integer, or Boolean, that determines the possible values for that type; the operations that can be done on values of that type; the meaning of the data; and the way values of...
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
tables to generate an automatically incremented numeric counter. It may be used to create an identity column
Identity column
An Identity column is a column in a database table that is made up of values generated by the database. This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle...
which uniquely identifies each record in a table. Only one AutoNumber field is allowed in each table.
The data type was called Counter in Access 2.0.
Forms
There are three forms in which AutoNumbers can be generated:
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 Identifier
Globally Unique Identifier
A globally unique identifier is a unique reference number used as an identifier in computer software. The term GUID also is used for Microsoft's implementation of the Universally unique identifier standard....
s, and the probability of collision is low until the year AD 3400.
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".
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 --82.45.72.94 (talk) 18:55, 9 October 2011 (UTC)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.
A data definition language or data description language is a syntax similar to a computer programming language for defining data structures, especially database schemas.-History:...
(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 Table1
ALTER 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;
