ACC97: Tips for Converting Applications to Using ODBCDirect
This article was previously published under Q164481
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article contains tips on how to convert an application that currentlyuses the Microsoft Jet database engine and Data Access Objects (DAO) sothat it uses ODBCDirect. ODBCDirect is a technology that enables you towork with ODBC database servers without loading the Microsoft Jet databaseengine. ODBCDirect relies on the Microsoft DAO 3.5 object model, so thatyou can easily modify your existing DAO code to take advantage ofODBCDirect.
This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout Visual Basic for Applications, please refer to the "BuildingApplications 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
Changing the Type of Workspace You UseThe first thing you must do when implementing ODBCDirect is to createODBCDirect Workspace objects in your code. In Microsoft Access 97, you cancreate 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 WorkspaceDBEngine.DefaultType = dbUseODBCSet 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 WorkspaceSet wrkODBC = DBEngine.CreateWorkspace("NewODBCWrk", "admin", "", _ dbUseODBC)
Changing the Database You OpenPrior to Microsoft Access 97 and ODBCDirect, the only way to connect to anODBC data source was through the Database object. ODBCDirect offers youtwo connection options: you can use the traditional call to theOpenDatabase method, or you can create a Connection object using theOpenConnection method. The difference between the Database object and theConnection object is that the performance of the Connection object istuned for remote database connectivity. For example, the Connection objectcan perform asynchronous operations and can create temporary QueryDefobjects against remote data; in contrast, the Database object follows thetraditional 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: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:
Set connection = workspace.OpenConnection (name, options, readonly, _ connect)NOTE: 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:
ODBC;UID=UserName;PWD=MyPassWord;DSN=DataSourceNameIf 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:
dbDriverPrompt dbDriverComplete dbDriverCompleteRequired
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 Sub
- Switching 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) OperationsDAO's ODBCDirect functionality does not support the TableDefs or Indexescollection. This means that an application that programmatically createsnew TableDef objects or looks up indexes in the Indexes collection of aTableDef object will not work in the ODBCDirect object model of DAOversion 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 ObjectsWhen you use the OpenDatabase method in an ODBCDirect workspace to connectto an ODBC data source, the CreateQueryDef method is not supported.Therefore, the only way to successfully execute the CreateQueryDef methodagainst an ODBC data source using ODBCDirect, is through a Connectionobject. If you have existing code that uses the CreateQueryDef method, andthe ODBCDirect connection was established using a Database object, youmust change your CreateQueryDef calls to execute on the Connectionproperty of the Database object rather than on the Database object itself.
NOTE: QueryDef objects that you create in an ODBCDirect workspace are notstored in the database, and are lost when the Workspace object is closedor goes out of scope.
QueryDef objects are powerful because they are prepared and optimizedstatements that can be called again and again. QueryDef objects, likeConnection objects, support asynchronous execution through the Execute andOpenRecordset methods. Also, you can use the QueryDef object to set upproperties for the resulting Recordset. For example, when you useODBCDirect, you can use the CacheSize method of a QueryDef object to limitthe number of records cached locally. The following example illustratesthis technique:
For more information about QueryDef objects and their properties, searchthe Help Index for "QueryDef objects," and then select "QueryDef Object(DAO)."
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
Opening Recordset ObjectsAnother consideration when you use ODBCDirect workspaces is thatrecordsets open differently by default than they do in a Jet workspace.For example, Recordset objects opened in an ODBCDirect workspace defaultto the fastest Recordset type, which is a forward-only, read-onlyRecordset.
The syntax for creating a Recordset object is:
In this syntax, the Source argument is required; it refers to thename of the table, query, view, or an SQL statement that returns records.The Type argument is optional; it indicates the type of Recordset to openor the manner in which records are retrieved from the server and buffered.The constants you can use for the Type argument in ODBCDirect are:
Set rs = object.OpenRecordset(source, type , options, lockedits)
NOTE: If you do not specify a Type argument with the OpenRecordset methodin an ODBCDirect workspace, the object defaults to dbOpenForwardOnly. Inorder to update records, or to scroll backward through the recordset, besure to use dbOpenDynaset or dbOpenDynamic in the Type argument.
dbOpenDynaset dbOpenDynamic dbOpenSnapShot dbOpenForwardOnly
The Options argument is also optional; it specifies the characteristics ofthe new Recordset. The Options argument can be any of the followingconstants in a Microsoft Jet Workspace
However, you can only supply a zero (0) for the Options argument in anODBCDirect Workspace, for example:
dbAppendOnly dbSQLPassThrough dbSeeChanges dbDenyWrite dbDenyRead dbForwardOnly dbReadOnly dbRunAsync dbExecDirect dbInconsistent dbConsistent
In a Microsoft Jet Workspace, you can use constants in the Options argumentin combination, for example:
Set rs=cn.OpenRecordset("Source", dbOpenDynaset, 0, dbOptimistic)
However, you must be careful when choosing the combinations you create.The type you choose must work with the options that can be selected forthat type. For example, in the following statement the dbSeeChanges optionis not necessary with a dbOpenSnapShot type recordset:
Set rs=cn.OpenRecordset("Source", dbOpenDynaset,dbSeeChanges+dbRunAsync)
The LockEdits argument is optional; it specifies the record lockingmechanism to use if you open your recordset as dbOpenDynaset ordbOpenDynamic. The constants you can use in this argument are:
Set rs=cn.OpenRecordset("Source", dbOpenSnapShot, dbSeeChanges _ + dbConsistent
dbPessimistic dbOptimistic dbOptimisticValue dbOptimisticBatch
Using Parameterized QueriesWhen you work with parameterized QueryDef objects in an ODBCDirectworkspace, you continue to work with the Parameter object common toMicrosoft Jet database engine version 3.0. One new feature which has beenadded to Parameter objects which makes them more useful for client/serverapplications is the Direction property. This property sets or returns avalue that indicates whether a Parameter object represents an inputparameter, an output parameter, both input and output, or the return valuefrom the procedure. Although the ODBC driver will attempt to determine thedirection of the parameter, the Direction property is read/write so youcan set it if you need to.
NOTE: Some ODBC servers require you to specify information in theDirection property before you execute the query; others will set theproperty for you.
ODBCDirect does not support named parameters. Therefore, the syntax for aparameter 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 sameSQL 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 UpdatingAnother advantage to using ODBCDirect is the ability to decrease networktraffic between client and server computers using Batch OptimisticUpdating. This means that all changes to a recordset are cached locallyuntil you specifically tell DAO to flush all changes to the server. Thisis accomplished by specifying the type argument dbUpdateBatch when youcall the Update method.
Before you call the Update method, it is recommended that you specify howindividual rows will be updated. To accomplish this, you can use theUpdateOptions property of the Recordset object. Unless you specifyotherwise, the UpdateOptions property defaults to the following:
This means that Microsoft Access is going to use the primary key valuewhen it constructs the Where clause during the batch update. Thisproperty 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 satisfythe 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
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
Microsoft Office 97 "Visual Basic Programmer's Guide," Chapter 11, "DataAccess 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.
Article ID: 164481 - Last Review: 12/04/2015 16:28:30 - Revision: 3.4
Microsoft Access 97 Standard Edition
- kbnosurvey kbarchive kbhowto kbinterop kbprogramming KB164481