Using an SQL Pass-Through Query with a Stored QueryDef Object

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

1. Use the CreateQueryDef method of the Database object to create a new QueryDef object. If you plan to use the QueryDef object again, save it in the database by assigning a name; if you plan to use the query one time, pass an empty string ("") for the name argument.
   
   
2. Set the Connect property of the QueryDef object to a valid ODBC connection string. This identifies the query as an SQL pass-through query to the Microsoft Jet database engine. Microsoft Jet will not parse the SQL code in the query when you specify an ODBC connection string.
   
   
3. Set the SQL property of the QueryDef object to the SQL statement you want to execute against the remote database. The syntax of the SQL statement must conform to the syntax of the remote database server. If the remote database server cannot execute the statement, a trappable error occurs.
   
   
4. If your query returns a set of records, you must also set the ReturnsRecords property of the QueryDef object to True. This instructs Microsoft Jet to prepare a Recordset object to hold the query results. If you don’t set the ReturnsRecords property to True for a query that returns records, a trappable error results.

   Note You can return multiple result sets from a single query. For information, see “Processing Multiple Result Sets” later in this chapter.

5. Use the OpenRecordset method of the QueryDef object to open the result set.

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