Switching Between Connection and Database Objects

With ODBCDirect, you can open a Database object and a Connection object against the same ODBC data source, and use both in your code. You can then take advantage of each object for its different capabilities.

Alternatively, you may want to create a single object and then switch to the other type when needed. To do this, use the Connection property of the Database object or the Database property of the Connection object. You can use these properties to create Connection objects from Database objects and to create Database objects from Connection objects. This is especially useful for adding ODBCDirect capabilities to existing applications that only use Database objects.

For example, you can use a Database object for most of your ODBC data access needs, but when you need to run an asynchronous query, you can create a Connection object from the Database object and then run the query on the Connection object. The following example illustrates this technique.

Sub DeleteWashingtonRecords()
	Dim dbs As Database, cnn As Connection
	Dim strSQL As String, strConnect As String

	strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;"
	strSQL = "DELETE FROM Authors WHERE State = ""WA"""

	' Open database in default workspace.
	Set dbs = OpenDatabase("", False, False, strConnect)

	' Try to create Connection object from a Database object. If workspace is an
	' ODBCDirect workspace, the query runs asynchronously. If workspace is a
	' Microsoft Jet workspace, an error occurs and the query runs synchronously.

	Err.Clear
	On Error Resume Next
	Set cnn = dbs.Connection
	If Err.Number = 0 Then
		cnn.Execute strSQL, dbRunAsync
	Else
		dbs.Execute strSQL
	End If
End Sub