在Access/VBA中构建SQL字符串

问题描述：

有时，我不得不在VBA中构建一个SQL字符串，并使用Docmd.RunSql()执行它。我一直通过连接变量到字符串，建这些字符串如：在Access/VBA中构建SQL字符串

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

顺便提一句，这就排除了使用换行来格式化字符串，因为有limit on the number of line-continuations you can use on a single string（提示：小于50）。此外，VBA不会让您在续行后发表评论，grr！

直到最近，我还以为这是构建这些字符串的唯一方法。但是最近我看到了一种不同的模式，在字符串中注入参数，如this question (VB.NET），我发布了一个答案，并想知道是否有相当于VBA的Parameters.AddWithValue()，或者如果甚至比字符串连方法更好。所以我认为这是值得自己的问题。也许我在这里错过了一些东西。

有些Access专家可以澄清一下在Access/VBA中构建SQL字符串的最佳实践。

答

我有一个时间表应用程序，具有相当复杂的未绑定劳动事务条目表单。有很多数据验证，费率计算和其他代码。我决定使用以下来创建我的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.Execute strCommand，lngRecordsAffected，adCmdText然后可以删除docmd.setwarnings行。

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

这两种方法的性能也可能有很大的不同。一篇文章称currentdb.execute花了两秒钟，而docmd.runsql花了八秒钟。一如既往YMMV。

如果你使用CurrentDB，而不是将CurrentDB分配给一个变量，那么你不能返回受影响的记录。 – Fionnuala 2009-11-26 22:45:18

真的够了，我一直忘记那个细节。我有一段代码来处理这种情况。 – 2009-11-27 00:47:01

而且您不能使用SELECT @IDENTITY获取最后一个插入的自动编号PK。 – 2009-11-27 21:02:33

答

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

答

我会用上面的方法，每个参数在单独的行很不错，便于调试和增加。

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

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

然而，我会坚持你所拥有的，但它会很高兴，如果VBA会明白= +

还有更多的优点参数（在SQL代码和数据访问中间件）比性能例如强大的数据类型，默认参数值，SQL注入保护，转义特殊字符等。另外，我确定有些情况下Access数据库引擎PROCEDURE实际上比动态SQL执行的更差。 – onedaywhen 2009-11-26 09:54:29

同意参数查询的确有很多好处。我只是提到了事物的性能方面，因为访问会保留查询计划，而不必像使用动态生成的SQL语句那样工作。说了现代硬件，我怀疑你会注意到任何差异，所以有很多理由使用参数问题，但性能非常低的名单 – 2009-11-26 11:08:28

我建议编辑您的张贴改变措辞“我会用上面的方法，“包括海报名称。根据观看问题的方式，最新，最旧和投票顺序的答案可以改变。 – 2009-11-26 19:20:25

答

添加到什么@astander说，你可以创建一个QueryDef（带参数），并将其保存为一部分的数据库。

例如

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

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

有趣。保存的querydef myTableInsert会显示在查询列表中吗？ – 2009-11-26 08:50:10

是的。您必须使用带PARAMETERS子句的INSERT查询来创建它，如上所示。 – shahkalpesh 2009-11-26 11:00:42

正如我在我的文章中所述，您不必创建查询，只需使用CreateQueryDef的sql参数即可创建临时查询。请参阅提供的链接了解更多详情。 – Fionnuala 2009-11-26 22:38:51

答

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

这是一个非常复杂的过程，但如果您有兴趣，我会很乐意在下周回到工作岗位时挖掘代码。

答

正如其他人所说，首先利用参数可能会更好。但是，...

我也错过了一个串联运算符，已经习惯了。=在PHP中。在一些情况下，我写了一个函数来完成它，但并不特定于连接SQL字符串。下面是我使用一个HTTP GET创建一个查询字符串为一个代码：

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

并在那里我已经把它叫做一个例子：

...等等。

现在，构建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代码中的OR逐行连接它们，因为你可能想要仔细放置括号以确保AND/OR优先级正确执行。

现在，没有一个真正解决了INSERT语句的VALUES连接问题，但是我质疑您在Access应用程序中实际插入文字值的频率。除非您使用未绑定的表单来插入记录，否则您将使用表单来插入记录，因此根本没有SQL语句。因此，对于VALUES子句，似乎在Access应用程序中，您不应该经常需要它。如果您发现自己需要编写像这样的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字符串（值的括号（）之间条款）。

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

答

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

在Access/VBA中构建SQL字符串

相关推荐