467,907 Members | 1,122 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

'Persisting' a Recordset

Expert 8TB
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
  5. Set rst = New ADODB.Recordset
  7. 'Open the recordset from the database
  8. rst.Open "tblCustomers", CurrentProject.Connection, _
  9.                adOpenStatic, adLockOptimistic
  11. 'Construct a file name to use (ADTG or XML)
  12. strFile = CurrentProject.Path & "\Customers.adtg"      'OR
  13. 'strFile = CurrentProject.Path & "\Customers.xml"
  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
  21. 'Now save the recordset to disk (ADTG Format)
  22. rst.Save strFile, adPersistADTG
  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
  5. Set rst = New ADODB.Recordset
  7. 'Construct a file name to use
  8. strFile = CurrentProject.Path & "\Customers.adtg"      'OR
  9. 'strFile = CurrentProject.Path & "\Customers.xml"
  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
  • viewed: 9862

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Mark Carter | last post: by
2 posts views Thread by Chris S. | last post: by
2 posts views Thread by Citoyen du Monde | last post: by
4 posts views Thread by Dave Veeneman | last post: by
1 post views Thread by lim | last post: by
2 posts views Thread by xenophon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.