在 Access/VBA 中构建 SQL 字符串 [英] Building SQL strings in Access/VBA

问题描述

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

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:

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 +....不可爱.

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.

顺便说一下，这排除了使用行延续来格式化字符串，因为有一个 限制您可以在单个字符串上使用的续行数(提示:少于 50).此外，VBA 不允许您在续行后添加注释，gr！

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!

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

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.

能否请一些 Access 专家澄清在 Access/VBA 中构建 SQL 字符串的最佳做法是什么.

推荐答案

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

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.

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

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

以下多行:

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 是 autonumber primamy kay 的值.

Note that the Mid lines remove the leading ", ". lngTrans is the value of the 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

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

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.

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

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.

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

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.

这篇关于在 Access/VBA 中构建 SQL 字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！