Upsizing 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

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

Microsoft Access AutoNumber Field Type

Strategic Overview

SQL Server Security

SQL Azure Cloud

SQL Server Tips

SQL Server Express

Videos

Services

Additional Resources

Connect with Us