Saving an ADO Recordset as XML to Disk

To save an ADO recordset as XML to a disk file, use the Save method of the Recordset object with the adPersistXML constant. The procedure illustrated below establishes a connection to the sample Northwind database using the ADO Connection object. Next it executes an SQL SELECT statement against the database to retrieve all of the records from the Products table. Once the records are placed in a recordset, the Save method is called to store the recordset to a disk file. If the disk file already exists, the procedure deletes the existing file using the VBA Kill statement. The On Error Resume Next statement allows bypassing the Kill statement if the file that you are going to create does not yet exist.

The following exercise demonstrates how to create XML files from ADO recordsets.

1. Open a new workbook and activate the Visual Basic Editor window.

2. Insert a new module and type the SaveRst_ADO procedure, as shown below.

3. Save the spreadsheet file as XMLviaADO.xls.

4. Run the SaveRst_ADO procedure.

5. Open the C:\Products.xml file created by the SaveRst_ADO procedure and examine its content.

Sub SaveRst_ADO()

Dim rst As ADODB.Recordset Dim conn As New ADODB.Connection

Const strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Program Files\Microsoft Office\" _ & "Office\Samples\Northwind.mdb"

' open a connection to the database conn.Open strConn

' execute a select SQL statement against the database Set rst = conn.Execute("SELECT * FROM Products")

' delete the file if it exists On Error Resume Next Kill "C:\Products.xml"

' save the recordset as an XML file rst.Save "C:\Products.xml", adPersistXML End Sub

0 0

Post a comment