Executing SQL Pass-Through Queries with the Execute Method

Another way to create and execute an SQL pass-through query is to use the Execute method on the Database object.

Û To execute an SQL pass-through query by using the Execute method

1. Open the remote database, supplying a valid connect string for the connect argument.
   
   
2. To run the query, use the Execute method of the Database object and specify the dbSQLPassThrough constant in the options argument.

This example shows how to execute an SQL pass-through query from code by using the Execute method.

Sub SQLPassThroughExecute()
	Dim dbs As Database, strSQL As String

	On Error GoTo Err_SQLPassThroughExecute
	' Open remote database.
	Set dbs = OpenDatabase("", False, False, _
		"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Pubs")
	strSQL = "UPDATE Titles SET Royalty = 33 WHERE Type = 'Business'"
	' Call Execute method with pass-through flag.
	dbs.Execute strSQL, dbSQLPassThrough

Exit_SQLPassThroughExecute:
	On Error Resume Next
	dbs.Close
	Set dbs = Nothing
	Exit Sub

Err_SQLPassThroughExecute:
	VerboseErrorHandler
	Resume Exit_SQLPassThroughExecute
End Sub

Note that you can also execute an SQL pass-through query by opening a Microsoft Jet database and setting the Connect property of the Database object to point to the remote database server you want to query. Then call the Execute method of the Database object with the dbSQLPassThrough option. For example, you could rewrite the previous procedure with the following code, where strDbPath is the path to a Microsoft Jet database:

Dim dbs As Database, strSQL As String

' Open local Microsoft Jet database.
Set dbs = OpenDatabase(strDbPath)
dbs.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Pubs"
strSQL = "UPDATE Titles SET Royalty = 33 WHERE Type = 'Business'"
' Call Execute method with pass-through flag.
dbs.Execute strSQL, dbSQLPassThrough

The Microsoft Jet database may or may not contain linked ODBC tables that point to tables on the remote database server.

The data loading section of the BookSales sample application (located in the Dataload.bas module) performs several cleanup tasks on the server tables before loading new data. It deletes all the records in the Sales, TitleAuthor, Authors, Titles, and Stores tables that have been added since the instance of the application began running (it does not remove the original data in the Pubs SQL Server database). If this application was originally .mdb-based, it might delete these records by opening a Recordset and stepping through each record using the Delete method, but using a delete query is faster than programmatically stepping through individual records and deleting them.

When using SQL statements with a Microsoft Jet Workspace object, you can tell Microsoft Jet to assign more of the processing burden to the server. To do this, use the dbSQLPassThrough constant in the options argument of the Execute method. This instructs Microsoft Jet to send the command directly to the server without doing any of its own processing. For example:

dbs.Execute "DELETE stores", dbSQLPassThrough

Note that the DELETE statement is not valid Microsoft Jet SQL syntax. If you run this query without using the dbSQLPassThrough constant, a run-time error will occur. When sending a query directly to the server as a pass-through query, you must use the syntax and keywords of the server’s dialect of SQL, not Microsoft Jet SQL. Microsoft Jet will not translate a pass-through query.

Note All SQL queries are passed directly to an ODBC server when using an ODBCDirect Workspace object. For information on how to use ODBCDirect features, see “The Object Model for ODBCDirect Workspaces” later in this chapter.