ACC97: Tips for Converting Applications to Using ODBCDirect

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=DataSourceName
  						

  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:

      dbDriverPrompt
      dbDriverComplete
      dbDriverCompleteRequired
  						

  If 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 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) 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