Use Globally Unique Identifiers in Access and SQL Server

Use Globally Unique Identifiers in Access and SQL Server

Add a GUID Column
Because the GUID datatype is native to Access (95 and later) and SQL Server (7.0 and later), generating GUID values is a very simple process. Access GUIDS are called Replication ID's. To generate a GUID in Access, you simply define a Replication ID AutoNumber field. In SQL Server, you set a uniqueidentifier column's Is RowGuid property to Yes. Creating a GUID primary key column in Access, SQL Server Desktop (MSDE), or SQL Server is very similar:

Launch your database system and create a new table.
Name the first column.
Access users: select AutoNumber from the Data Type column. (Notice that the default Field Size setting is Long Integer.) SQL Server users: choose uniqueidentifier.
Access users: select Replication ID from the Field Size property dropdown list (see Figure 3).

Figure 3: Choose the Replication ID Field Size Setting
In SQL Server, change the Is RowGuid property setting to Yes. Both SQL Server Desktop and SQL Server will set the Default Value property to NEWID(), the Transact-SQL function that generates a new GUID.
Click the Primary Key tool on the Table Design toolbar in Access. In SQL Server, click the Set primary key tool. (Figure 4 shows an Access project, and Figure 5 shows a similar table in SQL Server's Enterprise Manager.)

Figure 4: Change the Is RowGuid Property to Yes in an Access Project

Figure 5: The GUID Column Defaults to the NEWID() as the Default Value Property
Add at least one other column so you can add data and watch the GUID primary key respond (see Figure 6).

Figure 6: Add Data So You Can Watch the System Generate GUID Values