Data Access Object’s ODBCDirect

Presented by: Emily B. Kruglick

Introduction

DAO 3.5 is the newest version of the Microsoft® Data Access Objects (DAO). It will be available in Microsoft ® Office 97, Microsoft ® Visual Basic® 5.0, and Microsoft ® Visual C++® 5.0. DAO is the object model used by developers to programmatically access data from different databases, including Microsoft Jet. Most people don't even know they are using it, but if you have written a Visual Basic or Microsoft® Access database program, it is extremely likely that you have used DAO.

So what is special about DAO 3.5? DAO 3.5 is the first version of DAO that brings extraordinary power to developers who are developing client/server database applications. In addition to providing programmatic access to the Microsoft Jet database engine, DAO has always given users the power to access data in several back ends, including Microsoft FoxPro®, dBASE, and many other file share database providers. Now DAO also provides access to remote database servers like Microsoft SQL Server and Oracle databases that are an imperative part of client/server applications.

ODBCDirect is a second path that DAO 3.5 provides for accessing remote data. In previous released versions of DAO, if you wanted to access data through Open Database Connectivity (ODBC), DAO passed your calls to the Microsoft Jet database engine, which then passed the calls along to ODBC. This path, though it got the job done, was not always the most efficient way. Any developer using this path soon realized two weaknesses. First, it required loading Microsoft Jet, even though the actual database being accessed was not a Microsoft Jet database. Second, it could be slower due to the extra functionality Jet provided and footprint it required. Now, with ODBCDirect, developers have a direct path to ODBC and their data.

So now there are two paths to data with one DAO interface, ODBCDirect and Microsoft Jet. One of the two paths will be more appropriate, depending on what the application is doing. It might even make sense to use both paths in the same application, depending on the tasks at hand.

For example, if your application is hitting a Microsoft Jet .mdb or any other file share database, you should definitely use the Microsoft Jet path, because ODBCDirect was created specifically for accessing remote data. Another reason to use Microsoft Jet would be if you want to join data in tables stored in different back ends, for instance Oracle and Microsoft SQL Server data. You will need to use Microsoft Jet in this case, for it provides heterogeneous joins. A third reason is if you are planning on doing a lot of data definition language (DDL), you may choose to go through Microsoft Jet, because ODBCDirect does not provide table definitions or the ability to create tables through DAO's object model. You can still create tables using ODBCDirect, by executing SQL statements, but it's more convenient to use the Jet TableDef object.

So after that description, does it seem like ODBCDirect doesn't provide much functionality? Well, most client/server developers simply want to read data in, change it, and write it back to the back end, and they want to do it fast. They also want to be able to get at powerful features supplied by remote data servers.

At this point, you may be asking, what about Remote Database Objects (RDO), which shipped with Visual Basic 4.0 Enterprise edition? Doesn't RDO do all this? Why not use it? There are reasons for using both RDO and DAO. The differences are summarized in the following table:

To summarize, RDO and DAO are very similar. RDO is more specialized and provides slightly more functionality, but requires a license for, and only ships with Visual Basic Enterprise Edition. DAO is more flexible and portable, at the cost of some advanced functionality.

So now that you know what DAO 3.5 is and when you would want ODBCDirect or Microsoft Jet, it's time to see how to use ODBCDirect.

Activating ODBCDirect

Since the workspace is the path through which all database interaction takes place, there is now a second type of workspace. The first is a workspace connected to the Microsoft Jet engine, termed a Microsoft Jet workspace. It's likely you're already familiar with it. The new workspace type is a workspace connected to ODBC, termed an ODBCDirect workspace. Therefore, it is at the workspace level that the developer will choose how to access the data. This is cool because it allows the developer to begin coding the application against a Microsoft Jet database using a Microsoft Jet workspace, and then later convert it to go against a Microsoft SQL Server database through ODBCDirect, by simply changing the type of workspace that the application uses. You should note, however, that for the changeover to go smoothly, the developer, while coding the original application, must keep in mind what functionality is supported in both workspaces. (For more information, see "Tips for Converting Your Application to ODBCDirect.") It is also recommended that once the application is converted, it should be optimized for the specific workspace, because each workspace has its own strengths and weaknesses.

Enough about the types of workspaces-how do you create one? The most direct way is to specifically create the workspace using the CreateWorkspace method of the DBEngine object. In DAO 3.5, there is a new optional fourth parameter called Type, which allows you to specify which type of workspace to create. Pass to this parameter either the constant dbUseJet for a Microsoft Jet workspace or dbUseODBC for an ODBC workspace:

Dim wksODBC as Workspace
Dim wksJet as Workspace

    Set wksODBC = DBEngine.CreateWorkspace( _
        "HelloODBCWS", "sa", "", dbUseODBC)

    Set wksJet = DBEngine.CreateWorkspace( _
        "HelloJetWS", "admin", "")

Notice that the new parameter is optional. So what happens if you don't pass anything? Well, the default is used, but what is that? On the DBEngine, which is what the workspace is created from, there is a new property, DefaultType, which dictates the type of workspace to create if no type is specified. This property defaults to dbUseJet. So if you do nothing to your existing code, it will work exactly as always. However, if you change it to dbUseODBC, then all workspaces you create after that point without specifying a workspace type will be ODBCDirect workspaces.

You may already know that DAO creates a default workspace as soon as it is needed. By setting the DefaultType before any calls are made that need a default workspace, you can dictate which type of workspace will be the default workspace:

Dim wksDefault as Workspace

    DBEngine.DefaultType = dbUseODBC
    ' Since the default type is dbUseODBC, the
    ' default workspace will be a ODBCDirect
    ' Workspace.
    Set wksDefault = DBEngine.Workspaces(0)

Connecting to Databases

Now that you can create an ODBCDirect workspace, what can you do with it? Let's start with the new workspace features that DAO exposes to support ODBCDirect functionality. First, there is a Type property now on the workspace. It only makes sense, since now you can have different types of workspaces (Microsoft Jet or ODBCDirect). The Type property is read-only. It is a good thing to know about if you will be writing code that will use both paths in DAO. For instance, if the Type property equals the constant dbUseJet, the program won't try to open a connection because connections are only supported by ODBCDirect. (For more information, see "Comparing Database and Connection Objects.")

The other new property on a workspace, DefaultCursorDriver, is only valid for ODBCDirect workspaces, since Microsoft Jet workspaces shield the user from cursors. By setting DefaultCursorDriver, you can tell DAO to use a local cursor, a server cursor, an optimistic batch cursor (explained later), or no cursor at all. The default is set to dbUseDefaultCursor. This lets the ODBC driver decide which cursor to use. If the ODBC Driver wants to handle the SQL calls, local cursors are used, but if it wants to pass the calls directly off to the server (if the server supports cursors), server cursors will be used. In most cases, if dbUseDefaultCursor is supplied, server cursors will be used if the server supports cursors. (Microsoft SQL Server 4.21 does not support server cursors, while Microsoft SQL Server 6.0 and 6.5 do support server cursors.) If the server does not support cursors, local cursors are used. The reason for saying "most cases" is that it is completely ODBC driver dependent; it is possible that someone could create an ODBC driver that did not use server cursors when they are available. By the same token, if you want to force the application to use a different type of cursor than the driver would pick, you can explicitly set the DefaultCursorDriver property. You should note, however, that you cannot force the application to use a cursor type that is not supported.

New in DAO 3.5 is the concept of "No Cursor," specified by setting the DefaultCursorDriver property to dbUseNoCursor. This sets up a forward-only, read-only Recordset that fetches one record at a time from the server. This Recordset requires the least overhead, but it is the least functional.

Comparing Database and Connection Objects

Once you have chosen your default cursor, it is time to establish a connection with your data. Before the arrival of ODBCDirect, there was no choice here-you simply would call OpenDatabase. ODBCDirect allows you two similar paths for getting to your database, the Database object and the Connection object. The only difference is that the Connection object is tuned more for remote database connectivity, while the Database object tends to follow the more historical DAO model.

What is meant by this? The Connection object has the ability to run asynchronous operations and create temporary QueryDef objects (prepared statements, for those of you more familiar with RDO 1.0 or ODBC), while the Database object is more compatible with the Microsoft Jet path. For example, you cannot create QueryDef objects from a Database object unless you are working with a Microsoft Jet database.

Why use the Database object? Well, if you are writing code that you want to be able to switch easily from one type of workspace to another because you are planning on changing the database back ends (that is, from Microsoft Jet to Microsoft SQL Server), using Database makes plenty of sense, since connection is not supported at all in the Microsoft Jet path. However, if your code needs to be able to use QueryDef objects against remote databases or is going to run asynchronous queries, you should use a Connection.

There is one little trick we thought you might like. We have put a Connection object property on the Database object and a Database object property on the Connection object. Through these you can easily change from one object type to the other in order to take advantage of both functional paths. This will come in handy if you have some code that may be shared between both paths. For instance, you may want to write all your code so that it can either run through Microsoft Jet or ODBCDirect, but then, much lower in the code, you may want to run a specific query asynchronously if it is available. By connecting to the database using OpenDatabase, you can use the same code whether or not you are using ODBCDirect. Then, lower in the code, you can do a check to see if you are in ODBCDirect, and, if you are, you can jump on over to the Connection object and execute the statement asynchronously. (See code example #1.)

When opening a Connection you must choose what type of ODBC prompt you would like to use. In the Microsoft Jet workspace, you will always be prompted if you have supplied insufficient or incorrect information. This behavior is also the default for ODBCDirect. It was designed this way to ensure that existing code continues to run the same way even though it is now running through ODBCDirect. Other options are to simply error if all the required information is not supplied or to prompt no matter what is supplied.

Samples of opening Connection and Database objects are:

dim dbs as database
dim cnn as connection

    set dbs = OpenDatabase("", _
        dbDriverRequired, false, _ 
        "ODBC;dsn=DBSer;database=pubs;uid=sa;pwd=pass;")

    set cnn = OpenConnection("", _
        dbDriverNoPrompt, false _
        "ODBC;dsn=DBSer;database=pubs;uid=sa;pwd=pass;")

Unleashing the Power of QueryDef Objects

So now that you have opened a Connection and are able to create and use QueryDef objects, you need to understand what they are, what they can do for you, and how they differ from QueryDef objects in a Microsoft Jet workspace. QueryDef objects created from a Connection object are temporary QueryDef objects, they are not saved to the data source. QueryDef objects are powerful, because they are prepared and optimized statements that can be called again and again.

When you set the SQL, either through the CreateQueryDef method or by changing the actual SQL property on the QueryDef, an instruction is sent to the server to prepare the statement. Then, when you execute the QueryDef or open a Recordset from the QueryDef, the prepared path is used by the server. QueryDef objects, like Connection objects, support asynchronous execution through both the Execute and OpenRecordset methods. (See "Running Async Queries.") These are major differences from QueryDef objects created for Microsoft Jet databases, which were typically saved in the database and could only be executed synchronously.

You can also use the QueryDef object to set up properties of the resulting Recordset, like the number of records to cache locally. In ODBCDirect, the CacheStart property and the FillCache method of the Recordset are not supported in favor of using the CacheSize method of a QueryDef (see "Handling Recordset Objects"). The Recordset property CacheSize is still supported, but is read-only and contains the number of records DAO will cache. An example of how to tell DAO to use a different CacheSize than the default, which is 100 records for ODBCDirect, follows:

Dim qdf as QueryDef
Dim rst as Recordset

    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

ODBCDirect QueryDef objects can also be used to execute your stored procedures. They are designed to work well with input and output parameters, as well as return values. To work with stored procedures, you simply create a QueryDef object whose definition looks very much like an ODBC call to the stored procedure:

' Create a simple stored proc
strSQL$ = "CREATE PROC myproc AS " & _
    "SELECT * FROM EMPLOYEES;"
cnn.Execute strSQL$

Set qdf = cnn.CreateQueryDef("q1", _
    "{call myproc()}")
Set rst = qdf.OpenRecordset()

Working with parameters is done using the same Parameter object you are likely already familiar with in DAO 3.0. To make them more useful for client/server applications, we've added the Direction property, which allows you to tell DAO how the parameter will function. In dealing with some servers, you will need to specify this information before executing the statement; however, other servers will set the property for you. Here is an example of using the new parameters functionality:

'Create a simple stored proc
'with a return value
strSQL$ = "CREATE PROC myproc " & _
    "(@invar int) AS " & _
    "RETURN @invar;"
cnn.Execute stSQL$

'Set up a QueryDef to talk with the
'stored procedure
Set qdf = cnn.CreateQueryDef("q1", _
    "{? = call myproc(?)}")

'Handle the parameters
qdf.Parameters(0).Direction = _
    dbParamReturnValue
qdf.Parameters(1) = 10
qdf.Execute

'Read return value
var = qdf.Parameters(0).Value

Handling Recordset Objects

Probably the hardest thing to grasp when dealing with ODBCDirect is the types of cursors it exposes. When opening a Recordset, you can specify what type of cursor to open and what locking that cursor should use. This leads to many different possibilities, so let's start as simply as possible. There are four different types of Recordset objects (or cursors) that you can open:

Note: dbOpenTable is not supported for ODBCDirect.

There are also five types of locking:

Once you start combining these, you will find some combinations will not work together; however, this is entirely dependent on the ODBC driver and remote database server. For instance, against Microsoft SQL Server 6.0 server cursors, dbOpenSnapshot only supports dbReadOnly. (See your cursor driver documentation for specifics.)

The cursor driver you are using also influences what cursors and lock types are supported. DAO passes the cursor settings directly to ODBC. This means that the driver controls the world. If it can handle the type of Recordset you are asking for, no problem; if not, it will either roll over into another type of Recordset or it will return an error. If an error occurs, DAO will place the error information in the Errors collection. Some examples of opening Recordset objects are:

Dim rst as Recordset

    set rst = dbs.OpenRecordset( _
        "select * from authors")
    set rst = dbs.OpenRecordset( _
        "select * from authors" _
        , dbOpenDynaset, 0, dbPessimistic)
    set rst = cnn.OpenRecordset("authors", _
        dbOpenDynamic, 0, dbOptimistic)

The first OpenRecordset brings up an interesting question. What are the defaults? In a Microsoft Jet workspace, the most functional Recordset is always opened. In an ODBC workspace, the default is the fastest Recordset, dbOpenForwardOnly, dbReadOnly. Therefore, if you want to edit your data, you need to make sure you supply a lock type other than dbReadOnly, and if you want to be able to scroll around your Recordset, you will need to supply a Recordset type other than dbOpenForwardOnly.

Besides the different types of Recordset objects and record locking, Recordset objects basically function the same as in DAO 3.0. There are a few more advanced areas that will be discussed later, such as running asynchronous queries, working with multiple Recordset objects, and batch optimistic cursors. Other than those, the only changes worth mentioning are that Recordset functionality has slimmed a bit. For instance, ODBCDirect does not support selecting indexes to use on the Recordset object nor can you use the Seek function. This is because against remote databases you never open a table directly, as you would in file server databases. In fact, even in the Microsoft Jet workspace remote databases don't support index and seek, because you cannot open a table-type Recordset on remote data. Also not supported are the Find methods: FindFirst, FindNext, FindPrevious, and FindLast. They are not supported because client-side navigation through records is also more of a file-based database technique. To get decent performance going against a remote database, users should allow the back end to navigate the records, not grab them all and navigate through the records on the client as the Find methods do.

One last change we should talk about is how record caching is handled. The default cache size of Microsoft Jet is one record. The default of ODBCDirect is 100. Because Microsoft Jet only caches one record at a time, DAO provided the FillCache method and the CacheStart and CacheSize properties. These work together so the user can define what data to cache and cache it. In ODBCDirect the user can either use the 100 default CacheSize or they can change it by creating a QueryDef and altering the CacheSize property to dictate how many records should be cached. (See "Unleashing the Power, QueryDef Objects.")

Controlling Multiple Recordset Objects

One of the new advanced features DAO provides is the ability to handle the return of multiple Recordset objects from one SQL call. Note that when you use multiple Recordset objects, don't plan on updating data. There are two ways to get multiple Recordset objects back, and neither allow updating. The first way is to use local cursors; they will execute multiple selects and return the data no matter what type of Recordset you request. The second is to use server cursors, though it is actually using no cursor at all, just doing simple data fetches under the covers. You do this by setting up a QueryDef with a CacheSize of 1, thus telling the server to only give you one record at a time. Then you open a Recordset as dbOpenForwardOnly and dbReadOnly from the QueryDef. You could also use the dbUseNoCursor option on the workspace's DefaultCursorDriver property, before opening the connection, to achieve this forward-only, read-only, CacheSize 1 cursor. This tells the server not to bother with cursors, just return one row at a time. In this mode (no cursor mode), SQL Server can handle opening multiple Recordset objects. Without these steps, Microsoft SQL Server will simply return an error when you try to open a multiple Recordset query.

Once you have multiple Recordset objects being returned, you can use DAO to access each Recordset. This is done by using the new NextRecordset method. It will throw away the current Recordset and replace it with the next Recordset. Then you can navigate through it as you would any Recordset. To tell if there are any Recordset objects left, you should check the return value of NextRecordset. It will be true if you have just received a valid Recordset. If there were no more Recordset objects left, NextRecordset will have returned false and the current Recordset will be empty. (See code example #2.)

Running Async Queries

ODBCDirect also exposes the ability to run queries, both row-returning and non-row-returning, asynchronously. Both work by passing dbRunAsync to the appropriate methods. To check if the operation is done, you read the StillExecuting property. If it returns true, the operation has not finished. If it is False, everything has completed. If you want to abort the operation, simply use the new Cancel method.

All right, enough overview-lets look at this in a little more detail. First, let's talk about async queries running through the execute command. You can launch an async query in ODBCDirect from the Connection object or the QueryDef object. Note that you cannot use dbRunAsync when executing from the Database object. Except for passing the dbRunAsync, all other rules are the same as if you were running any query. Once you have launched the query, you are free to go do anything else the program may need to do. This can be really nice if you want to perform some bulk operations, but don't want to make the user sit and wait for it to complete. It also finally gives the user the ability to be able to tell the program to stop executing! This is something users have wanted for a long time. If the query seems to be running too long, you can apply the Cancel method from the object that launched the execute, and the operation will be canceled. One thing to note, however, is that canceling in the middle of a bulk operation is not always a wise thing to do; it will not roll back-it will just stop updating in the middle. If you think you may cancel a bulk operation, you will want to wrap it in a transaction so you can roll back if it is not completed.

Besides adding Cancel to Connection and QueryDef objects, we have also added the StillExecuting property. This property will tell you if you currently have an asynchronous operation running. While one operation is running, you cannot launch others, because the connection to the database will be busy. Just like the Cancel method, you will check the StillExecuting property on the object that you called the execute from to see if it is done running. (See code example #3.)

Besides running asynchronous executes, you can also open Recordset objects asynchronously. This is done by supplying dbRunAsync as an option to the OpenRecordset command. Unlike the execute, the Cancel method and StillExecuting property that correspond to the opening of a Recordset are on the actual Recordset object, not the object it was launched from. If you choose to cancel an OpenRecordset call, the Recordset will become invalid and you will need to reopen the Recordset to get a valid Recordset object back. Cancel comes in handy here when more records than the user planned on are being returned and the user wants to abort the first query and then requery with a more specific request. (See code example #4.)

Supporting Batch Optimistic Updating

ODBCDirect also supports batch optimistic updating. This is new since the DAO 3.1 beta. Optimistic batch updating means the data for a given Recordset is cached locally. All changes you make to the Recordset are also cached locally until you specifically tell DAO to flush all changes to the server. Batch updating can really help speed things up, because it cuts down on the network traffic between client and server.

It is accomplished first by creating an ODBCDirect workspace and setting the DefaultCursorDriver to dbUseClientBatchCursor. This tells DAO to pass all SQL calls through the client batch cursor library. It is the only library that currently supports batch updating. Then, after opening your connection, you open a Recordset with a LockType of dbOptimisticBatch, which tells the library that you plan on doing batch updates.

At this point, you can use the Recordset as you normally would, reading and updating its data, but the data is not sent to the server. When you have updated all the data you plan to work with, you call the Update method on the Recordset, but with the flag dbUpdateBatch. All data is then flushed to the server and the updates are made.

How does DAO know which record in the Recordset relates to a particular record in the source? This is specified using the UpdateOptions property of the Recordset. The default for this is to use the primary key. However, you can override this to use all columns or the time stamp. Additionally, you can specify how record updates are executed, either by updating the record in place or by deleting and adding a new record.

What happens, though, if a collision occurs (the update tries to change a record that has been changed by another source since the process first looked at the records)? In that case, you can use the BatchCollisionCount property, to find out how many updates failed, and the BatchCollisions property. The BatchCollisions property is an array of bookmarks to your Recordset that points to records that failed. Finally, you can use the VisibleValue and OriginalValue properties of the Field object in conjunction with the Value property to determine how to reconcile the failed updates.

Viewing Errors

The last thing to talk about is how to view errors using DAO. This topic is not exactly new, but is more important in ODBCDirect, and it is not all that well known. DAO provides an Errors collection, in which it will put any error information it receives. The reason this is interesting is because when an ODBC error occurs, all that may be returned to the programmer through the Err and Error$ variable may be "3146: ODBC - Call Failed." This can be rather annoying since you don't know what went wrong. By looking in the errors collection, you just might find more useful information. The lowest index error usually is the most detailed. Here is some sample code that you can use to print out all the errors from the collection and see exactly what we are talking about:

Debug.Print Err & ":  " & Error$
For Each e In DBEngine.Errors
    Debug.Print e.Number & ":  " & e.Description
Next e

If you try to open a database and tell the driver not to prompt but don't supply the DSN in the connection string, you will get "ODBC-Call Failed" in the Error$. If you look in the Errors collection, you will find that there are actually two errors returned, and Errors(0) has much more detailed information in it. The errors returned are:

Errors(0).Description =
IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Errors(1).Description =
3146:  ODBC--call failed.

Wrapping It Up

The new ODBCDirect functionality in DAO 3.5 gives DAO users the functionality they've been asking for and waiting for, to get to ODBC data quickly, and to get at the powerful features remote data sources like Microsoft SQL Server provide, without hiding the details, and it puts that power into the DAO object model, an object model that is well-known and well-received. It is fully backward-compatible with DAO 3.0. Finally, DAO has made it easy to switch from using Microsoft Jet data to using ODBC data, allowing you to write your application using local data and then to change it to use remote data as business needs require it.

Examples

Example 1: Telling Which Workspace Your Database Is Opened In

This routine demonstrates how to tell whether your database is opened in an ODBC workspace or a Microsoft Jet workspace. It also shows how to grab the Connection object from a Database object and use it to perform an asynchronous SQL operation.

Sub DeleteRecords()
    Dim dbs As Database
    Dim cnn As Connection

    ' This will open a database in the default
    ' workspace, no matter what type of workspace it
    ' actually is (ODBC or Microsoft Jet)
    Set dbs = OpenDatabase("", False, False, _
        "ODBC;dsn=DBServer;database=pubs;uid=sa;pwd=;")

    ' Check to see if it is ODBCDirect
    fNoError = True
    On Error GoTo ErrorTrap
        Set cnn = dbs.Connection
    On Error GoTo 0

    ' If there was no error than it is ODBCDirect
    If fNoError Then
        cnn.Execute "delete from authors" _
            , dbRunAsync
        cnn.Close
    Else
        dbs.Execute "delete from authors"
        dbs.Close
    End If
Exit Sub

ErrorTrap:
    fNoError = False
    Resume Next
End Sub

Example 2: Getting Multiple Recordset Objects from the Server

These routines demonstrate how to get multiple Recordset objects back from the server and how to walk through them. It has two parts. The first demonstrates forcing local cursors to be used to get multiple Recordset objects. The second demonstrates how to move through multiple Recordset objects in DAO.

Sub GetMultipleResults()
    Dim rst As Recordset
    Dim cnn As Connection

    ' Use Local Cursors
    DefaultType = dbUseODBC
    DBEngine.Workspaces(0).DefaultCursorDriver = _
        dbUseLocalCursor
    ' Now open the connection to the database
    Set cnn = OpenConnection("", _
        dbDriverNoPrompt, false _
        "ODBC;dsn=DBServer;database=pubs;uid=sa;pwd=")

    strCmd$ = "select * from authors; " & _
        "select * from titles;"

    ' execute the SQL statement
    Set rst = cnn.OpenRecordset(strCmd$)
    ViewResults rst
    cnn.Close
End Sub

Sub ViewResults(rst As Recordset)
    Do
        While Not rst.EOF
            ' loop through each record
            For Each fld In rst.Fields
                ' print each field
                Debug.Print fld.Name & _
                    ":  " & fld.Value
            Next f
            rst.MoveNext
        Wend
    ' Get the next Recordset and stop if we are done
    Loop Until (rst.NextRecordset() = False)
End Sub

Example 3: Canceling a Bulk Operation

The following example illustrates how to cancel a bulk operation. When doing this you should always wrap the code in a transaction, because canceling the operation can leave data in an unknown state.

Sub CancelExecute()
    Dim cnn As Connection

    ' By setting this before touching
    ' the default workspace, the default
    ' workspace will be created as an
    ' ODBCDirect workspace.
    DefaultType = dbUseODBC

    ' Open a connection instead of
    ' a database because databases
    ' do not support dbRunAsync
    Set cnn = OpenConnection("", _
        dbDriverNoPrompt, false _
        "ODBC;dsn=DBServer;database=pubs;uid=sa;pwd=")

    ' Start a Transaction to be able
    ' to roll back if you cancel the
    ' operation.
    BeginTrans

    ' Execute your SQL using dbRunAsync
    cnn.Execute "delete from mytable", dbRunAsync

    ' You should always check that the
    ' query is still running before canceling
    If cnn.StillExecuting Then
        cnn.Cancel
        ' If you have canceled, roll back
        ' any records that were changed
        Rollback
    Else
        ' If if completed than go
        ' ahead and commit the changes
        CommitTrans
    End If

    ' Close the connection to the database.
    cnn.Close
End Sub

Example 4: Using dbRunAsync to Open a Recordset

The following routine demonstrates using dbRunAsync to open a Recordset. It should be noted that this can be performed from a Database or Connection object, unlike using dbRunAsync with Execute, which can only be used from a Connection object.

Sub CancelRecordset()
    Dim wks as workspace
    Dim dbs As Database
    Dim rst As Recordset

    ' Example of how to open a workspace
    set wks = CreateWorkspace("Space1", "sa", _
        "", dbUseODBC)
    ' Open a database to show you can
    ' use dbRunAsync to OpenRecordset from
    ' the database object.
    Set dbs = wks.OpenDatabase("", _
        dbDriverNoPrompt, False, _
        "ODBC;dsn=DBServer;database=pubs;uid=sa;pwd=")

    Set rst = dbs.OpenRecordset( _
        "select * from authors", _
        dbOpenDynaset, dbRunAsync)

    ' You should always check that the
    ' query is still running before canceling
    If rst.StillExecuting Then
        rst.Cancel
    Else
        rst.Close
    End If

    ' Close the database.
    dbs.Close
    wks.close
End Sub

Example 5: Working with QueryDef Objects

This routine demonstrates how to work with QueryDef objects. It shows the use of stored procedures and parameters.

Sub WorkWithQueryDefs()
    Dim cnn As Connection
    Dim qdf As QueryDef
    Dim rst As Recordset

    ' By setting this before touching
    ' the default workspace, the default
    ' workspace will be created as an
    ' ODBCDirect workspace.
    DefaultType = dbUseODBC

    ' Open a connection instead of
    ' a database because we need
    ' QueryDef support
    Set cnn = OpenConnection("", _
        dbDriverNoPrompt, false _
        "ODBC;dsn=DBServer;database=pubs;uid=sa;pwd=")

    ' Create the stored procedure
    ' This will usually be done outside the program,
    ' but is done here for clarity's sake.
    strCmd$ = "Create proc GetDataFrom" & _
        " (@state char(2))" & _
        " as select * from authors" & _
        " where state = @state"
    cnn.Execute strCmd$

    ' Create a QueryDef with one parameter
    ' When the SQL is set, the prepare is called
    Set qdf = cnn.CreateQueryDef("myquery", _
        "{call GetDataFrom (?)}")
    qdf.Parameters(0) = "WA"
    qdf.Parameters(0).Direction = dbParamInput

    ' Get the Data
    Set rst = qdf.OpenRecordset()

    ' Print out the data
    While Not rst.EOF
        Debug.Print rst!au_id
        rst.MoveNext
    Wend
    rst.Close
    qdf.Close

    ' Close the connection to the database.
    cnn.Close
End Sub

Tips for Converting Your Application to ODBCDirect

The following are hints for converting existing applications from running against Microsoft Jet databases to hitting ODBC data sources instead. It assumes the database has already been placed on the server. They also assume that you will want to use only ODBCDirect to communicate with your data on a server.

• Changing the type of workspaces you are using. If you do not create any workspaces, you still need to tell DAO that the default workspace should be an ODBCDirect workspace. This is accomplished by setting the DefaultType property of DBEngine to dbUseODBC before doing any operations that will need the default workspace. Once a workspace is created, you cannot change its type. If you are explicitly creating workspaces in your application and you have set DefaultType to dbUseODBC, all workspaces created while DefaultType has this setting will be ODBCDirect workspaces. However, if you want your code to be self-documenting or if you will be using both Microsoft Jet and ODBCDirect workspaces, you can pass a fourth parameter to each CreateWorkspace, telling it what type of workspace to create.
  
  
• Changing the database you open. You will need to change the arguments passed to OpenDatabase to represent the new database you are opening. Instead of passing a database name for the first parameter, you will now pass a connection string for the fourth parameter. Note that all connection strings start with "ODBC;". You may also decide that you would rather open connections instead of databases; if this is the case, you will need to change all your OpenDatabase calls to OpenConnection. Both functions take similar arguments, so this change will not be to much work.
  
  
• Handling DDL. ODBCDirect functionality does not support the TableDefs collection or Indexes collection. This means that if your application creates new TableDefs or looks up indexes in the index collection from a TableDef, it will no longer work. If this is a problem, there are two ways you can change your code. You can create a Microsoft Jet workspace and open a second database to the data source, doing all DDL work within it; or you can execute SQL calls to create and find objects. SQL calls are the way to go if you want to keep from loading Microsoft Jet. However, if you want to limit the amount of code that changes, the Microsoft Jet path works very well.
  
  
• Creating/using QueryDef objects. In ODBCDirect workspaces the Database object does not support the CreateQueryDef method. That is handled by the Connection object. In your code, you need to change all CreateQueryDef calls to be executed on the Connection property of the Database rather than the Database object itself, or if you have changed all OpenDatabase to OpenConnection, you won't have to worry about this. QueryDef objects created in ODBCDirect are not stored in the database and are lost when the object is closed or goes out of scope.
  
  
• Opening Recordset objects. ODBCDirect Recordset objects default to the fastest Recordset type, rather than the most functional, as in Microsoft Jet. Typically, this is a Recordset that cannot scroll backwards and is read-only. If you need more functionality in the Recordset you use, you need to specify that. If you need to scroll backwards or need bookmarks, choose a different Recordset type, such as dbOpenDynaset. If you need to update the Recordset, choose a locking type, such as dbOptimistic. Code written in a Microsoft Jet workspace that opens a Recordset with only the name argument provided and plans on editing data will no longer run in ODBCDirect, unless you change the OpenRecordset to also supply a Recordset type and locking constant that will allow updating.
  
  
• Using parameterized queries. ODBCDirect does not support named parameters. The syntax for a parameter in an SQL statement is a '?', rather than a name, as in Microsoft Jet. For example, the Microsoft Jet SQL "SELECT * FROM Employees WHERE LastName = txtName" creates a parameter txtName. In ODBCDirect, the SQL would read "SELECT * FROM Employees WHERE LastName = ?". In the parameters collection, the name of the parameter would be "Parameter1". It should also be noted that Microsoft Jet allows you to say "Parameters iAuthId Integer; SELECT * from authors where au_id = iAuthId". Since ODBCDirect does not have named parameters, you cannot use the "Parameters …;" part of the previous example.

These tips will get you up and running quickly using ODBCDirect. However, for best performance, you should always go back over the program and look for optimizations. ODBCDirect offers new and different ways to improve performance, but just getting your program running in an ODBCDirect workspace may take advantage of all the power at your fingertips.

© 1997 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Microsoft, Visual C++, and FoxPro are registered trademarks of Microsoft Corporation.

Other product or company names mentioned herein may be the trademarks of their respective owners.