arty firms broadens the selection of routes for tapping ODBCDirect's power. This article builds on last month's Programming column by going beyond the basics to showcase a collection of advanced ODBCDirect features.
How does ODBCDirect contrast with other back-end database-connectivity options? ODBCDirect links to ODBC data more quickly and with fewer resources than going through Jet. However, Jet has its own special benefits, including heterogeneous database joins, bound forms, and controls. Developers must choose which projects will benefit most from ODBCDirect's speed and finer-grained functions versus Jet's traditional and easy route of ODBC access.
From Back End into Excel
My first sample application drops data from a SQL Server data set into an Excel spreadsheet. This shows how to drop back-end data into friendly enviro
nments and illustrates interesting ODBCDirect development issues. While this example works with Excel, it sets up the ODBCDirect workspace and connects to the Pubs database with the same code I used last month for Access. This sharing of common code among Office 97 applications shows VBA's ability to dramatically reduce development time.
Two additional points merit attention besides the actual code. The only way to derive the benefit of ODBCDirect's small footprint is to use it outside Access. This is because Access loads Jet, even when you use ODBCDirect workspaces exclusively. For Excel or other applications that rely on ODBCDirect to avoid using Access inadvertently, you must reference the DAO 3.5 Object Library explicitly. In Excel, choose the Tools menu and select References. Then scroll to Microsoft DAO 3.5 Object Library and select its check box before closing the References dialog box, as
shown in the screen
.
The listing
"From the Back End to Excel"
begins by creating a string,
stSQL
, that contains the SQL for the data that it will download. A querydef,
qdfYTD
, relies on this string. The
OpenRecordset
method for the querydef returns the rows from SQL Server to the workstation's local recordset,
rsYTD
. After some spreadsheet maintenance, a
While...Wend
loop copies the values from the DAO recordset to Sheet1, the active sheet. Offset methods move the active cell to the proper spreadsheet location before a simple assignment statement performs the copy.
Cached, Batched Updating
One of ODBCDirect's more advanced features is its ability to perform cached, local updating of server-based data. ODBCDirect introduces batch optimistic updating. This facilitates the downloading of a recordset that's used to cache the local updates and the subsequent batch updating of those records on the server.
Other database users can edit records between the time you download your records for local processing a
nd upload them back to the server. When the edits of others conflict with yours, the two versions collide on the server. ODBCDirect includes several built-in mechanisms for facilitating collision reconciliation.
First, batch optimistic updating automatically detects, counts, and marks collisions. Second, ODBCDirect provides simple techniques for overriding other changes with your own. Third, properties permit you to compare and contrast the various values of a collided field on a record. The example illustrates how to get started coding this capability.
Batch optimistic updating requires a minimum of six steps. First, you create an ODBCDirect workspace. Second, set the workspace's
DefaultCursorDriver
property to
dbUseClientBatchCursor
. Third, forge a link to a server database via an ODBCDirect connection or database object. Fourth, create a local recordset based on server-based data with the
OpenRecordset
method. You must specify
dbOptimisticBatch
for the
l
ockedits
argument. Fifth, perform edits as necessary to the local recordset cache. Last, use the recordset's
Update
method with a
dbUpdateBatch
type argument.
The second, fourth, and sixth steps are critical ones. Batch optimistic updating requires that you set the
DefaultCursorDriver
property to
dbUseClientBatchCursor
. The use of
dbOptimisticBatch
for the
lockedits
argument in step four enables batch optimistic updating. The
dbUpdateBatch
parameter for the type argument in step six instructs ODBCDirect to send the local cache to the server as a batch of records.
My code excerpt
"Cached, Batched Updating"
has four code blocks. First, it makes the back-end server connection. I show this code so you can see precisely how and where to specify the
DefaultCursorDriver
workspace property.
The second block creates a recordset,
rsSales
, and completes the invocation of batch optimistic updating
by referencing
dbOptimisticBatch
in an
OpenRecordset
method. You also need a recordset type that is not read-only, as the code shows using the
dbOpenDynaset
argument.
The third code block performs a series of edits to the cached data in its
While...Wend
loop. The
Update
method inside the loop caches the changes locally. The
Update
method outside the loop sends these updates from the local cache to the server.
The fourth code block illustrates a simple collision-reconciliation rule. It overwrites all collisions on the server with those from the local cache. After the initial batch update at the end of the third block, ODBCDirect marks those records in the local cache that collide with the server. It also retrieves the conflicting server values. The code shows how to copy data from the local cache to the server and then update them en masse. The last line uses a
dbUpdateBatch
argument that sends all the changes to the server. The
Tr
ue
parameter forces those changes over the server data.
Asynchronous Operations
ODBCDirect can open asynchronous connections, querydefs, and recordsets. ODBCDirect also lets you move asynchronously to the last record in a recordset. Each of these functions can take a while: Consider moving to the last of 2,000,000 records -- on a remote server.
ODBCDirect's asynchronous capabilities let you use this time locally while you are waiting for a remote server to respond to a command. However, you will have to write code that checks when the asynchronous activity concludes. An object's
stillExecuting
property permits you to run tasks while you wait for the property to turn false or cancel the operation if it takes too long.
Always wrap your updating code statements in a transaction when running tasks that can modify a remote database. This permits your program to "roll back" any completed updates if the task gets canceled, or if it didn't complete the operation within a speci
fied maximum duration.
ODBCDirect offers powerful ways to add value to back-end databases. VBA and DAO developers have new opportunities to serve up back-end data to users in familiar, friendly environments. I hope Microsoft sees fit to upgrade ODBCDirect with events, so that it's easier to manage asynchronous functions.
From the Back End to Excel
'Create row-returning recordset from server
stSQL = "SELECT titles.title_id, titles" & _
".title, titles.ytd_sales FROM titles"
Set qdfYTD = conPubs.CreateQueryDef("", _
stSQL)
Set rsYTD = qdfYTD.OpenRecordset
'Insert column headings
Sheets("Sheet1").Cells(1, 1).Value = "Title"
Sheets("Sheet1").Cells(1, 2).Value = _
"YTD_Sales"
Sheets("Sheet1").Cells(2, 1).Activate
'Copy rows
While Not rsYTD.EOF
ActiveCell.Value = rsYTD!Title
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = rsYTD!ytd_sales
ActiveCell.Offset(1, -1).Act
ivate
rsYTD.MoveNext
Wend
Cached, Batched Updating
'Make connection
Set wspPubs=CreateWorkspace("PubsSession", _
"admin", "", dbUseODBC)
wspPubs.DefaultCursorDriver _
= dbUseClientBatchCursor
stConnect = "ODBC;DSN=Pubs;UID=sa;PWD=;" & _
"DATABASE=Pubs"
Set conPubs = wspPubs.OpenConnection _
("", , , stConnect)
'Create cached recordset
stSQL = "SELECT Stor_id, Ord_num," & _
"title_id, qty FROM Sales"
Set rsSales = conPubs.OpenRecordset(stSQL, _
dbOpenDynaset, 0, dbOptimisticBatch)
'Modify local cache and attempt
'to update server
While Not rsSales.EOF
rsSales.Edit
rsSales!qty = rsSales!qty + 3
rsSales.Update
rsSales.MoveNext
Wend
rsSales.Update dbUpdateBatch
'Force in changes even if there
'are conflicts
intIterations=rsSales.BatchCollisionCount _
- 1
For j = 0 To intIterations
rsSales.Bookmark = _
rsSales.BatchCollisions(j)
Next j
rsSales.Update dbUpdateBatch, True
screen_link (43 Kbytes)
You must explicitly reference DAO 3.5 to use ODBCDirect outside of Access.
Rick Dobson, Ph.D., is president of CAB, Inc., a database and Internet development consultancy. You can send e-mail to him at
Rick_Dobson@msn.com
.
Using ODBCDirect's Advanced Features
