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 Ifconn.Openconn.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 SubtagError:    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateSQLString of VBA Document Form_LabourEntry"    Exit SubEnd 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 QueryDefset qd = CurrentDB.QueryDefs("myTableInsert")qd.Parameters("dtBegin").Value = myTextFieldHavingBeginDateqd.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 Recordset2Dim sql As StringDim 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.OpenRecordsetEnd Sub