Microsoft SQL Server ResourcesUpsizing Microsoft Access to SQL Server Tip for AutoNumber Data Types with Increment or Random Settings

Microsoft Access AutoNumber Field Type

In Microsoft Access, AutoNumber fields are long integer data types that are automatically filled with a unique value when a new record is added to the table. There are two types of AutoNumber values that can be created by setting the NewValues property to Increment or Random:

Setting Description
Increment (Default) AutoNumber field values increment by 1 for new records.
Random AutoNumber field values are assigned a random Long Integer value for new records.

When the tables are upsized to Microsoft SQL Server, it is important to note the property settings that are created during the process. The most important one is how the identity values are generated.

NewValue Setting in MS Access Property Setting in Microsoft SQL Server Identity Values
Increment Default Value = (disabled)
Identity = YES
Identity Seed = 1
Identity Increment = 1
Values are incremented by the property settings
Random Default Value = 0
Identity = NO
Identity Seed = (disabled)
Identity Increment = (disabled)
A trigger is created automatically to manage the random values assigned to the field when a new record is added to the table

Free Product Catalog from FMS