Persistent Recordsets in ADO

Stephen Forte

There are things ADO can do that no other data access method can. Among the coolest features is persistent datasets. In this article, one of the gurus of ADO shows you how to use this technology in your Access applications.

As an Access developer, by now you must have heard about ActiveX Data Objects (ADO). So, now that you're getting familiar with ADO, I can give you a look at one of ADO's advanced features: persistent recordsets. If you're not an Access 2000 user, you'll need to download ADO from the Microsoft Web site (see the sidebar, "Getting ADO").

What are persistent Recordsets?
You've probably become very comfortable with the recordset object in DAO, RDO, and ODBCDirect; you've probably been opening recordsets and modifying data for years. A new feature of ADO 2.0 is the ability to open a recordset created from your Access database (or any other database, including SQL Server and Oracle) and save the recordset to disk. You can then open the recordset that now resides on the disk and modify the data it contains. If you choose, you can then reopen a connection to the original database and re-sync the saved the recordset with the original data in the database. Since the original database might have changed since the recordset was saved, you need to follow the re-sync with a process that checks for successful updates. ADO provides a way to do this also.

Saving a recordset to disk in ADO is very easy. All you have to do is use the Save method of the ADO Recordset object. The syntax looks like this:

rst.Save Filename, FileType

In the Filename argument, you specify a valid path to output the data to. With the FileType argument, you specify what type of file format to save your recordset to. There's only one valid file type in ADO 2.0: Table Datagram. You specify this file type with the predefined constant, adPersistADTG. With ADO 2.1, you also have the ability to save a recordset in XML format using adPersistXML (ADO 2.1 ships with IE 5.0, Office 2000, and Windows 2000).

I'll demonstrate this feature of ADO with a complete code example that opens a recordset based on the Shippers table in Northwind and saves it to disk. Before saving a recordset to disk, you have to create a recordset. This code creates a Connection object for the database Saccess.MDB, using the OLE DB provider for Access (OLE DB is the technology underlying ADO):

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset

On Error GoTo Proc_Err
Set conn = New ADODB.Connection

With conn
 .Provider = "Microsoft.Jet.OLEDB.3.51"
 .ConnectionString = _
  "data source=c:\saccess\saccess.mdb"
 .Mode = adModeRead
 .Open
End With

With the Connection object created, the following code opens a recordset on the Shippers table, using the Connection object just created:

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open "Shippers", conn

Now that the recordset is created, I can save its contents to the file Saccess.DAT with this code (after first deleting any previous versions of the file):

On Error Resume Next
Kill "c:\saccess\saccess.dat"
rst.Save "c:\saccess\saccess.dat", adPersistADTG

Opening a recordset
To open the recordset from disk, all you have to do is use the Open method of a Recordset object. Instead of specifying a SQL statement to load your recordset, you just specify the name of the file you want to open as the command you pass to the Open method. You must also tell ADO that the command you're passing is a filename by using the Options parameter of the method:

rst.Open "c:\stuff\saccess\saccess.dat",,,adCmdFile

In the following code, I'll show how to load a recordset from disk and iterate through its contents, filling a string with values to populate a list box. This technique can come in handy if you're in a client/server environment and want to fill some list boxes with data from the server. If the data doesn't change that often, you can have an application that runs every night, deletes the old saved recordset on disk, and then replaces it with a new updated one. Throughout the following day, your Access application can read the saved recordset and fill list and combo boxes, or even local tables, without having to connect to the server, saving a lot of time.

This code re-opens the recordset that I created in the previous example:

Dim rst As ADODB.Recordset
Dim strRowSource As String

On Error GoTo Proc_Err
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open "c:\stuff\saccess\saccess.dat", , , adCmdFile

With the recordset open, you can iterate through the recordset exactly as if it had been created from a database, loading the data into a string with each value separated by a semi-colon:

Do Until rst.EOF
 strRowSource = strRowSource & rst!CompanyName & ";"
 rst.MoveNext
Loop

This string can be loaded directly into a ListBox's RowSource to have the box display the values from the persisted recordset. Like a regular recordset, the persisted recordset must be closed when you're done with it:

Me.lstItems.RowSource = strRowSource
rst.Close

Working offline
The real power of persisted recordsets is that you have to ability to take the saved recordset and work with it offline. When you're done with the recordset, you can send your changes back to the computer with the database to be re-synced with the data. In the following code, I'll save the Orders table of Northwind database to disk:

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
On Error Resume Next
Kill "c:\stuff\saccess\mailme.dat"

On Error GoTo Proc_Err

Set conn = New ADODB.Connection
With conn
  .Provider = "Microsoft.Jet.OLEDB.3.51"
  .ConnectionString = _
    "data source=c:\stuff\saccess\saccess.mdb"
  .Mode = adModeReadWrite
  .Open
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open "Orders", conn, adOpenKeyset, _
  adLockBatchOptimistic, adCmdTable

rst.Save "c:\stuff\saccess\mailme.dat", _
   adPersistADTG

Now that I have the Orders table saved to disk, I can e-mail it to someone who has to work with it offline, disconnected from the corporate LAN. When my partner gets the file, the recordset can be used in an unbound form in Access to look at the data and modify it. Figure 1 shows the unbound Access form used to perform this task.

The way the unbound form works is by declaring a module-level ADO recordset variable called rst. I then create the recordset object on the form's load event as shown in the following code, loading it with data from the persisted file. After I load the recordset into memory from disk, I fill the text boxes with the data from the current record in the recordset. Here's the code:

Private Sub Form_Load()

On Error GoTo Proc_Err

Set rst = New ADODB.Recordset
'Open the ADO recordset saved on disk.
rst.CursorLocation = adUseClient
rst.Open "c:\stuff\saccess\mailme.dat", , _
    adOpenKeyset, adLockBatchOptimistic, adCmdFile

'Call a routine that will fill the text
'boxes on the form with the data from
'the current record.

Me.txtID = rst!OrderID
Me.txtShipDate = rst!ShippedDate

Proc_Exit:
    Exit Sub
    
Proc_Err:
    MsgBox Err.Description
    Resume Proc_Exit
End Sub

Moving to the next record to browse through the form is easy. I just use the MoveNext method of the recordset object that I defined in the form's Declarations section. I also check the recordset's EOF property to make sure that I'm not at the end of the data. As I move through the recordset, I save the current record by using the Update method of the recordset. This effectively emulates the Access form's behavior of saving the records as you scroll through them.

Private Sub cmdNext_Click()

rst.Update "ShippedDate", Me.txtShipDate
rst.MoveNext

If rst.EOF Then
  MsgBox "There are no more items left", _
       vbInformation
Else
  Me.txtID = rst!OrderID
  Me.txtShipDate = rst!ShippedDate
End If
           
End Sub

I've chosen to retrieve the data into an Access form for simplicity in the demo. However, since ADO is a COM library that can be called from any COM-compliant language, I could have used a Visual Basic form, or even a Web page. In addition, with ADO 2.1, I could have saved the recordset to XML format. XML increases the flexibility of the recordset, since there are XML processors for many different types of systems, including Mac, UNIX, Linux, and Windows (see Michael Corning's article on XML, "Access to XML," in the March 1999 issue for more information). Further, in Access 2000, the process of binding the form to the recordset would be even simpler since I'd only have to set the form's Recordset property to the recordset that I created from the file.

Re-syncing the data
After all of the changes have been made in my unbound form, my partner can send the persisted dataset back to me. When I receive the file, I can re-sync the data. Re-syncing is a simple three-step process:

1. Open the recordset from the disk.

2. Set the recordset's ActiveConnection property to a valid connection object.

3. Use the recordset's UpdateBatch method.

The UpdateBatch method will update the records from the recordset back to the database.

Once the UpdateBatch method completes, you should loop through the recordset to check the status of each record and determine whether the record was re-synced successfully or not. Records will fail to re-sync if the record was deleted or updated since the recordset was saved to disk.

To check the status of a record's re-sync, you first must filter the recordset to see only the affected records. You can use the recordset's Filter method like this:

rst.Filter = adFilterAffectedRecords

With the filter set, you can iterate through the recordset and check the Status property of each record. ADO will return many different status values to you, but you only want to know whether your record was re-synced or not. ADO will return the constant adRecUnmodified (value=8) in the Status property if the record was unmodified by the sync.

In this loop, you can perform as much error handling as you want. This can include your own custom conflict resolution code, since each field in each record will make available both the original value and your updated value. Figure 2 shows all of the Status codes that are available, as seen in the Object Browser.

Here's some code that will re-sync a recordset with a database and loop through the recordset after the re-sync to display the OrderID and the Status code of each record:

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strLstRowSource As String

On Error GoTo Proc_Err

Set conn = New ADODB.Connection
'Open the connection to the database
With conn
    .Provider = "Microsoft.Jet.OLEDB.3.51"
    .ConnectionString = "data source=" _
       & gstrPath & "\saccess.mdb"
    .Mode = adModeReadWrite
    .Open
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open gstrPath & "\mailme.dat", , adOpenKeyset, _
  adLockBatchOptimistic, adCmdFile

rst.ActiveConnection = conn
rst.UpdateBatch adAffectAll

rst.Filter = adFilterAffectedRecords
Do Until rst.EOF
    strLstRowSource = strLstRowSource & _
       rst!OrderID & ": " & rst.Status & ";"
    rst.MoveNext
Loop

Me.lstResults.RowSource = strLstRowSource

As you can see, the ability to save a recordset to disk and then work with it as a recordset in your code is a very powerful technique. Like the other features of ADO, persistent recordsets are available to you today using Access 97. Access 2000 will give you even more power with the new Recordset property, and ADO 2.1 will give you more flexibility by allowing you to save a recordset to XML format. So get out and start saving those recordsets to disk!

Stephen Forte runs a New York City consulting firm, The Aurora Development Group (http://www.auroradev.com). He's also co-author of Access 97 Unleashed, Second Edition (SAMS Publishing, 1997) and is writing books on Jet 4.0 and Access 2000. He serves as the president of the NYC Access & VB Users Group (http://www.nycaccessvb.com). stephenforte@hotmail.com.

Sidebar: Foundation Concepts
ADO is Microsoft's long-term replacement for DAO. Like DAO, ADO provides an object-oriented way to retrieve data, organized around the Recordset object. While offering a simpler object model compared to DAO, ADO also offers some features that DAO doesn't have.

Sidebar: Getting ADO
If you don't have ADO installed on your machine, you'll have to download it from the Microsoft Web site at http://www.microsoft.com/data. In order to use ADO in your Access 97 or 95 applications, you'll have to set a reference to ADO by opening up a module and selecting Tools | References from the main menu. Once you have the References dialog box open, you'll need to set a reference to "Microsoft ActiveX Data Object 2.0," and you're ready to go. You must have ADO 2.0 installed to use persistent recordsets (you Access 2000 users can skip this step).