Since at least ADO 2.0 it has been possible to persist an ADODB.Recordset object to a binary format called ADTG (Advanced Data Table Gram) using the .Save method. Starting in ADO 2.1 it became possible to also persist a recordset to XML. If the hits from Google are to be believed this is used primarily for retrieving blocks of data from a remote source and then disconnecting the recordset and allowing local editing of data without having to send packets back to the data source over the network. When all the edits are complete, the recordset reconnects to the data source, and calls .UpdateBatch to save the changes to the data source.

What I wanted to do was not so much use the XML file as a temporary cache, but rather as a long term storage and/or transfer medium that could seed a new empty database at some point potentially far removed from the time when the data was saved. A few tantalizing hints and remarks on the web suggested, this was entirely possible, as long as the new empty data source had the exact same structure as the original data source.

I couldn't get any of the suggested methods to work, though. I fiddled some over the weekend and fiddled a bit more today and finally have a sequence that accomplishes exactly what I wish to do.

The algorithm:

  1. Connect to the original data source and load the data into a Recordset.
  2. Save the Recordset to an XML file (using the adPersistXML parameter to the Recordset.Save method).
  3. Prior to loading the XML file into a new recordset, make the following adjustments to the XML contents:
    • For each <s:AttributeType /> tag add an rs:basecatalog attribute, if necessary.
    • For each <s:AttributeType /> tag, set the value of the rs:basecatalog attribute to the new data source (it may be initialized to the old data source).
    • In the rs:data section of the XML file place all the z:row elements inside an insert block by putting an <rs:insert> tag between the rs:data tag and the first z:row element and an </rs:insert> between the last z:row element and the </rs:data> tag.
  4. Open a Recordset object based on the altered XML file (be sure the CursorLocation is adUseClient and the LockType is adLockBatchOptimistic).
  5. Update the ActiveConnection for the Recordset to point to the new data source (.ActiveConnection Is Nothing after opening an XML file).
  6. Use the .UpdateBatch method to execute the insert commands stored in the XML file. NOTE: It may be necessary to SET IDENTITY_INSERT for the table and you may need to make sure that parent foreign key data already exists. It would also be a good idea to wrap the update inside a transaction that you could rollback on an error.