OLE DB Persistence Provider

Portable computing (for example, using laptops) has generated the need for applications that can run in both a connected and disconnected state. ADO has added support for doing this by giving the developer the ability to save a client cursor recordset to disk and reload it later.

There are several interesting scenarios in which to use this type of feature, including the following:

• Traveling salesman: When taking the application on the road, it is vital to supply the ability to make changes and add new records that can then be reconnected to the database later and be committed.
• Infrequently updated lookups: Often in an application, tables are used as lookups—for example, state tax tables. They are infrequently updated and are read-only. Instead of rereading this data from the server each time the application is started, the application can simply load the data from a locally persisted recordset.

In ADO, to save and load recordsets, use the Recordset.Save and Recordset.Open(,,,,adCmdFile) methods on the ADO Recordset object, as demonstrated in the following example:

Sub Form_Load()

   Dim conn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Set conn = New ADODB.Connection
   Set rst = New ADODB.Recordset

   conn.ConnectionString = _
   "Provider=SQLOLEDB;Data Source=sql70server;" _
      & "User ID=sa;Password='';Initial Catalog=pubs"
   conn.Open

   conn.Execute "create table testtable (dbkey int _
      primary key, field1 char(10))"
   conn.Execute "insert into testtable values (1, 'string1')"

   Set rst.ActiveConnection = conn
   rst.CursorLocation = adUseClient

   rst.Open "select * from testtable", conn, adOpenStatic, _
      adLockBatchOptimistic

   'Change the row on the client
   rst!field1 = "NewValue"

   'Save to a file--the .dat extension is an example; choose
   'your own extension. The changes will be saved in the file
   'as well as the original data.
   Kill "c:\temp\temptbl.dat"
   rst.Save "c:\temp\temptbl.dat", adPersistADTG
   Set rst = Nothing

   'Now reload the data from the file
   Set rst = New ADODB.Recordset
   rst.Open "c:\temp\temptbl.dat", , adOpenStatic, _
      adLockBatchOptimistic, adCmdFile

   Debug.Print "After Loading the file from disk"
   Debug.Print "   Current Edited Value: " & rst!field1.Value
   Debug.Print "   Value Before Editing: " & rst!field1.OriginalValue

   'Note that you can reconnect to a connection and 
   'submit the changes to the data source
   Set rst.ActiveConnection = conn
   rst.UpdateBatch

   rst.Close
   conn.Close

End Sub