Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


MS Access Using GUIDs - Getting uniqueidentifier data types from SQL to Access

MS Access Using Globally Unique Identifiers

This article describes how to retrieve GUIDs (uniqueidentifier) data types from a SQL Server database table in MS Access and push them out to a Stored Procedure for processing in a separate SQL Server table. An MS Access form will be created in this article which will attempt to use 2 methods to retrieve the uniqueidentifier from the linked SQL Server table: DLookup and a DAO.Recordset.

SQL Table with uniqueidentifier Data Type in MS Access

Suppose there is a SQL Server table that I want to link to in my MS Access database. This table has a GUID (SQL uniqueidentifier) in it along with other fields. I create the table and insert some data using the statements below in SSMS using the AdventureWorks database. The newID function takes care of creating the uniqueidentifiers for me so I don't have to create them.

Create Table with uniqueidentifier

A simple SELECT statement verifies the successful creation of the table and that the table has been populated with data.

Verified Table has GUIDs

By getting External Data (ODBC) and linking to the table, UserSecurity, in MS Access, I can verify the data types that are being interpreted by MS Access (since MS Access does not have an equivalent GUID data type builtin. Although you can create a user defined type and create your own GUIDs, you will still have the issue of the way that DLookup interprets the data from a table: Numeric, Text, or Date). The userid data field shows that it appears to Access to be a number with a field size of ReplicationID.

SQL Table Design Mode in MS Access Showing uniqueidentifier as Number SQL Table Design Mode in MS Access Showing Number Field Size as ReplicationID

Retrieving GUID data from Linked SQL Server Table

Create a table in SSMS where MS Access will process and send the data (an INSERT action via a Stored Procedure that we will write).

CREATE TABLE [dbo].[Contact](
 [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL PRIMARY KEY CLUSTERED,
 [Title] [nvarchar](8) NULL,
 [FirstName] [dbo].[Name] NOT NULL,
 [MiddleName] [dbo].[Name] NULL,
 [LastName] [dbo].[Name] NOT NULL,
 [Suffix] [nvarchar](10) NULL,
 [EmailAddress] [nvarchar](50) NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT NewID(),
 [ModifiedDate] [datetime] NOT NULL DEFAULT getdate(),
 UserID uniqueidentifier null)
GO

Create a stored procedure to receive the data to be imported into our dbo.Contacts table.

Use AdventureWorks
GO

CREATE PROC dbo.usp_MoveToContacts
 @UserID uniqueidentifier,
 @FirstName nvarchar(255),
 @LastName nvarchar(255),
 @emailAddress nvarchar(255)
as

BEGIN TRANSACTION
 BEGIN

  INSERT INTO [dbo].[Contact] (UserID, FirstName, LastName, emailAddress) VALUES
   (@UserID, @FirstName, @LastName, @emailAddress)

 IF @@error = 0
  COMMIT TRAN
 ELSE
  ROLLBACK TRAN
 END

Create a form for the linked table (UserSecurity) data. Create two buttons, one which will use DLookup to capture the data and attempt to send it to the Stored Procedure and one which will use a DAO Dataset to pass the data to the Stored Procedure.

MS Access Form with Uniqueidentifier

Code the DLookup button to use DLookup on the UserSecurity SQL Server table to retrieve the GUID. We will use DLookup to get the userID from the table and put it into the string variable sContactID and then using an ADODB.Command, send it with other data from the form to the SQL Stored Procedure for INSERT into the dbo.Contacts table in the AdventureWorks database.

Private Sub cmdUpload_Click()
 On Error GoTo Err_cmdUpload_Click

 Dim sContactID As String
 Dim cmd As ADODB.Command

  sContactID = Nz(DLookup("userId", "dbo_UserSecurity", "UserName = '" & Me.username & "'"), "")

  If sContactID = "" Then
   MsgBox "Contact must be selected."
   Exit Sub
  Else
   Set cmd = New ADODB.Command
    With cmd
     .ActiveConnection = "Provider=MSDataShape;Data Provider=SQLOLEDB;SERVER=YourSQLServerName;
      DATABASE=AdventureWorks;Integrated Security=SSPI"
     .CommandType = adCmdStoredProc
     .CommandText = "usp_MoveToContacts"
     .Parameters("@UserId") = sContactID
     .Parameters("@FirstName") = Me.FirstName
     .Parameters("@LastName") = Me.LastName
     .Parameters("@EmailAddress") = Me.emailaddress
     .Execute
     .Parameters.Refresh
    End With
  End If

 Exit_cmdUpload_Click:
  Exit Sub

 Err_cmdUpload_Click:
  MsgBox Err.Description
  Resume Exit_cmdUpload_Click
End Sub

Putting a break on the line below our UserID parameter to be passed, we can pause the execution of the code to view what the value of the sContactID variable is. When we pause it, we see a string of question marks ("????????"). This results in an Error if we let the execution continue.

DLookup Method on GUID

Error message from MS Access when cmd (ADODB.Command) attempts to execute the stored procedure.

Error message from MS Access - GUID in wrong format

The code to transmit the data via DAO will also use the UserID from dbo_UserSecurity, but it will be within a DAO recordset. The recordset field is called and then assigned to sContactID where a couple of "trims" will take place to get rid of extra characters. The DAO recordset is then closed and the SQL Server stored procedure is called via the ADODB.Command and the data is processed into the dbo.Contacts table.

Private Sub cmdTransmitOther_Click()
 On Error GoTo Err_cmdTransmit_Click

 Dim sContactID As String
 Dim cmd As ADODB.Command
 Dim myset As DAO.Recordset
  Set myset = CurrentDb.OpenRecordset("Select UserID from dbo_UserSecurity where UserName = '" &
   Me.username & "'", dbOpenDynaset, dbSeeChanges, dbOptimistic)
  sContactID = myset!userid
  sContactID = Right(sContactID, Len(sContactID) - 6)
  sContactID = Left(sContactID, Len(sContactID) - 1)
  myset.Close

  Set cmd = New ADODB.Command
   With cmd
    .ActiveConnection = "Provider=MSDataShape;Data Provider=SQLOLEDB;SERVER=YourSQLServer;
      DATABASE=AdventureWorks;Integrated Security=SSPI"
    .CommandType = adCmdStoredProc
    .CommandText = "usp_MoveToContacts"
    .Parameters("@UserId") = sContactID
    .Parameters("@FirstName") = Me.FirstName
    .Parameters("@LastName") = Me.LastName
    .Parameters("@EmailAddress") = Me.emailaddress
    .Execute
    .Parameters.Refresh
   End With

 Exit_cmdTransmit_Click:
  Exit Sub

 Err_cmdTransmit_Click:
  MsgBox Err.Description
  Resume Exit_cmdTransmit_Click

End Sub

Putting a similar break in the code, we can verify that the correct UserID GUID is being passed from MS Access to the stored procedure in sContactID.

DAO Recordset method in MS Access to retrieve GUID data correctly

To verify the data has been processed, we can go back to SSMS and using a SELECT query, see that the data has indeed been inserted.

SQL verification of insertion of uniqueidentifier field from MS Access

While DLookup does not work in retrieving GUIDs properly in MS Access, at least the DAO recordset will get the job done.