ACC97: Tips for Converting Applications to Using ODBCDirect
ID: Q164481
|
The information in this article applies to:
SUMMARYAdvanced: Requires expert coding, interoperability, and multiuser skills.
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 INFORMATIONChanging 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
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.
Additional query words:
Keywords : kbinterop kbprg AccCon OdbcHowto
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbhowto
|