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.
A simple SELECT statement verifies the successful creation of the table and that the table has been populated with data.
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.
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.
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.
Error message from MS Access when cmd (ADODB.Command) attempts to execute the stored procedure.
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.
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.
While DLookup does not work in retrieving GUIDs properly in MS Access, at least the DAO recordset will get the job done.