Using ADO to exchange data as XML (Access 97/2000/2002)
As you may know, Access 2002 natively supports XML, but earlier versions do not. However, you technically can import data into older versions of Access as well. Although you may work with the Recordset object's Open method frequently, it isn't well known that the Open method in ADO 2.1 and above allows you to specify the name of an XML file to import.
The only catch is, without getting into the technical details, the structure of the XML files ADO can work with is less common than what you'll find in the majority of XML files you're likely to come across. While you can create custom procedures that import the more common XML files into older versions of Access, ADO's methods are the easiest to work with. So, if the application storing the original source data supports ADO, you're in luck because you can simply create the XML using the Recordset object's Save method.
To create a compatible XML file using ADO, first create a Recordset object based on the data you want to export. Then use the Save method, which uses the syntax:
recordset.Save Destination, PersistFormat
The Destination argument specifies the name of the XML file to create and the PersistFormat argument should be set to the intrinsic constant adPersistXML. For example, your code might resemble:
rstExport.Save "C:\SourceData.xml", adPersistXML
To open the persisted recordset in Access, ensure that you have a reference to the ADO 2.1 library (or higher). Then, create a new Recordset object and use the Open method in the form:
recordset.Open FileName, "PROVIDER=MSPersist;", , , adCmdFile
Simply replace the FileName argument with the name of the appropriate XML file, as in:
rstImport.Open "C:\SourceData.xml", "PROVIDER=MSPersist;", , , adCmdFile
Once opened, you can work with the recordset as you would any other, so you could create a new table based on the data or build a procedure to append the information to an existing table.