在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不允许您在行继续之后发表评论，grr！

直到最近，我还认为这是构建这些字符串的唯一方法。但是最近我看到了一种不同的模式，将诸如此类的参数（VB.NET注入到我在上面发布了答案的字符串中，并且想知道是否存在与Parameters.AddWithValue()VBA等效的参数，或者是否比VBA更好。字符串串联方法。因此，我认为这值得提出自己的问题。也许这里有些我想念的东西。

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

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是自动编号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.ExecutestrCommand，lngRecordsAffected，adCmdText然后可以删除docmd.setwarnings行。

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

两种方法之间的性能也可能存在显着差异。一篇文章说currentdb.execute花了两秒钟，而docmd.runsql花了八秒钟。一如既往的YMMV。