Creating and running queries in an ODBCDirect workspace is similar to creating and running queries in a Microsoft Jet workspace. You create the query by invoking the CreateQueryDef method on a Connection object, and then use the Execute or OpenRecordset methods on the resulting query.
You can use asynchronous queries so that users can continue using your application while the query runs. You can also give users the ability to cancel asynchronous queries if they are taking too long. The following example runs an asynchronous query.
Function DeleteLargeSales() As Boolean
Dim wrk As Workspace, rst As Recordset
Dim cnn As Connection, qdf As QueryDef
Dim strConnect As String, strSQL As String
Dim errObj As Error
On Error GoTo Err_DeleteLargeSales
' Create ODBCDirect workspace.
Set wrk = DBEngine.CreateWorkspace("ODBC", "Admin", "", dbUseODBC)
' Create connection string.
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
' Open connection on workspace.
Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
' Create QueryDef.
Set qdf = cnn.CreateQueryDef("DeleteLargeSales")
strSQL = "DELETE FROM sales WHERE qty > 100"
qdf.SQL = strSQL
' Run query asynchronously.
qdf.Execute dbRunAsync
Do Until Not qdf.StillExecuting
' Additional code runs here while query runs.
' Check StillExecuting property to determine whether query has finished.
Loop
DeleteLargeSales = True
Exit_DeleteLargeSales:
On Error Resume Next
cnn.Close
wrk.Close
Exit Function
Err_DeleteLargeSales:
For Each errObj In Errors
Debug.Print errObj.Number, errObj.Description
Next errObj
DeleteLargeSales = False
Resume Exit_DeleteLargeSales
End Function
The preceding example uses a QueryDef object on a Connection object to run an asynchronous query. You can also use the Execute method directly on the Connection object, as shown in the following example.
Dim wrk As Workspace, cnn As Connection
Dim strConnect As String
' Create ODBCDirect workspace.
Set wrk = DBEngine.CreateWorkspace("ODBC", "Admin", "", dbUseODBC)
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
' Open connection.
Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
' Execute SQL statement.
cnn.Execute "DELETE FROM sales WHERE qty > 100", dbRunAsync
cnn.Close
When you run a query asynchronously, you can use the StillExecuting property to determine if the query has completed. If the value of the StillExecuting property is True, the query has not yet completed. If you want to cancel an asynchronous query, use the Cancel method, as shown in the following example.
Function CancelAsynchQuery() As Boolean
Dim wrk As Workspace, cnn As Connection, strConnect As String
Dim errObj As Error
On Error GoTo Err_CancelAsynchQuery
Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
' Start transaction in order to roll back if needed.
wrk.BeginTrans
cnn.Execute "DELETE FROM sales WHERE qty > 100", dbRunAsync
' Perform other operations.
.
.
.
' If query is still running, cancel and roll back.
If cnn.StillExecuting Then
cnn.Cancel
wrk.Rollback
' If query is complete, commit transaction.
Else
wrk.CommitTrans
End If
CancelAsynchQuery = True
Exit_CancelAsynchQuery:
On Error Resume Next
cnn.Close
wrk.Close
Exit Function
Err_CancelAsynchQuery:
For Each errObj In Errors
Debug.Print errObj.Number, errObj.Description
Next errObj
CancelAsynchQuery = False
Resume Exit_CancelAsynchQuery
End Function
You can use the StillExecuting property and the Cancel method with QueryDef, Connection, and Recordset objects.
A Connection object can support only one asynchronous operation at a time. Also, you can’t perform another DAO operation, such as recordset manipulation, on a Connection object while an asynchronous query runs on the same Connection object. After an asynchronous query is complete, you can then begin running another asynchronous query on the same Connection object. You must first test the value of the StillExecuting property to determine whether you can start the next asynchronous operation. Depending on the capabilities of your servers, to run multiple asynchronous queries at the same time, you may need to create separate Connection objects and run each asynchronous query on its own Connection object.
When running an action query as an asynchronous query, you’ll generally want to run it as part of a transaction. Be aware, however, that if you call the CommitTrans method while the asynchronous query is still running, your code will pause at the CommitTrans method until the query finishes. For this reason, it is more efficient to periodically check the StillExecuting property and continue to perform other work while the query runs. Once the StillExecuting property returns False, you can then call the CommitTrans method. This prevents your code from pausing at the CommitTrans method.
Note If you cancel an action query that is not part of a transaction, the query updates records up to the point where you called the Cancel method. The operation will be partially complete and will not be rolled back. For this reason, you should use the Cancel method on an action query only within the scope of a transaction. Additionally, if you start an asynchronous query in a procedure and the procedure exits before the query has completed, the query will continue to run.
To improve performance when you’re retrieving data from an ODBC data source, you can cache records locally. A cache is a space in local memory that holds the data most recently retrieved from the server. If you’re performing repeated operations on a set of data, caching that data makes those operations faster because you don’t have to retrieve the data from the server each time you need it.
In ODBCDirect queries, use the CacheSize property of the QueryDef object to specify the number of records to cache. The default cache size is 100 records. The following example shows how to reset the cache size to 200 records.
Sub SetCacheSize()
Dim wrk As Workspace, qdf As QueryDef, rst As Recordset
Dim cnn As Connection, strConnect As String
Set wrk = CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
' Open connection.
Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
' Create temporary QueryDef.
Set qdf = cnn.CreateQueryDef("tempquery")
With qdf
.SQL = "SELECT * FROM roysched"
.CacheSize = 40
Set rst = .OpenRecordset()
End With
' Print recordset to Debug window.
PrintRecordset rst
rst.Close
cnn.Close
End Sub