'Persisting' a Recordset

[ADezii]

Most Access Users realize that Recordsets, being virtual representations of a Query, Table, or SQL Statement, exist only in our PC's memory. They, and the data they contain, literally exist at one specific moment in time - then gone the next. Few of us realize, however, that they can be saved to disk and later retrieved to will. The technical jargon for this is called 'Persisting a Recordset' and I'll show you how it can be done. ADO has this unique ability to persist a Recordset to a file on disk. You can also later reopen it, edit it, reconnect it to the original data source, and save changes. To persist a Recordset to disk for later use, you call its Save Method.
[CODE=vb]rst.Save Filename, Format[/CODE]

The Filename Parameter is the full path and filename to the file you wish to use to hold the contents of the Recordset

The Format Parameter can be 1 of 2 intrinsic constants:

1. adPersistADTG (default) - Saves the Recordset in the Microsoft proprietary Advanced Data Tablegram format.
2. adPersistXML - Saves the Recordset as XML. If you save the Recordset in XML format, you can easily use the saved XML file as a Data Source for another application or control that understands XML. XML is an emerging Internet Standard for transferring data.

NOTE: ADTG files are smaller than XML files, so unless you need the ability to distribute data in XML format, stick to ADTG.

Enough on the Overview - 2 well commented Sub-Routine Procedures will demonstrate how to Save (Persist) a Recordset to a file on disk, and then retrieve it, make a change, and save it back to disk:
[CODE=vb]Public Sub SaveRecordset()
Dim rst As ADODB.Recordset
Dim strFile As String

Set rst = New ADODB.Recordset

'Open the recordset from the database
rst.Open "tblCustome rs", CurrentProject. Connection, _
adOpenStatic, adLockOptimisti c

'Construct a file name to use (ADTG or XML)
strFile = CurrentProject. Path & "\Customers.adt g" 'OR
'strFile = CurrentProject. Path & "\Customers.xml "

'Destroy any existing file. Necessary because the Save Method
will fail if the specified file already exists.
On Error Resume Next
Kill strFile
Err.Clear

'Now save the recordset to disk (ADTG Format)
rst.Save strFile, adPersistADTG

'Close the recordset in memory
rst.Close
End Sub[/CODE]

[CODE=vb]Public Sub RetrieveRecords et()
Dim rst As ADODB.Recordset
Dim strFile As String

Set rst = New ADODB.Recordset

'Construct a file name to use
strFile = CurrentProject. Path & "\Customers.adt g" 'OR
'strFile = CurrentProject. Path & "\Customers.xml "

'Make sure the file exists
If Len(Dir(strFile )) > 0 Then
'Open the recordset from the file
rst.Open strFile, , adOpenStatic, adLockOptimisti c
'Reconnect the recordset to the database
rst.ActiveConne ction = CurrentProject. Connection
'Make a change and save it
rst.Fields("Con tactTitle") = "Sales Rep"
rst.Update
End If
rst.Close
Set rst = Nothing
End Sub[/CODE]