Queries: Simplify action queries in code

    Have you ever put a batch import/update/export process in place in Access using the Execute or RunSQL method? If so, then you probably know the pains of going back in your code to update the SQL statement. I'll be honest, I've come close to pulling my hair on several occasions.

    Till I realized one day that we could use an Access table and some simple logic to not only trim the possible hundreds of lines of embedded SQL and VBA code, but also make the queries themselves self documenting. One could continue to define queries with Access's QBE and not have to end up concatenating the entire SQL in code.

I defined a table, tblSQL, as

The field description represent the actual content of each field. The idea behind this process is to save SQL statements in the order they need to get executed, ensuring their uniqueness via the SQLSequence number. If you have several functions using action queries, they can still be saved in the same table provided that you use the entry in the CalledFromProc field to categorize the SQL statements. Some typical entries in the table would look like

Create a simple form bound to this table containing all the fields. You may leave out SQLID if you wish. This form will provide a means for you to save, change, and view the saved SQL statements.

The steps required to run these queries in sequence just as they would have run had you used a layout like concatenating the entire SQL in code are actually very simple.

We need to basically take these steps:

• Identify the SQL statements that we need to execute for a particular procedure.
• Find out the first SQL statement in sequence (SQLSequence) for all the statements found for the identified procedure.
• Replace all quotes with Chr(34) in the SQL statement.
• Execute the action query.

With some error trapping to handle syntax errors etc., and a function to replace quotes, we have everything ready to go. A sample usage of this concept is the code is as follows:

'*************** Code Start ***************
' This code was originally written by Dev Ashish. 
' It is not to be altered or distributed, 
' except as part of an application. 
' You are free to use it in any application,  
' provided the copyright notice is left unchanged.
'
' Code courtesy of
' Dev Ashish
'
Sub sSomeSubRoutine()
Dim lodb As Database, strSQL As String
Dim loSQLRS As Recordset, lngCurrent As Long
Dim varTmp As Variant
Const cERR_GRACEFUL_EXIT = vbObjectError + 20

    On Local Error GoTo Err_handler
    varTmp = SysCmd(acSysCmdSetStatus, "Starting Batch process...")
    Set lodb = CurrentDb
    Set loSQLRS = lodb.OpenRecordset("Select * from tblSQL where" _
                    & " CalledFromProc='sStartOutSiderProcess'", dbOpenSnapshot)
    lngCurrent = 1
    With loSQLRS
        .FindFirst "SQLSequence=" & lngCurrent
        If .NoMatch Then Err.Raise cERR_GRACEFUL_EXIT
        Do While Not .NoMatch
            strSQL = adhHandleQuotes(!SQLString)
            lodb.Execute strSQL, dbFailOnError
            lngCurrent = lngCurrent + 1
            .FindFirst "SQLSequence=" & lngCurrent
        Loop
    End With
    
Exit_Here:
    varTmp = SysCmd(acSysCmdClearStatus)
    Set loSQLRS = Nothing
    Set lodb = Nothing
    Exit Sub
Err_handler:
    Dim strXX As String
    Select Case Err.Number
        Case 3065:      'SQL is a Select statement
            strXX = "You can only run Action queries by the Execute method."
            strXX = strXX & vbCrLf & "Please change the SQL to an action statement."
            strXX = strXX & vbCrLf & vbCrLf & loSQLRS!SQLString
            MsgBox strXX, vbCritical + vbOKOnly, "Error in SQL Statement"
        Case 3075:      'Syntax error in SQL statement
            strXX = "The following SQL statement has syntax error."
            strXX = strXX & vbCrLf & "Please verify the SQL string and try again."
            strXX = strXX & vbCrLf & vbCrLf & loSQLRS!SQLString
            MsgBox strXX, vbCritical + vbOKOnly, "Error in SQL Statement"
        Case cERR_GRACEFUL_EXIT:
        Case Else:
            strXX = "Proc: sSomeSubRoutine"
            strXX = strXX & vbCrLf & "Error #: " & Err.Number
            strXX = strXX & vbCrLf & "Description: " & Err.Description
            If Not (loSQLRS Is Nothing) And Not (lodb Is Nothing) Then
                strXX = strXX & vbCrLf & "The last action query " & vbCrLf & vbCrLf & _
                        loSQLRS!SQLString & vbCrLf & vbCrLf & "affected " & _
                        lodb.RecordsAffected & " records"
            End If
            MsgBox strXX, vbExclamation + vbOKOnly, "Runtime Error"
    End Select
    Resume Exit_Here
End Sub


Public Function adhHandleQuotes(strValue As String) As String
    ' Fix up all instances of a quote within a string by
    ' breaking up the string, and inserting Chr$(34) whereever
    ' you find a quote within the string.  This way, Jet can
    ' handle the string for searching.
    '
    ' From Access 97 Developer's Handbook
    ' by Litwin, Getz, and Gilbert (Sybex)
    ' Copyright 1997.  All rights reserved.
    '
    ' Solution suggested by Jurgen Welz, a diligent reader.

    ' In:
    '   strValue:   Value to fix up.
    ' Out:
    '   Return value: the text, with quotes fixed up.
    ' Requires:
    '   adhReplace (or some other function that will replace
    '       one string with another)
    '
    ' Example:
    '    adhHandleQuotes("John "Big-Boy" O'Neil") returns
    '     "John " & Chr$(34) & "Big-Boy" & Chr$(34) & " O'Neil"

   Const QUOTE As String = """"

   adhHandleQuotes = QUOTE & adhReplace(strValue, QUOTE, _
    QUOTE & " & Chr$(34) & " & QUOTE) & QUOTE
 End Function

 Function adhReplace(ByVal varValue As Variant, _
  ByVal strFind As String, ByVal strReplace As String) As Variant

     ' Replace all instances of strFind with strReplace in varValue.

     ' From Access 97 Developer's Handbook
     ' by Litwin, Getz, and Gilbert (Sybex)
     ' Copyright 1997.  All rights reserved.

     ' In:
     '    varValue: value you want to modify
     '    strFind: string to find
     '    strReplace: string to replace strFind with
     '
     ' Out:
     '    Return value: varValue, with all occurrences of strFind
     '     replaced with strReplace.

     Dim intLenFind As Integer
     Dim intLenReplace As Integer
     Dim intPos As Integer

     If IsNull(varValue) Then
         adhReplace = Null
     Else
         intLenFind = Len(strFind)
         intLenReplace = Len(strReplace)

         intPos = 1
         Do
             intPos = InStr(intPos, varValue, strFind)
             If intPos > 0 Then
                 varValue = Left(varValue, intPos - 1) & _
                  strReplace & Mid(varValue, intPos + intLenFind)
                 intPos = intPos + intLenReplace
             End If
         Loop Until intPos = 0
     End If
     adhReplace = varValue
 End Function
'****************** Code End *****************

The benefits of this approach:

• The SQL is in ready condition to be pasted into the SQL view of a query object. No longer do you have to remove underscores and concatenation operators.
• Need to have a particular statement execute in a different place? Simply change the SQLSequence for a particular procedure!
• Maintain the SQLDescription entries and you will find the code a whole lot easier to understand when you revisit it down the road. You can even create a report on the table sorted by CallingProcName and SQLSequence fields respectively and viola! You have instant documentation.
• Need I mention the pages of codes that we just got rid of? Everything is controlled by one single controlling procedure.
• Want to "test run" a certain number of statements? Simply put the statements you don't want to run "out of sequence". That is, if you want to run statements 1 through 10, but not 11 and 12; set the SQLSequence for statements 11 and 12 to say, 20 and 21. Since the logic of the master procedure looks for sequentially incrementing SQLSequence values, the code will stop after executing the 12th statement.