By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,341 Members | 1,394 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

'Persisting' a Recordset

ADezii
Expert 5K+
P: 8,669
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.
Expand|Select|Wrap|Line Numbers
  1. rst.Save Filename, Format
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:
Expand|Select|Wrap|Line Numbers
  1. Public Sub SaveRecordset()
  2. Dim rst As ADODB.Recordset
  3. Dim strFile As String
  4.  
  5. Set rst = New ADODB.Recordset
  6.  
  7. 'Open the recordset from the database
  8. rst.Open "tblCustomers", CurrentProject.Connection, _
  9.                adOpenStatic, adLockOptimistic
  10.  
  11. 'Construct a file name to use (ADTG or XML)
  12. strFile = CurrentProject.Path & "\Customers.adtg"      'OR
  13. 'strFile = CurrentProject.Path & "\Customers.xml"
  14.  
  15. 'Destroy any existing file. Necessary because the Save Method 
  16. will fail if the specified file already exists.
  17. On Error Resume Next
  18. Kill strFile
  19. Err.Clear
  20.  
  21. 'Now save the recordset to disk (ADTG Format)
  22. rst.Save strFile, adPersistADTG
  23.  
  24. 'Close the recordset in memory
  25. rst.Close
  26. End Sub
Expand|Select|Wrap|Line Numbers
  1. Public Sub RetrieveRecordset()
  2. Dim rst As ADODB.Recordset
  3. Dim strFile As String
  4.  
  5. Set rst = New ADODB.Recordset
  6.  
  7. 'Construct a file name to use
  8. strFile = CurrentProject.Path & "\Customers.adtg"      'OR
  9. 'strFile = CurrentProject.Path & "\Customers.xml"
  10.  
  11. 'Make sure the file exists
  12.   If Len(Dir(strFile)) > 0 Then
  13.      'Open the recordset from the file
  14.       rst.Open strFile, , adOpenStatic, adLockOptimistic
  15.      'Reconnect the recordset to the database
  16.       rst.ActiveConnection = CurrentProject.Connection
  17.      'Make a change and save it
  18.          rst.Fields("ContactTitle") = "Sales Rep"
  19.          rst.Update
  20.   End If
  21.   rst.Close
  22.     Set rst = Nothing
  23. End Sub
May 26 '07 #1
Share this Article
Share on Google+