在Access/VBA中构建SQL字符串-BuildingSQLstringsinAccess/VBA

9  

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 Insert/Update字段。

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是由原始码数kay的值。

Sub CreateSQLString(strFieldName As String, varFieldValue As Variant, Optional blnZeroAsNull As Boolean)
'    Call CreateSQLString("[]", )

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。执行strSQL,dbfailonerror . .CurrentProject.Connection ADO使用。执行strCommand, lngrecordsaffection, 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公布一次。执行耗时2秒，而docmd.runsql耗时8秒。一如既往地YMMV。

4  

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

假设您使用myname表插入保存了它，您可以编写如下代码

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.

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

4  

    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

1  

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:

…和调试。打印输出此字符串:

  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.

这种变化可能对你更有用，比如。,你可能希望有一个可选的连接操作符(所以你可以或者),但我可能做的,通过构造一个接一个的字符串,连接或逐行代码,因为你可能想要把你的括号小心以确保正确执行和/或优先级。

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语句的连接地址值,但实际上我问题多长时间你访问应用程序中插入文字值。除非你使用一个未装订的形式插入记录,您将使用一种形式插入记录,因此任何SQL语句。所以，对于value子句，似乎在Access应用程序中，你不应该经常用到它。如果您发现自己需要编写这样的值子句，我建议您不要正确地使用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.

但是正如其他人所说，最好首先利用参数。

1  

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

0  

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理解=+就好了

0  

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代码，以找到参数并将参数存储在表中。我将在访问之外编写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.

这是一个非常复杂的过程，但是如果你有兴趣的话，我很乐意在下周我回去工作的时候把代码挖出来。