在Access/VBA中构建SQL字符串

Question

有时候，我需要在VBA中构建一个SQL字符串，并使用Docmd.RunSql()执行它。我总是通过将变量连接到字符串中来构建这些字符串，例如：

Dim mysqlstring as String
mysqlstring = "INSERT INTO MyTable (Field1, Field2, Field3 ...) VALUES ("
mysqlstring = mysqlstring + Me.TextMyField1 + ", " 'parameter comments
mysqlstring = mysqlstring + Me.TextMyField2 + ", " 
mysqlstring = mysqlstring + Me.TextMyField3 + ", " 
...
mysqlstring = mysqlstring + ");"
Docmd.RunSql mysqlstring

VBA似乎没有一元连接运算符（如+=），虽然这看起来并不理想，但至少我可以注释每个参数并独立更改它们。这使得阅读和更改比一个庞大的连接字符串更容易。但仍然似乎是构建SQL字符串的可怕方式。我在工作中有一个包含约50个参数的字符串，因此需要50行mysqlstring = mysqlstring + ...。不可爱。

顺便说一句，这排除了使用行连续格式化字符串的方法，因为存在单个字符串上可以使用的行连续数限制（提示：小于50）。此外，VBA不允许在行连续后添加注释，咆哮！

直到最近，我认为这是构建这些字符串的唯一方式。但最近我看到了一种不同的模式，像我发布了答案的这个问题（VB.NET）一样将参数注入字符串中，并想知道是否有与Parameters.AddWithValue()相当的VBA方法，或者那是否比字符串连接方法更好。所以我认为这值得提出自己的问题。也许我错过了什么。

Access专家们能否澄清在Access / VBA中构建SQL字符串的最佳实践是什么。

- Dale

Answer 1

我有一个时间表应用程序，其中包含相当复杂的非绑定劳动力交易录入表单。有很多数据验证、费率计算和其他代码。我决定使用以下内容创建我的SQL插入/更新字段。

变量strSQLInsert、strSQLValues和strSQLUpdate是表单级别的字符串。

许多行如下内容：

Call CreateSQLString("[transJobCategoryBillingTypesID]", lngJobCategoryBillingTypesID)

接着是：

If lngTransID = 0 Then
    strSQL = "INSERT into Transactions (" & Mid(strSQLInsert, 3) & ") VALUES (" & Mid(strSQLValues, 3) & ")"
Else
    strSQL = "UPDATE Transactions SET " & Mid(strSQLUpdate, 3) & " WHERE transID=" & lngTransID & ";"
End If

conn.Open
conn.Execute strSQL, lngRecordsAffected, adCmdText

请注意，中间行会删除前导的“，”。lngTrans是自动编号主键的值。

Sub CreateSQLString(strFieldName As String, varFieldValue As Variant, Optional blnZeroAsNull As Boolean)
'    Call CreateSQLString("[<fieldName>]", <fieldValue>)

Dim strFieldValue As String, OutputValue As Variant

    On Error GoTo tagError

    ' if 0 (zero) is supposed to be null
    If Not IsMissing(blnZeroAsNull) And blnZeroAsNull = True And varFieldValue = 0 Then
        OutputValue = "Null"
    ' if field is null, zero length or ''
    ElseIf IsNull(varFieldValue) Or Len(varFieldValue) = 0 Or varFieldValue = "''" Then
        OutputValue = "Null"
    Else
        OutputValue = varFieldValue
    End If

    ' Note that both Insert and update strings are updated as we may need the insert logic for inserting
    '    missing auto generated transactions when updating the main transaction
    ' This is an insert
    strSQLInsert = strSQLInsert & ", " & strFieldName
    strSQLValues = strSQLValues & ", " & OutputValue
    ' This is an update
    strSQLUpdate = strSQLUpdate & ", " & strFieldName & " = " & OutputValue

    On Error GoTo 0
    Exit Sub

tagError:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateSQLString of VBA Document Form_LabourEntry"
    Exit Sub
End Sub

我看到其他海报都在使用Execute方法。DoCmd.RunSQL的问题在于它可能会忽略错误。以下任何一种方法都将显示查询接收到的任何错误消息。如果使用DAO，请使用Currentdb.Execute strSQL，dbfailonerror。对于ADO，请使用CurrentProject.Connection.Execute strCommand，lngRecordsAffected，adCmdText。然后您可以删除docmd.setwarnings行。

如果要使用docmd.setwarnings，请确保在任何错误处理代码中放置True语句。否则，在您工作于应用程序时，可能会发生奇怪的事情。例如，如果关闭对象，则不再会收到“是否要保存更改”消息。这可能意味着将保存未想要的更改、删除或添加到您的MDB。

此外，两种方法之间的性能差异可能会显着不同。有一个帖子指出，currentdb.execute需要两秒钟，而docmd.runsql需要八秒钟。总之，结果因人而异。

- Tony Toews

---

如果您使用CurrentDB而不是将其分配给变量，则无法返回受影响的记录。 - Fionnuala

足够正确，我总是忘记那个细节。我有一段代码片段来处理那种情况。 - Tony Toews

你不能使用SELECT @IDENTITY来获取最后插入的自动编号主键。 - David-W-Fenton

Answer 2

@astander所说的可以补充一下，您可以创建一个带参数的查询定义，并将其保存为数据库的一部分。

例如：

Parameters dtBegin DateTime, dtEnd DateTime;
INSERT into myTable (datebegin, dateend) values (dtBegin, dtEnd)

假设您将它保存为myTableInsert，则可以编写以下代码：

dim qd as QueryDef
set qd = CurrentDB.QueryDefs("myTableInsert")
qd.Parameters("dtBegin").Value = myTextFieldHavingBeginDate
qd.Parameters("dtEnd").Value = myTextFieldHavingEndDate    
qd.Execute

注意：我没有测试过这段代码。但是，我猜想这应该就是它。
希望这能为你开始提供足够的信息。

- shahkalpesh

---

有趣。保存的查询定义 myTableInsert 会出现在查询列表中吗？ - Dale

是的。您必须使用带有PARAMETERS子句的INSERT查询来创建它，如上所示。 - shahkalpesh

正如我在我的帖子中所述，您不必创建查询，只需使用CreateQueryDef的sql参数即可创建临时查询。请参阅提供的链接获取更多详细信息。 - Fionnuala

@Remou: 我不知道，它与在代码中编写SQL有何不同。使用现有的查询定义，您的SQL是存储在数据库中的查询的一部分。我喜欢你的例子，但它仍需要在VB代码中编写一个大查询，而我认为这是不必要的。希望你看到这条留言 :) - shahkalpesh

Answer 3

    Private Sub Command0_Click()
Dim rec As Recordset2
Dim sql As String
Dim queryD As QueryDef

    'create a temp query def.
    Set queryD = CurrentDb.CreateQueryDef("", "SELECT * FROM [Table] WHERE Val = @Val")
    'set param vals
    queryD.Parameters("@Val").Value = "T"
    'execute query def
    Set rec = queryD.OpenRecordset
End Sub

- Adriaan Stander

Answer 4

正如其他人所说，最好一开始就利用参数。然而，...

我也错过了一个连接运算符，因为我已经习惯了 PHP 中的 .=。在一些情况下，我编写了一个函数来实现它，虽然不是专门用于连接 SQL 字符串。这里是我用于创建 HTTP GET 查询字符串的代码：

  Public Sub AppendQueryString(strInput As String, _
       ByVal strAppend As String, Optional ByVal strOperator As String = "&")
    strAppend = StringReplace(strAppend, "&", "&")
    strInput = strInput & strOperator & strAppend
  End Sub

这是我调用它的一个例子：

  AppendQueryString strOutput, "InventoryID=" & frm!InventoryID, vbNullstring
  AppendQueryString strOutput, "Author=" & URLEncode(frm!Author)

......等等。

现在，对于构建SQL WHERE子句，您可以考虑像这样将其作为Application.BuildCriteria的包装器：

  Public Sub ConcatenateWhere(ByRef strWhere As String, _
      strField As String, intDataType As Integer, ByVal varValue As Variant)
    If Len(strWhere) > 0 Then
       strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & Application.BuildCriteria(strField, _
       intDataType, varValue)
  End Sub

然后你会这样调用：

  Dim strWhere As String

  ConcatenateWhere strWhere,"tblInventory.InventoryID", dbLong, 10036
  ConcatenateWhere strWhere,"tblInventory.OtherAuthors", dbText, "*Einstein*"
  Debug.Print strWhere
  strSQL = "SELECT tblInventory.* FROM tblInventory"
  strSQL = strSQL & " WHERE " & strWhere

...并且Debug.Print将输出此字符串：

  tblInventory.InventoryID=10036 AND tblInventory.OtherAuthors Like "*Einstein*"

对于您来说，可能更有用的是变体，即您可能想要一个可选的连接运算符（这样您就可以使用OR），但我可能会通过构建一系列WHERE字符串并在代码中逐行使用OR进行连接来实现这一点，因为您可能希望仔细放置括号以确保AND/OR优先级得到正确执行。

现在，所有这些都没有真正解决INSERT语句中VALUES的连接问题，但我怀疑您在Access应用程序中实际上插入文字值的频率有多高。除非您使用未绑定的表单插入记录，否则您将使用表单插入记录，因此根本不需要SQL语句。因此，在Access应用程序中，对于VALUES子句，您不应该经常需要这样做。如果您发现自己需要编写这样的VALUES子句，我建议您没有正确使用Access。

话虽如此，您可以使用类似于以下内容的东西：

  Public Sub ConcatenateValues(ByRef strValues As String, _
      intDatatype As Integer, varValue As Variant)
    Dim strValue As String

    If Len(strValues) > 0 Then
       strValues = strValues & ", "
    End If
    Select Case intDatatype
      Case dbChar, dbMemo, dbText
        ' you might want to change this to escape internal double/single quotes
        strValue = Chr(34) & varValue & Chr(34)
      Case dbDate, dbTime
        strValue = "#" & varValue & "#"
      Case dbGUID
        ' this is only a guess
        strValues = Chr(34) & StringFromGUID(varValue) & Chr(34)
      Case dbBinary, dbLongBinary, dbVarBinary
        ' numeric?
      Case dbTimeStamp
        ' text? numeric?
      Case Else
        ' dbBigInt , dbBoolean, dbByte, dbCurrency, dbDecimal, 
        '   dbDouble, dbFloat, dbInteger, dbLong, dbNumeric, dbSingle
        strValue = varValue
    End Select
    strValues = strValues & strValue
  End Sub

...这将连接您的值列表，然后您可以将其连接到整个SQL字符串中（在VALUES()子句的括号之间）。

但正如其他人所说，最好一开始就利用参数。

- David-W-Fenton

Answer 5

顺便说一下，我使用稍微不同的格式，使用Access的换行符“_”。我还使用连接运算符“&”。主要原因是为了可读性：

Dim db as Database: Set db = Current Db
Dim sql$
sql= "INSERT INTO MyTable (Field1, Field2, Field3 ...Fieldn) " & _
     "VALUES (" & _
     Me.TextMyField1 & _
     "," & Me.TextMyField2 & _
     "," & Me.TextMyField3 & _
     ...
     "," & Me.TextMyFieldn & _
     ");"
db.Execute s
Set db = nothing

- maxhugen

Answer 6

我会使用上述方法，每个参数都放在单独的一行上，这样很容易调试和添加。

如果你真的不喜欢这种方式，那么你可以考虑使用参数查询。它的灵活性稍微差一些，但在某些情况下速度稍微快一些。

或者另一种方法是为插入该表格定义一个公共函数，并将值作为参数传递给它。

然而，我会坚持你已经拥有的方法，但如果VBA能够理解=+就更好了。

- Kevin Ross

---

参数（无论是在SQL代码还是数据访问中间件中）的优点不仅仅是性能，例如强数据类型、默认参数值、SQL注入保护、转义特殊字符等。此外，我相信有些情况下，Access数据库引擎存储过程的性能实际上比动态SQL更差。 - onedaywhen

虽然参数查询确实提供了许多好处，但我只是提到了性能方面可能会有的副作用，因为Access将保留查询计划而不必像动态生成的SQL语句那样进行计算。话虽如此，随着现代硬件的发展，我怀疑您不会注意到任何差异，因此有很多理由使用参数查询，但性能排在最后。 - Kevin Ross

我建议您编辑帖子，更改“我将使用上述方法”的措辞，以包含发帖人的姓名。根据问题的查看方式，最新、最旧和投票，答案的顺序可能会改变。 - Tony Toews

抱歉，Tony，我对这个网站有点陌生，我习惯于普通的论坛，那里的顺序不会改变。我会更改我的帖子风格，通过名称引用帖子。 - Kevin Ross

Kevin，我听到你了。我自己是一个长期的Fidonet BBS/NNTP用户，所以这个在线论坛需要一点时间来适应。 - Tony Toews

Answer 7

过去我做过的一件事是创建一个解析SQL代码以查找参数并将参数存储在表中的系统。我会在Access之外编写我的MySQL查询。然后，我只需要从Access打开文件，每次想要运行它时，它就可以立即更新。

这是一个非常复杂的过程，但如果你有兴趣的话，下周回到工作岗位时我很乐意为你找出代码来。

- Ben McCormack