Using Disconnected Recordsets, Part 2By Sean Grimaldi
In Part 1 we looked at how to create a disconnected Recordset. In this final part, we'll look at the process of creating a disconnected Recordset in detail and how to create custom, disconnected Recrodsets!
Exploring Disconnected Recordsets
Now we have a concrete example of a Recordset returning function, there are two points that should be immediately addressed. The first point is the use of constants to set Recordset properties. In order to create a disconnected Recordset two Recordset properties must be set appropriately. It is a requirement that the
property is set to
adUseClient and the
LockType property is set to
adLockBatchOptimistic. Note that the
CursorType will default to
adUseStatic if we don't explicitly state that it should be set to
If you cannot memorize these two values or you expect to use many other ADO constants you can include the file:
adovbs.inc. Since this file is approximately 500 lines of ASP, as a general rule, do not use it
unless you have to. You may also want to rename it to an
.asp file extension for security reasons.
It is more efficient to set the values you will use as constants. (For more information about using
be sure to check out this FAQ; for information
on why to give server-side includes a
.asp extension, be sure to read
As a side note, you could also use the cursor number directly, although this is somewhat sloppy programming if
you leave the comment off, as it is harder to comprehend what the code is doing.
The second, and less obvious point, is that although disconnected implies the Recordset was at one time connected; but this is not necessarily true. A disconnected Recordset can be used like an array, a collection, or a dictionary to hold a dataset. This is a valuable option for at least a couple of reasons. One reason you may want to use a Recordset to hold data rather than an array, collection, or dictionary is that all the Recordset methods that made ADO so convenient for client-server application, such as sort can be a real time saver. Performance is also a factor. Chances are that they will also be dramatically more efficient then coding your own sort algorithm , for example. Plus think what a headache recursion, required in most sort algorithms, can be. The code snippet below illustrates how to create a custom Recordset, one crafted without a database connection. (For more information on custom Recordsets, be sure to read: Using Custom Recordsets.)
Although it is usually best to write a SQL update statement to update the data, ADO can be used to update the database with the client side Recordset's data. Here is the classic example. You have written an application that gets inventory for salespeople. They store this inventory on their laptop as a persisted disconnected Recordset in an XML file (for more information on saving a Recordset's data to XML, be sure to read: Creating a Function to Stream a Recordset to XML). Every night they connect to your application by modem. If a customer wants to return an item, the salesperson updates the data on their laptop and then when they connect at night the database is updated. At this point, their local data (in a disconnected recordset) is not the same as the database server's data. When they do an update at night the two will be resynchronized.
Unfortunately, in the real world, it is not as simple as using
objRS.Update. If the database
has been updated by another salesperson, there will be a conflict between the disconnected Recordset and other
user's data. Erring on the side of data consistency, updates must check that they will not conflict with updates
other users have performed. To check if there are database conflicts use
objRS.Filter = adFilterConflictingRecords. In an active database, there will usually be
conflicting records; you can raise these so the user can make a decision as to the best course of action. This
is not a very good solution in many cases however as a typical business user, in this case, a salesperson should
not be making decisions that affect overall data consistency.
The best example I have seen of updating
disconnected Recordsets can be found in Francesco Balena's Programming Microsoft Visual Basic 6.0 (Microsoft Press).
This traveling salesperson example is a simplification of his in depth example. Just about anyone who owns Visual
Basic should own this book.
So now, you have seen that a Recordset that does not have a database connection can be very useful as a tool in your programming. It can save you time and effort and make your code more scalable.
This article reviewed disconnected Recordsets and hopefully pointed out some of the underused features that are available. In addition to greater scalability than connected Recordsets, disconnected Recordsets are handy to use. They have valuable methods that make writing code faster and less error prone, since the developer is reusing proven methods.