ADO - ADO Recordset Persistence Using XML

Introduction

ADO Recordset Persistence is a feature that allows the contents of a Recordset to be saved to a file and later restored without reconnecting to the original database. One of the most commonly used formats for persistence is XML (Extensible Markup Language). XML is a platform-independent, human-readable format that stores both the data and the schema (structure) of a Recordset.

By saving a Recordset as an XML file, developers can transport data between applications, create backups, cache data locally, or work with database information while offline. Later, the XML file can be loaded back into a Recordset, allowing the application to continue working with the data as if it had just been retrieved from the database.

What is Recordset Persistence?

Recordset Persistence is the process of storing a Recordset outside the application's memory so that it can be reused later.

Normally, when an application closes, all Recordset data stored in memory is lost. Persistence allows developers to save that data into a permanent storage medium such as an XML file or an ADTG (Advanced Data TableGram) file.

The process includes:

  1. Retrieve data from the database.

  2. Save the Recordset to an XML file.

  3. Close the application.

  4. Open the application later.

  5. Load the XML file back into a Recordset.

This eliminates the need to retrieve the same data repeatedly from the database.

What is XML?

XML (Extensible Markup Language) is a standard format used to store and exchange structured information.

Unlike HTML, which focuses on displaying information, XML focuses on describing and storing data.

Example:

<Employee>
    <EmployeeID>101</EmployeeID>
    <EmployeeName>Rahul</EmployeeName>
    <Department>Sales</Department>
    <Salary>55000</Salary>
</Employee>

Each value is enclosed within descriptive tags, making the data easy to understand and exchange between different systems.

Why Use XML for Recordset Persistence?

Saving Recordsets as XML provides several benefits:

  • Data can be stored permanently.

  • Data can be transferred between different applications.

  • XML files are platform-independent.

  • Data can be edited without a database connection.

  • XML files are easy to inspect and debug.

  • XML supports both data and schema information.

How Recordset Persistence Works

The overall workflow is straightforward.

Step 1: Connect to Database

The application establishes a database connection.

Application
      ↓
Database

Step 2: Retrieve Records

The Recordset fetches the required data.

Database
      ↓
Recordset

Step 3: Save Recordset as XML

The Recordset is written to an XML file.

Recordset
      ↓
Employees.xml

The database connection can now be closed.

Step 4: Close the Application

Even though the application closes, the XML file still contains all the data.

Step 5: Load XML Later

When the application starts again, it reads the XML file.

Employees.xml
      ↓
Recordset

The data is available without querying the database.

Saving a Recordset to XML

ADO provides the Save method for persisting Recordsets.

Example:

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.Open ConnectionString

rs.Open "SELECT * FROM Employees", con

rs.Save "Employees.xml", adPersistXML

con.Close

Explanation

  • Save writes the Recordset to a file.

  • "Employees.xml" is the file name.

  • adPersistXML specifies that the data should be stored in XML format.

Loading an XML File into a Recordset

The Open method can read an XML file directly into a Recordset.

Example:

Dim rs As New ADODB.Recordset

rs.Open "Employees.xml"

The Recordset now contains the same data that was previously saved.

No database connection is required.

XML File Contents

A persisted Recordset contains two main sections:

Schema

The schema describes the structure of the data.

It includes:

  • Table name

  • Column names

  • Data types

  • Field sizes

  • Constraints

Example:

<Field Name="EmployeeID" Type="Integer"/>
<Field Name="EmployeeName" Type="String"/>
<Field Name="Salary" Type="Currency"/>

The schema ensures that the Recordset can be reconstructed correctly.

Data

The data section stores the actual records.

Example:

<Employee>
    <EmployeeID>101</EmployeeID>
    <EmployeeName>Rahul</EmployeeName>
    <Salary>55000</Salary>
</Employee>

<Employee>
    <EmployeeID>102</EmployeeID>
    <EmployeeName>Anita</EmployeeName>
    <Salary>60000</Salary>
</Employee>

XML Persistence Modes

ADO supports two persistence formats.

XML Format

adPersistXML

Characteristics:

  • Human-readable.

  • Platform-independent.

  • Easy to transfer.

  • Larger file size.

  • Suitable for web services and data exchange.

ADTG Format

adPersistADTG

Characteristics:

  • Binary format.

  • Faster loading.

  • Smaller file size.

  • Not human-readable.

  • Mainly used within ADO-based applications.

Working Offline with XML

A common use case involves offline editing.

Example workflow:

  1. Download customer records.

  2. Save them as Customers.xml.

  3. Disconnect from the database.

  4. Modify customer details locally.

  5. Load the updated XML.

  6. Synchronize changes with the database later.

This approach is especially useful for mobile and remote applications.

Advantages of XML Persistence

Data Backup

The XML file acts as a backup of the Recordset.

If the application crashes, the saved data remains available.

Reduced Database Traffic

Frequently used data can be stored locally.

Instead of repeatedly querying the database, the application reads from the XML file.

Easy Data Sharing

XML files can be:

  • Emailed

  • Uploaded

  • Copied to another computer

  • Shared across different operating systems

Platform Independence

XML is supported by almost every programming language.

Examples include:

  • VB6

  • VB.NET

  • C#

  • Java

  • Python

  • PHP

Human Readability

Unlike binary files, XML files can be opened using:

  • Notepad

  • Visual Studio Code

  • Notepad++

  • XML editors

Developers can inspect the contents directly.

Real-World Applications

Inventory Management

An inventory application downloads product details and saves them as an XML file. Warehouse staff can continue working even when the database is unavailable. The updated data is synchronized later.

Sales Applications

Sales representatives download customer information before visiting clients. The data is stored in XML format and updated during meetings. Once internet access is available, the information is uploaded to the central database.

Educational Institutions

Student records can be exported as XML for reporting, archival, or transferring data between departments and applications.

Banking

Banks generate XML files containing customer transactions, account summaries, or reports that can be securely exchanged between systems.

E-Commerce

Online stores cache product catalogs as XML to reduce database queries and improve page loading speed. Product information can be refreshed periodically.

Limitations

  • XML files are larger than binary files.

  • Reading large XML files can be slower.

  • XML files are plain text and should be protected if they contain sensitive information.

  • Manual editing of XML files may introduce errors if the structure is altered incorrectly.

  • XML persistence captures a snapshot of the data and does not automatically reflect subsequent changes in the database.

Best Practices

  • Use meaningful file names such as Employees.xml or Customers.xml.

  • Store XML files in secure locations with appropriate access permissions.

  • Validate XML before loading it into a Recordset to ensure it is well-formed.

  • Compress large XML files when transferring them over a network.

  • Refresh persisted data periodically to keep it synchronized with the database.

  • Use XML persistence for caching, backup, and data exchange rather than as a replacement for a live database.

  • Handle exceptions when saving or loading files to prevent data loss.

Conclusion

ADO Recordset Persistence Using XML enables developers to save Recordsets as XML files and restore them later without reconnecting to the database. By preserving both the data and its structure, XML persistence supports offline processing, data backup, caching, and interoperability between different systems. Because XML is platform-independent, readable, and widely supported, it remains a practical choice for exchanging and storing Recordset data in enterprise, desktop, and distributed applications.