在 Access/VBA 中构建 SQL 字符串

Building SQL strings in Access/VBA

提问by Dale

Occasionally, I have had to build a SQL string in VBA and execute it with Docmd.RunSql(). I have always built these strings by concatenating variables into the string, e.g:

有时，我不得不在 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 doesn't seem to have a unary concatenation operator (like +=) and while this doesn't look ideal, at least I can comment each of my parameters and change them independently. It makes it easier to read and to change than one monster concatenated string. But it still seems like a terrible way to build SQL strings. I have one with about 50 parameters at work, so 50 lines of mysqlstring = mysqlstring +.... Not cute.

VBA 似乎没有一元连接运算符（如 +=），虽然这看起来并不理想，但至少我可以评论我的每个参数并独立更改它们。它比一个怪物级联字符串更易于阅读和更改。但这似乎仍然是构建 SQL 字符串的一种糟糕方式。我有一个大约有 50 个参数在工作，所以有 50 行mysqlstring = mysqlstring +.... 不可爱。

Incidentally, that rules out the use of line-continuations to format the string, as there is a limit on the number of line-continuations you can use on a single string(hint: less than 50). Also, VBA doesn't let you put a comment after the line-continuation, grr!

顺便说一下，这排除了使用续行符来格式化字符串的可能性，因为您可以在单个字符串上使用的行续行数有限制（提示：少于 50）。此外，VBA 不允许您在续行后添加评论，grr！

Up until recently, I thought this was the only way to build these strings. But recently I have seen a different pattern, injecting the parameters in the string like this question (VB.NET) that I posted an answer on, and wondered if there was an equivalent of Parameters.AddWithValue()for VBA, or if that would even be any better than the string concatenation approach. So I figured that this deserves its own question. Maybe there's something I'm missing here.

直到最近，我还认为这是构建这些字符串的唯一方法。但是最近我看到了一种不同的模式，将参数注入我发布了答案的这个问题（VB.NET）中的字符串中，并想知道是否有Parameters.AddWithValue()VBA的等价物，或者这是否甚至比字符串连接方法。所以我认为这值得自己提出问题。也许我在这里遗漏了一些东西。

Can some of the Access experts please clarify what are the best practices for building SQL strings in Access/VBA.

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

采纳答案by Tony Toews

I have a timesheet app with a reasonably complex unbound labour transaction entry form. There is a lot of data validation, rate calculation and other code. I decided to use the following to create my SQL Insert/Update fields.

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

The variables strSQLInsert, strSQLValues, strSQLUpdate are form level strings.

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

Many lines of the following:

以下的许多行：

Call CreateSQLString("[transJobCategoryBillingTypesID]", lngJobCategoryBillingTypesID)

followed by:

其次是：

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

Note that the Mid lines remove the leading ", ". lngTrans is the value of the autonumber primamy kay.

请注意，中行删除了前导“,”。lngTrans 是 autonumber primamy kay 的值。

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

I see that the other posters are all using the Execute method. The problem with DoCmd.RunSQL is that it can ignore errors. Either of the following will display any error messages received by the query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror.. For ADO use CurrentProject.Connection.Execute strCommand, lngRecordsAffected, adCmdText You can then remove the docmd.setwarnings lines.

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

If you're going to use docmd.setwarnings make very sure you put the True statement in any error handling code as well. Otherwise weird things may happen later on especially while you are working on the app. For example you will no longer get the "Do you wish to save your changes" message if you close an object. This may mean that unwanted changes, deletions or additions will be saved to your MDB.

如果您打算使用 docmd.setwarnings，请务必确保将 True 语句也放入任何错误处理代码中。否则以后可能会发生奇怪的事情，尤其是在您使用应用程序时。例如，如果您关闭一个对象，您将不再收到“您是否希望保存更改”消息。这可能意味着不需要的更改、删除或添加将保存到您的 MDB。

Also performance can be significantly different between the two methods. One posting stated currentdb.execute took two seconds while docmd.runsql took eight seconds. As always YMMV.

两种方法之间的性能也可能存在显着差异。一个帖子说 currentdb.execute 用了两秒，而 docmd.runsql 用了八秒。一如既往的YMMV。

回答by Adriaan Stander

    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

回答by shahkalpesh

Adding to what @astander has said, you could create a querydef (with parameters) and save it as part of the database.

添加到@astander 所说的内容，您可以创建一个 querydef（带参数）并将其保存为数据库的一部分。

e.g.

例如

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

Assume, you saved it with a name myTableInsert, you could write the code as below

假设，您使用 name 保存它myTableInsert，您可以编写如下代码

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

Note: I have not tested this piece of code. But, I am guessing this should be it.
Hope this gives you enough info to get started.

注意：我没有测试过这段代码。但是，我想应该是这样。
希望这能给你足够的信息来开始。

回答by David-W-Fenton

As others have said, it's probably better to utilize parameters in the first place. However, ...

正如其他人所说，首先使用参数可能更好。然而， ...

I, too, have missed a concatenation operator, having become accustomed to .= in PHP. In a few cases, I've written a function to do it, though not specific to concatenating SQL strings. Here's the code for one I use for creating a query string for an HTTP GET:

我也错过了连接运算符，因为已经习惯了 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

And an example of where I've called it:

以及我称之为的一个例子：

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

...and so forth.

……等等。

Now, for constructing SQL WHERE clauses, you might consider something like that as a wrapper around Application.BuildCriteria:

现在，为了构造 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

You would then call that as:

然后你会称之为：

  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

...and the Debug.Print would output this string:

...和 ​​Debug.Print 将输出此字符串：

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

Variations on that might be more useful to you, i.e., you might want to have an optional concatenation operator (so you could have OR), but I'd likely do that by constructing a succession of WHERE strings and concatenating them with OR line by line in code, since you'd likely want to place your parentheses carefully to make sure the AND/OR priority is properly executed.

对此的变化可能对您更有用，即，您可能想要一个可选的连接运算符（因此您可以使用 OR），但我可能会通过构造一系列 WHERE 字符串并将它们与 OR 行连接来做到这一点代码中的一行，因为您可能希望仔细放置括号以确保正确执行 AND/OR 优先级。

Now, none of this really addresses the concatenation of VALUES for an INSERT statement, but I question how often you're actually inserting literal values in an Access app. Unless you're using an unbound form for inserting records, you will be using a form to insert records, and thus no SQL statement at all. So, for VALUES clauses, it seems that in an Access app you shouldn't need this very often. If you are finding yourself needing to write VALUES clauses like this, I'd suggest you're not using Access properly.

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

That said, you could use something like this:

也就是说，你可以使用这样的东西：

  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

...which would concatenate your values list, and then you could concatenate into your whole SQL string (between the parens of the VALUES() clause).

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

But as others have said, it's probably better to utilize parameters in the first place.

但正如其他人所说，首先使用参数可能更好。

回答by maxhugen

FWIW, I use a slightly different format, using Access's line break character "_". I also use the concatenation operator "&". The main reason is for readability:

FWIW，我使用稍微不同的格式，使用 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

回答by Ben McCormack

One of the things I've done in the past is create a system for parsing SQL code to find parameters and storing the parameters in a table. I would write my MySQL queries outside of Access. Then all I had to do was open the file from Access and it would be ready to be updated on the fly each time I wanted to run it.

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

It was a really complicated process, but I'd be happy to dig up the code next week when I get back to work if you're interested.

这是一个非常复杂的过程，但如果您有兴趣，我很乐意下周当我重新开始工作时挖掘代码。

回答by Kevin Ross

I would use the approach above, with each parameter on a separate line it is nice and easy to debug and add to.

我会使用上面的方法，将每个参数放在单独的行上，这很好且易于调试和添加。

If however you really did not like that way then you could look at a parameter query. Slightly less flexible but in some cases slightly quicker.

但是，如果您真的不喜欢这种方式，那么您可以查看参数查询。灵活性稍差，但在某些情况下稍快。

Or another way would be to define a public function for inserting into that table and pass the values to it as parameters.

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

I however would stick with what you have got but it would be nice if VBA would understand =+

然而，我会坚持你所拥有的，但如果 VBA 能够理解，那就太好了 =+