在Acces和Server数据库中使用GUID -将GUID值插入UNIQUEIDENTIFIER列

Question

我有一个与MS Access前端链接的Server数据库。

我试图在名为UNIQUEIDENTIFIER的FileSorceID字段中存储GUID值。我想要存储的GUID来自我当前记录集的GUID (Me!GUID)，它也是一个UNIQUEIDENTIFIER，直接在server中创建。这个盖德将存储在我的桌子上。

但是，在尝试这样做时，我总是会得到一个错误-2147217887 (80040e21)。

因此，我已经有了一个GUID，我只想将它存储在UNIQUEIDENTIFIER字段中的另一个表中。我找到的所有解决方案都在讨论如何在Server中创建一个新的GUID，但我已经有了一个只需要存储的解决方案。

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         

我还尝试删除具有相同结果的字符串转换。

它总是停在这一行代码上：

rs!FileSourceID = GUID

Answer 1

我找到了解决方案，当您想通过MS Access插入GUID时，您需要在GUID周围包含{和}。

工作代码：

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