The ADO client-side cursor engine supports only one type of cursor, the static cursor, so this is
what the CursorType property must be set to.
ADO has a lock type specifically designed for disconnected recordsets called Batch Optimistic.
The Batch Optimistic lock type makes it possible to reconnect the disconnected recordset to the
database and update the database with records that have been modified while the recordset was
disconnected. This operation is beyond the scope of this chapter, so note that the Batch Optimistic
lock type is required in order to create a disconnected recordset.
Opening the recordset is the next step. This example used a plain text SQL query. This is not a
requirement. You can create a disconnected recordset from almost any source that can be used to
create a standard recordset. The client-side cursor engine lacks a few capabilities, however; mul-
tiple recordsets are one example.
The final step is disconnecting the recordset from the data source. This is accomplished by set-
ting the recordset’s Connection object to Nothing. If you recall from the “Recordset Object
Properties” section, the Connection object associated with a Recordset object is accessed
through the Recordset.ActiveConnection property. Setting this property to Nothing severs
the connection between the recordset and the data source.
Now that you have a disconnected recordset to work with, what kinds of things can you do with it? Just
about any operation the Recordset object allows. Say that the user wanted to see a list of customers
located in Germany, sorted by alphabetical order. This is how you’d accomplish that task:
‘ Set the Recordset filter to display only records
‘ whose Country field is Germany.
grsData.Filter = “Country = ‘Germany’”
‘ Sort the records by CompanyName.
grsData.Sort = “CompanyName”
‘ Load the processed data onto Sheet1
If you are working in a busy multi-user environment, the data in your disconnected recordset may become
out-of-date during the course of your application due to other users inserting, updating, and deleting
records. You can solve this problem by requerying the recordset. As demonstrated by the following exam-
ple, this is a simple matter of reconnecting to the data source, executing the Recordset.Requery method,
then disconnecting from the data source:
‘ Reconnect to the data source.
Set grsData.ActiveConnection = gobjConn
‘ Rerun the Recordset object’s underlying query,
‘ Disconnect from the data source.
Set grsData.ActiveConnection = Nothing
Using ADO with Non-Standard Data Sources
This section describes how you can use ADO to access data from two common non-standard data sources
(data sources that are not strictly considered databases), Excel workbooks, and text files. Although the idea
may seem somewhat counterintuitive, ADO is often the best choice for retrieving data from workbooks
and text files because it eliminates the often lengthy process of opening them in Excel. Using ADO also
allows you to take advantage of the power of SQLto do exactly what you want in the process.
Chapter 20: Data Access with ADO