Building SQL strings in Access/VBA

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 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!

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.

Can some of the Access experts please clarify what are the best practices fo开发者_Go百科r building SQL strings in Access/VBA.

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.

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

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.

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

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.

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.

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.

Adding to what @astander has said, you could create a querydef (with parameters) and save it as part of the database.

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

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.

    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

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:

  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:

  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.

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).

But as others have said, it's probably better to utilize parameters in the first place.

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:

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

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 =+

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.

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.