One way to create and execute an SQL pass-through query is to create a QueryDef object.
Û To create and execute an SQL pass-through query using a QueryDef object
Note You can return multiple result sets from a single query. For information, see “Processing Multiple Result Sets” later in this chapter.
This example shows how to create and execute a simple SQL pass-through query named Total Orders. It uses the VerboseErrorHandler function to display any errors that occur, including all ODBC errors.
Sub SQLPassThroughQueryDef(strDbPath As String) Dim dbs As Database, qdf As QueryDef Dim rst As Recordset, intN As Integer Set dbs = OpenDatabase(strDbPath) On Error GoTo Err_SQLPassThroughQueryDef Set qdf = dbs.CreateQueryDef("Total Orders") ' Set QueryDef's Connect, SQL, and ReturnsRecords properties. With qdf .Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Pubs" .SQL = "SELECT * FROM Sales" .ReturnsRecords = True End With ' Open snapshot on query. Set rst = qdf.OpenRecordset(dbOpenSnapshot) intN = rst.RecordCount MsgBox intN & " records were returned by this query." Exit_SQLPassThroughQueryDef: On Error Resume Next rst.Close dbs.Close Set dbs = Nothing Exit Sub Err_SQLPassThroughQueryDef: ' Call error handling function to display ODBC errors. VerboseErrorHandler Resume Exit_SQLPassThroughQueryDef End Sub
The VerboseErrorHandler function is a simple error handler that exposes errors at each level in the client/server system. This function handles errors in many of the code examples found throughout this chapter.
Function VerboseErrorHandler() ' Handles single or multiple Jet errors. Dim errX As Error ' Check whether ODBC errors have occurred. If Errors.Count > 1 Then ' Loop through Errors collection. For Each errX In Errors MsgBox "Error " & errX.Number & _ ": " & errX.Description Next errX Else ' Display single VBA error. MsgBox "Error " & Err.Number & _ ": " & Err.Description End If End Function