Some Access Tools IV : Sample Code

  For iCurrProc = 1 To iNumProcs
    sSQL = UCase$(sSQLProcs(iCurrProc))
    If sSQL Like "USE DATABASE*" Then
      ' Opens a database
      Set dbCurrent = OpenDatabase(Trim$(Right$(sSQL, Len(sSQL) - 12)))
    ElseIf sSQL Like "CREATE DATABASE*" Then
      ' Create a database, also performs open
      Set dbCurrent = CreateDatabase(Trim$(Right$(sSQL, Len
                                     (sSQL) - 15)), DB_LANG_GENERAL)
    ElseIf sSQL Like "CLOSE DATABASE*" Then
      ' Close the active database
      dbCurrent.Close
      Set dbCurrent = Nothing
    ElseIf sSQL Like "REPAIR DATABASE*" Then
      ' Requests a database repair
      sFileName = Trim$(Right$(sSQL, Len(sSQL) - 15))
      RepairDatabase sFileName
    ElseIf sSQL Like "COMPACT DATABASE*" Then
      ' Requests a database repair
      If Dir$("SQL_EXEC.CMP") Then
        Kill "SQL_EXEC.CMP"
      End If
      sFileName = Trim$(Right$(sSQL, Len(sSQL) - 16))
      Name sFileName As "SQL_EXEC.CMP"
      CompactDatabase "SQL_EXEC.CMP", sFileName
    ElseIf sSQL Like "CREATE PROC*" Then
      ' Get the QueryDef name from the expression
      sQueryDefName = Right$(sSQL, Len(sSQL) - 12)
      sQueryDefName = Trim$(Left$(sQueryDefName, InStr
                                 (sQueryDefName, " AS ")))
      If (Left$(sQueryDefName, 1) = "[") And 
                                    (Right$(sQueryDefName, 1) = "]") Then
        sQueryDefName = Mid$(sQueryDefName, 2, Len(sQueryDefName) - 2)
      End If
      ' Get the query statement
      sSQL = Right$(sSQL, (Len(sSQL) - InStr(sSQL, " AS ")) - 3)
      ' Create and save the Querydef
      Set qdTemp = dbCurrent.CreateQueryDef(sQueryDefName, sSQL)
      qdTemp.Close
      Set qdTemp = Nothing
    Else
      ' The default case, just execute the SQL
      dbCurrent.Execute sSQL
    End If
        
    ' Update Progress indicator if it has been requested
    If giShowProgress Then
      SetProgress Nothing, gsProgressCaption, 
                           CInt((iCurrProc / iNumProcs) * 100)
    End If
  Next iCurrProc

Apart from some string manipulation to get the appropriate values from the SQL statement, the code is really very simple. We fetch the next statement from the array we built from the SQL script, and then execute it (hmmm… fetch, execute - sound familiar?). As you can see from the code above, we use VB code to provide support for Opening, Creating, Closing, Repairing, and Compacting databases, as well as for creating QueryDefs.

---