如何在VBA中将GUID分配给DAO参数

问题描述

我有一段代码可以归结为

I have a piece of code that boils down to

dim stmt   as dao.queryDef
dim parId  as dao.parameter

set stmt = currentDB().createQueryDef("", _
    "parameters id guid, ...; insert into tab (id, ... ) values ([id], ...)")

我用以下方法创建了表格

I created the table with

 create table tab (
    id guid,
    ...
 )

和

alter table tab add constraint tab_pk primary key (id)

稍后，我想为parId分配一个GUID:

Later, I want to assign a GUID to parId:

parId.value = GuidFromString("{936DA01F-9ABD-4D9D-80C7-02AF85C822A8}")

此分配导致运行时错误3421 :Data type conversion error.

如何将GUID分配给dao.parameter变量?

How is it possible to assign a GUID to a dao.parameter variable?

这是一个更新的示例，该示例使用在我的环境中不起作用的longbinary参数:执行stmt.execute时，它会导致运行时错误3001(无效参数).

This is an updated example that uses a longbinary parameter that is not working in my environment: it causes a Runtime Error 3001 (Invalid Argument) when stmt.execute is executed.

option explicit

sub main() ' {

    dim db as dao.database
    set db = application.currentDB

  ' db.execute("drop table tab")

    createTable  db
    insertValues db

end sub ' }

sub createTable(db as dao.database) ' {

    db.execute(   _
   "create table tab ( " & _
   "  id     guid,     " & _
   "  txt    char(60)  " & _
   ")")

   db.execute("alter table tab add constraint tab_pk primary key (id)")


end sub ' }


sub insertValues(db as dao.database) ' {

     dim stmt as dao.queryDef

     set stmt = db.createQueryDef("",   _
       "parameters "                  & _
       "  id     longbinary,  "       & _
       "  txt    char(60);    "       & _
       "insert into tab values ([id], [txt]) ")


     dim parId   as dao.parameter
     dim parTxt  as dao.parameter

     set parId  = stmt.parameters("id" )
     set parTxt = stmt.parameters("txt")

     parId.value  =  GuidFromString("{936DA01F-9ABD-4D9D-80C7-02AF85C822A8}")
     parTxt.value = "Hello world."

     stmt.execute ' Access throws Runtime Error 3001 (Invalid argument)

end sub ' }

推荐答案

GUID类型是一种特殊类型，不能作为参数使用.

The GUID type is a special type that's not valid as a parameter.

相反，使用LONGBINARY将GUID作为二进制数据传递给查询:

Instead, use LONGBINARY to pass the GUID as binary data to the query:

set stmt = currentDB().createQueryDef("", _
    "parameters id LONGBINARY, ...; insert into tab (id, ... ) values ([id], ...)")

---

针对新的MCVE:

---

In response to the new MCVE:

这里的主要错误是VARCHAR声明.进行以下修改:

The main error there is the VARCHAR declaration. The following modifications work:

Sub insertValues(db As dao.Database) ' {
     Dim stmt As dao.QueryDef
     Set stmt = db.CreateQueryDef("", _
       "parameters " & _
       "  idparam   LONGBINARY ;" & _
       "insert into tab(ID)  values ([idparam]) ")
     Dim parId   As dao.Parameter
     Dim parTxt  As dao.Parameter

     Set parId = stmt.Parameters("idparam")
     'Set parTxt = stmt.Parameters("txtparam")
     stmt.Parameters!idparam.Value = GUIDFromString("{936DA01F-9ABD-4D9D-80C7-02AF85C822A8}")
     'stmt.Parameters!txtparam.Value = "Hello world."
     stmt.Execute 'no errors
End Sub ' }

还有

Sub insertValues(db As dao.Database) ' {
     Dim stmt As dao.QueryDef
     Set stmt = db.CreateQueryDef("", _
       "parameters " & _
       "  idparam   BINARY, txtparam CHAR(60) ;" & _
       "insert into tab  values ([idparam], txtparam) ")
     Dim parId   As dao.Parameter
     Dim parTxt  As dao.Parameter

     Set parId = stmt.Parameters("idparam")
     Set parTxt = stmt.Parameters("txtparam")
     stmt.Parameters!idparam.Value = GUIDFromString("{936DA01F-9ABD-4D9D-80C7-02AF85C822A8}")
     stmt.Parameters!txtparam.Value = "Hello world."
     stmt.Execute 'no errors
End Sub ' }

这篇关于如何在VBA中将GUID分配给DAO参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！