在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个参数的参数，所以有mysqlstring = mysqlstring +...的50行.不可爱.

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.

顺便说一句，由于存在

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!

直到最近，我还认为这是构建这些字符串的唯一方法.但是最近我看到了一种不同的模式，将参数注入字符串中，例如此问题(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是自动编号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屋！