Working with GUIDs in Acces and SQL Server database - insert GUID value into UNIQUEIDENTIFIER column-CodePudding

I have a SQL Server database linked to a MS Access frontend.

I'm trying to store a GUID value in a UNIQUEIDENTIFIER field named FileSorceID. The GUID which I want to store there comes from the GUID of my current recordset (Me!GUID) which is also an UNIQUEIDENTIFIER and is created directly within the SQL server. This GUID shall be stored in my table.

But I always get an error -2147217887 (80040e21) when trying to do this.

So I already have a GUID which I just want to store in a different table in an UNIQUEIDENTIFIER field. All solutions I've found were discussing about creating a new GUID within SQL Server but I already have one that I just need to store.

strCnxn = "Provider=sqloledb;" & _
"Data Source=MYSERVER;" & _
"Initial Catalog=MYDATABASE;" & _
"Integrated Security=SSPI;" 'Windows-Authentication

Set cn = CreateObject("ADODB.Connection")
cn.Open strCnxn

'Recordset
sql = "AttachmentsFileStream" 'Table to add file
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, strCnxn, 1, 3  '1 - adOpenKeyset, 3 - adLockOptimistic"
 
Dim GUID As Variant
GUID = Me!GUID.Value 
GUID = StringFromGUID(Me.GUID.Value)
GUID = Replace(GUID, "{guid {", "")
GUID = Replace(GUID, "}}", "")
'GUID will now hold the string "39A0483A-AE4C-44B5-94C3-00267185B81E"
'Insert into database
rs.AddNew 'FileId (also a GUID) will be automatically handled by SQL
rs!FileName = FileName
rs!FileSourceID = GUID
rs!HideFile = False

'Clean up
rs.Update
rs = Nothing

I've also tried to leave out the string conversion with same result.

It always stops at this line of code:

rs!FileSourceID = GUID

CodePudding user response：

I found the solution, you need to include {and }around the GUID when you want to insert it via MS Access.

Working code:

strCnxn = "Provider=sqloledb;" & _
"Data Source=MYSERVER;" & _
"Initial Catalog=MYDATABASE;" & _
"Integrated Security=SSPI;" 'Windows-Authentication

Set cn = CreateObject("ADODB.Connection")
cn.Open strCnxn

'Recordset
sql = "AttachmentsFileStream" 'Table to add file
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, strCnxn, 1, 3  '1 - adOpenKeyset, 3 - adLockOptimistic"
 
Dim GUID As Variant
GUID = Me.GUID.Value 'needs to be a field in the form
GUID = StringFromGUID(GUID)
GUID = Replace(GUID, "{guid {", "{")
GUID = Replace(GUID, "}}", "}")
'GUID will now hold the string like "{39A0483A-AE4C-44B5-94C3-00267185B81E}"
'Insert into database
rs.AddNew 'FileId (also a GUID) will be automatically handled by SQL
rs!FileName = FileName
rs!FileSourceID = GUID
rs!HideFile = False

'Update recordset in SQL
rs.Update