Article ID: 164481
Article Last Modified on 1/19/2007
APPLIES TO
- Microsoft Access 97 Standard Edition
This article was previously published under Q164481
Advanced: Requires expert coding, interoperability, and multiuser skills.
SUMMARY
This article contains tips on how to convert an application that currently uses the Microsoft Jet database engine and Data Access Objects (DAO) so that it uses ODBCDirect. ODBCDirect is a technology that enables you to work with ODBC database servers without loading the Microsoft Jet database engine. ODBCDirect relies on the Microsoft DAO 3.5 object model, so that you can easily modify your existing DAO code to take advantage of ODBCDirect.
This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.
This article covers the following topics:
- Changing the Type of Workspace You Use
- Changing the Database You Open
- Handling Data Definition Language (DDL) Operations
- Creating and Using QueryDef Objects
- Opening Recordset Objects
- Using Parameterized Queries
- Performing Batch Optimistic Updating
MORE INFORMATION
Changing the Type of Workspace You Use
The first thing you must do when implementing ODBCDirect is to create ODBCDirect Workspace objects in your code. In Microsoft Access 97, you can create ODBCDirect workspaces in two ways:
If you anticipate using ODBCDirect workspaces primarily, you can set the DefaultType property of the DBEngine object to dbUseODBC. After you set that property once, each new Workspace object you create defaults to ODBCDirect until you change the DefaultType to dbUseJet. The following example creates an ODBCDirect workspace by default:
Dim wrkODBC As Workspace DBEngine.DefaultType = dbUseODBC Set wrkODBC = DBEngine.CreateWorkspace("NewODBCWrk", "admin", "")If you want your code to be self-documenting, or if you anticipate using a combination of Microsoft Jet and ODBCDirect workspaces in your application, you can pass the dbUseODBC constant as the fourth argument each time you use the CreateWorkspace method, as in the following example:
Dim wrkODBC as Workspace Set wrkODBC = DBEngine.CreateWorkspace("NewODBCWrk", "admin", "", _ dbUseODBC)
Once you create a workspace, its Type property is read-only, and is set to either dbUseJet or dbUseODBC.
Changing the Database You Open
Prior to Microsoft Access 97 and ODBCDirect, the only way to connect to an ODBC data source was through the Database object. ODBCDirect offers you two connection options: you can use the traditional call to the OpenDatabase method, or you can create a Connection object using the OpenConnection method. The difference between the Database object and the Connection object is that the performance of the Connection object is tuned for remote database connectivity. For example, the Connection object can perform asynchronous operations and can create temporary QueryDef objects against remote data; in contrast, the Database object follows the traditional DAO model using the Jet database engine.
Using the Database Object with ODBCDirect You can connect to an ODBC data source by using the OpenDatabase method to open a Database object. However, the Database object in an ODBCDirect workspace does not support all the functionality of a Connection object. For example, when you use a Database object, you cannot connect asynchronously, run queries asynchronously, or define QueryDef objects that represent queries in the ODBC data source. To connect to an ODBC data source with the OpenDatabase method in an ODBCDirect workspace, you must specify a valid connect string for the connect argument of the OpenDatabase method, as shown in the following example:
Dim ODBCWorkSp as Workspace Dim MyDB as Database Dim strConnect As String StrConnect = "ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=Pubs" Set ODBCWorkSp = DBEngine.CreateWorkspace("NewODBCDirect", "admin", _ "", dbUseODBC) Set MyDB = ODBCWorkSp.OpenDatabase("Pubs", dbDriverNoPrompt, False, _ strConnect)Using the Connection Object with ODBCDirect In place of the OpenDatabase method, you can use the OpenConnection method to establish an ODBCDirect connection. The syntax for opening a Connection object using ODBCDirect is:
Set connection = workspace.OpenConnection (name, options, readonly, _ connect)In this syntax, the workspace argument is the name of the ODBCDirect workspace from which you are creating the new Connection object. The connect argument is a valid connect string that supplies parameters to the ODBC driver manager. These parameters can include user name, password, default database, and data source name (DSN). The connect argument overrides the value in the name argument; if you specify a registered ODBC DSN in the connect argument, then the name argument can be any valid string. If a valid ODBC DSN is not included in the connect argument, then the name argument must refer to a valid ODBC DSN. Note that all connection strings start with "ODBC;" and must contain a series of values required by the ODBC driver to access data. The minimum requirements for the connect argument include a userID, a password, and a DSN, as shown below:
ODBC;UID=UserName;PWD=MyPassWord;DSN=DataSourceNameNOTE: If one or more required arguments is missing from your connection string, the ODBC driver manager will prompt you for the missing information if you use any of the following constants as the second argument of the OpenConnection method:
dbDriverPrompt dbDriverComplete dbDriverCompleteRequiredIf you do not want to be prompted for missing information, make sure your connection string contains all the required information, or use the dbDriverNoPrompt constant as the second argument of the OpenConnection method. In some cases, opening connections to data sources can take a long time. For that reason, you may want to open your connections asynchronously. This allows other users to work in your application while the connection is being established. To open a connection asynchronously, add the dbRunAsync constant to the options argument of the OpenConnection method. When you open an asynchronous connection, you can use the Cancel property of the Connection object to cancel the connection if it takes too long to connect. In addition, if you want to check to see if the connection has been established, you can check the StillExecuting property of the Connection object, as shown in the following example:
Sub CancelConnectionX() Dim wrkODBC As Workspace Dim conODBC As Connection Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", "", _ dbUseODBC) ' Open the connection asynchronously. Set conODBC = wrkODBC.OpenConnection("Publishers", _ dbDriverNoPrompt + dbRunAsync, False, _ "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers") ' If the connection has not been made, ask the user ' if he/she wants to keep waiting. If the user does not, cancel ' the connection and exit the procedure. Do While conODBC.StillExecuting If MsgBox("No connection yet--keep waiting?", _ vbYesNo) = vbNo Then conODBC.Cancel MsgBox "Connection cancelled!" wrkODBC.Close Exit Sub End If Loop ' Close the Connection and Workspace objects. conODBC.Close wrkODBC.Close End SubSwitching Between Connection and Database Objects Using ODBCDirect With ODBCDirect, you can open a Database object and a Connection object for the same ODBC data source and use both in your code. This allows you to take advantage of the capabilities of each type of object. Alternately, you may want to create a single object and then switch types when you need to. To switch back and forth, use the Connection property of the Database object or the Database property of the Connection object. Use these properties to create Connection objects from Database objects, and to create Database objects from Connection objects. For example, you can use a Database object for most of your ODBC data access, but when you want 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, taken from the Microsoft Office 97, Visual Basic Programmer's Guide, illustrates this technique:
Sub DeleteRecords() Dim dbs As Database Dim strConnect As String Dim cnn Connection ' Open database in default workspace strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;" Set dbs = OpenDatabase("", False, False, strConnect) ' Try to create a 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 = 0 On Error Resume Next Set cnn = dbs.Connection ' Check to verify whether or not the currently opened workspace ' is an ODBCDirect workspace. ' If there was no error, then it is ODBCDirect Workspace. If Err = 0 Then cnn.Execute "DELETE FROM Authors", dbRunAsync Else dbs.Execute "DELETE FROM Authors" End If End Sub
Handling Data Definition Language (DDL) Operations
DAO's ODBCDirect functionality does not support the TableDefs or Indexes collection. This means that an application that programmatically creates new TableDef objects or looks up indexes in the Indexes collection of a TableDef object will not work in the ODBCDirect object model of DAO version 3.5. There are two ways you can work around this limitation:
- You can create a Microsoft Jet workspace and open a Database object against your ODBC data source. Once the connection has been established, you are free to perform all DDL operations using Microsoft Jet.
- You can execute SQL calls to create and find objects. You can do this with an SQL pass-through query, or by creating a QueryDef object using an ODBCDirect connection. SQL calls are a good choice if you do not want to use the Microsoft Jet database engine. However, if you want to limit the amount of code you have to change in your application, the Microsoft Jet path works very well.
Creating and Using QueryDef Objects
When you use the OpenDatabase method in an ODBCDirect workspace to connect to an ODBC data source, the CreateQueryDef method is not supported. Therefore, the only way to successfully execute the CreateQueryDef method against an ODBC data source using ODBCDirect, is through a Connection object. If you have existing code that uses the CreateQueryDef method, and the ODBCDirect connection was established using a Database object, you must change your CreateQueryDef calls to execute on the Connection property of the Database object rather than on the Database object itself.
NOTE: QueryDef objects that you create in an ODBCDirect workspace are not stored in the database, and are lost when the Workspace object is closed or goes out of scope.
QueryDef objects are powerful because they are prepared and optimized statements that can be called again and again. QueryDef objects, like Connection objects, support asynchronous execution through the Execute and OpenRecordset methods. Also, you can use the QueryDef object to set up properties for the resulting Recordset. For example, when you use ODBCDirect, you can use the CacheSize method of a QueryDef object to limit the number of records cached locally. The following example illustrates this technique:
Sub SetCacheSize()
Dim wrksp As Workspace, qdf As QueryDef, rst As Recordset
Dim cnn As Connection, strConnect As String
Set wrksp = CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
strConnect = "ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=Pubs"
Set cnn = wrksp.OpenConnection("", dbDriverNoPrompt, False, _
strConnect)
Set qdf = cnn.CreateQueryDef("tempqd")
qdf.SQL = "Select * from authors"
'The local cache for the Recordset is 200 records
qdf.CacheSize = 200
Set rst = qdf.OpenRecordset()
Debug.Print rst.CacheSize
rst.Close
cnn.Close
End Sub
For more information about QueryDef objects and their properties, search the Help Index for "QueryDef objects," and then select "QueryDef Object (DAO)."
Opening Recordset Objects
Another consideration when you use ODBCDirect workspaces is that recordsets open differently by default than they do in a Jet workspace. For example, Recordset objects opened in an ODBCDirect workspace default to the fastest Recordset type, which is a forward-only, read-only Recordset.
The syntax for creating a Recordset object is:
Set rs = object.OpenRecordset(source, type , options, lockedits)
In this syntax, the Source argument is required; it refers to the name of the table, query, view, or an SQL statement that returns records. The Type argument is optional; it indicates the type of Recordset to open or the manner in which records are retrieved from the server and buffered. The constants you can use for the Type argument in ODBCDirect are:
dbOpenDynaset
dbOpenDynamic
dbOpenSnapShot
dbOpenForwardOnly
NOTE: If you do not specify a Type argument with the OpenRecordset method in an ODBCDirect workspace, the object defaults to dbOpenForwardOnly. In order to update records, or to scroll backward through the recordset, be sure to use dbOpenDynaset or dbOpenDynamic in the Type argument.
The Options argument is also optional; it specifies the characteristics of the new Recordset. The Options argument can be any of the following constants in a Microsoft Jet Workspace
dbAppendOnly
dbSQLPassThrough
dbSeeChanges
dbDenyWrite
dbDenyRead
dbForwardOnly
dbReadOnly
dbRunAsync
dbExecDirect
dbInconsistent
dbConsistent
However, you can only supply a zero (0) for the Options argument in an ODBCDirect Workspace, for example:
Set rs=cn.OpenRecordset("Source", dbOpenDynaset, 0, dbOptimistic)
In a Microsoft Jet Workspace, you can use constants in the Options argument in combination, for example:
Set rs=cn.OpenRecordset("Source", dbOpenDynaset,dbSeeChanges+dbRunAsync)
However, you must be careful when choosing the combinations you create. The type you choose must work with the options that can be selected for that type. For example, in the following statement the dbSeeChanges option is not necessary with a dbOpenSnapShot type recordset:
Set rs=cn.OpenRecordset("Source", dbOpenSnapShot, dbSeeChanges _
+ dbConsistent
The LockEdits argument is optional; it specifies the record locking mechanism to use if you open your recordset as dbOpenDynaset or dbOpenDynamic. The constants you can use in this argument are:
dbPessimistic
dbOptimistic
dbOptimisticValue
dbOptimisticBatch
Using Parameterized Queries
When you work with parameterized QueryDef objects in an ODBCDirect workspace, you continue to work with the Parameter object common to Microsoft Jet database engine version 3.0. One new feature which has been added to Parameter objects which makes them more useful for client/server applications is the Direction property. This property sets or returns a value that indicates whether a Parameter object represents an input parameter, an output parameter, both input and output, or the return value from the procedure. Although the ODBC driver will attempt to determine the direction of the parameter, the Direction property is read/write so you can set it if you need to.
NOTE: Some ODBC servers require you to specify information in the Direction property before you execute the query; others will set the property for you.
ODBCDirect does not support named parameters. Therefore, the syntax for a parameter in a SQL statement in ODBCDirect workspaces is a question mark (?), instead of a name as it is in Microsoft Jet workspaces. For example, the Microsoft Jet SQL expression "SELECT * FROM Employees WHERE LastName = [txtName]" creates a parameter named txtName. With ODBCDirect, the same SQL statement reads as "SELECT * FROM Employees WHERE LastName = ?"
For an example that uses the Direction property, search the Help Index for "Direction property."
Performing Batch Optimistic Updating
Another advantage to using ODBCDirect is the ability to decrease network traffic between client and server computers using Batch Optimistic Updating. This means that all changes to a recordset are cached locally until you specifically tell DAO to flush all changes to the server. This is accomplished by specifying the type argument dbUpdateBatch when you call the Update method.
Before you call the Update method, it is recommended that you specify how individual rows will be updated. To accomplish this, you can use the UpdateOptions property of the Recordset object. Unless you specify otherwise, the UpdateOptions property defaults to the following:
dbCriteriaKey+dbCriteriaUpdate
This means that Microsoft Access is going to use the primary key value when it constructs the Where clause during the batch update. This property accepts any combination of the following constants:
Constant Description
-----------------------------------------------------------------------
dbCriteriaKey (Default) Uses just the key column(s) in the
Where clause.
dbCriteriaModValues Uses the key column(s) and all updated columns
in the Where clause.
dbCriteriaAllCols Uses the key column(s) and all the columns in
the Where clause.
dbCriteriaTimeStamp Uses just the timestamp column if available
(will generate a run-time error if no timestamp
column is in the result set).
dbCriteriaDeleteInsert Uses a set of DELETE and INSERT statements for
each modified row.
dbCriteriaUpdate (Default) Uses an UPDATE statement for each
modified row.
To use Batch Optimistic Updating in Microsoft Access 97, you must satisfy the following conditions:
- You must use an ODBCDirect workspace
- the DefaultCursorDriver property of the workspace must be set to dbUseClientBatch at the time the Connection object is opened.
- The Recordset object must be opened with the OpenRecordset method's locktype argument set to dbOptimisticBatch
The following example illustrates the use of Batch Optimistic Updating using an ODBCDirect workspace.
Function BatchUpdate()
Dim wrkMain As Workspace
Dim conMain As Connection
Dim rstTemp As Recordset
Dim ConnStr as String
Set wrkMain = CreateWorkspace("ODBCWorkspace", "admin", "", _
dbUseODBC)
' This DefaultCursorDriver setting is required for
' batch updating.
wrkMain.DefaultCursorDriver = dbUseClientBatchCursor
ConnStr = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
Set conMain = wrkMain.OpenConnection("Publishers", _
dbDriverNoPrompt, False, ConnStr)
' The following locking argument (dbOptimisticBatch) is required for
' batch updating.
Set rstTemp = conMain.OpenRecordset("SELECT * FROM Authors", _
dbOpenDynaset, dbRunAsync, _
dbOptimisticBatch)
With rstTemp
' Increase the number of statements sent to the server
' during a single batch update, thereby reducing the
' number of times an update would have to access the
' server.
.BatchSize = 25
' Change the UpdateOptions property so that the WHERE
' clause of any batched statements going to the server
' will include any updated columns in addition to the
' key column(s). In addition, DAO 3.5 is going to use an Update
' statement for each modified row.
.UpdateOptions = dbCriteriaAllCols + dbCriteriaUpdate
Do While Not rstTemp.EOF
rstTemp.Edit
rstTemp.Fields("au_lname") = rstTemp.Fields("au_lname") & _
" Test"
rstTemp.Update
rstTemp.MoveNext
Loop
rstTemp.Update (dbUpdateBatch)
.Close
End With
conMain.Close
wrkMain.Close
End Function
REFERENCES
Microsoft Office 97 "Visual Basic Programmer's Guide," Chapter 11, "Data Access Objects," pages 289-312
For more information about using ODBCDirect, search the Help Index for "ODBCDirect workspaces," or ask the Microsoft Access 97 Office Assistant.
Keywords: kbhowto kbinterop kbprogramming KB164481
