473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,322 developers and data experts.

'Persisting' a Recordset

ADezii
8,834 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
  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
0 11204

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

Similar topics

0
by: Mark Carter | last post by:
Are there any good ways of persisting/depersisting a Worksheet?
2
by: Chris S. | last post by:
Out of a somewhat academic interest, I've created a rudimentary module for persisting dynamically created objects and data structures in plain Python source code. Presently, it's a little under a...
2
by: Citoyen du Monde | last post by:
Trying to get some ideas on a simple javascript project (to teach myself the language). I want to develop a client-side vocabulary practice application that would allow users to enter their own...
4
by: Dave Veeneman | last post by:
When does serializing objects make more sense than persisting them to a database? I'm new to object serialization, and I'm trying to get a feel for when to use it. Here is an example: I'm...
1
by: lim | last post by:
What is the possible error that occurs when the Page_load event is not triggered during execution. In my page there's some basic server control. Is there any loops holes?
12
by: Dave | last post by:
I'm relatively new to ASP and .NET and having trouble getting my head round this. I want to read a datset from a database, and display information from one row at a time, using a Next and a...
2
by: xenophon | last post by:
I added a Hidden Form Field to a form in the code behind. The value is being set in JavaScript client-side, but it is not persisting to the server in the PostBack. I know the value is being set...
5
by: Dick | last post by:
I have a GridView bound to an ObjectDataSource. I have a Button that calls GridView.DataBind. I want the row that is selected before the DataBind to still be selected afterwards. This happens...
7
JustJim
by: JustJim | last post by:
Having just re-read Adezii's article on the above subject, I thought I'd have a play. I think there must be a "Reference" that I need to set to use ADODB.Recordset? Otherwise I get a "User-defined...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.