HandsOn Creating an XML Document from ADO

1. In the Visual Basic Editor screen, choose Insert | Module to add a new standard module to the current VBA project.

2. In the module's Code window, enter the SaveRst_ToXMLwithADO procedure as shown below.

Sub SaveRst_ToXMLwithADO() Dim rst As ADODB.Recordset Dim conn As New ADODB.Connection

' change the path to point to the Northwind database on your computer Const strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Program Files\Microsoft Office\" _ & "Office11\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:\Learn_XML\Products_AttribCentric.xml" ' save the recordset as an XML file rst.Save "C:\Learn_XML\Products_AttribCentric.xml", adPersistXML

' cleanup Set rst = Nothing Set conn = Nothing End Sub

Taking Your VBA Programming Skills to the Web

The above procedure begins by establishing a connection to the sample Northwind database using the ADO Connection object. Next, it executes a 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 using the adPersistXML format. 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 you are going to create does not yet exist.

3. Position the insertion point anywhere within the code of the procedure and choose Run | Run Sub/UserForm.

4. Open the C:\Learn_XML\Products_AttribCentric.xml file created by the SaveRst_ToXMLwithADO procedure and examine its content.

The web browser displays the raw XML as shown in Figure 29-23. Notice that the content of this file looks different from other XML files you generated in this chapter. The reason for this is that XML that is persisted from ADO recordsets is created in attribute-centric XML. Microsoft Office Access supports only element-centric XML. Therefore, in order to import to Access an XML file created from ADO, you must first create and apply an XSLT transformation to the source document. The stylesheet you create should convert the attribute-centric XML to element-centric XML that Access can handle (see Hands-On 29-14).

'3Ï C:\Leorrt XML\Products_ AttribCentric.xmI - Microsoft Internet Explorer ||P|[X|

File Edt View Favorites Tools

-JJ -. CiMeam.^Ml^FYoduttsJSttribcerttric.xrrf

- <xml xmlns^uuidiBDCSESFO-SDAS-i Idi-A2A3-OOAAOOC14B0Z" xmlns:dt="uuid:czF4itJio-65B3-i idi-AZ9F-00AA0rjci4832" xmlnsirs^urnischemas-mlcrosoft-comirowset'' xmlns:z="#RowsetSchema'"> + <s ¡Schema id="RowsetSchema"> - <rs:data>

<z:row Product!D="l" ProductfcJame^'Chai" SupplierID="l" CategoryID="I" Quantity PerUnit=" 10 bones h20 bags" UnrtPrice="18" Unrts!nStocli="39" Units0n0rder="0" Reorden.evel="i[l" Diseontinued^'False" /> <21 row Product! D="2" ProductName="Chang" Supplier!D="l" CategoryIO="l" Quant(tyPerUmt="24 - 12 oz bottles" UnitPrice="l9" UnitslnStoci<="l7" Untts0n0rder="4n" ReorderLevel="2S" Discontinued="False" /> <z:row Product!D="3" ProductWame="Aniseed Syrup" SupplierlD="l" CategorylO="Z" Quantit/PerUnit="12 - 5SO ml bottles" UnitPrice="10" UnitslnStock="i3" Units0n0rder="70" ReorderLevel="Z5" Discontinued="False" /> <z;row Product!D="4" ProductfJarne="Chef Anton's Cajun Seasoning' 5upp|ierlD="Z" Category id ="2" QuantityPerUnit="48 - 6 oz Jars" UnitPrice="Z2" UnitsInStock="53" UnitsOrtOrder-pO" Reorderl_evel="0" Discontinued="False" /> <z:row Product!D="5" ProductName="Chef Anton's Gumbo MiK" SupplierlD='Z" CateaorylD="2" QuantityPerUnit="36 boKes" UnitPrice="Zl.35" Unit$InStock="0" Units0n0rder="0" ReorderLevel="0" Discontinued='True' (> <z:row ProductED—"6" ProductWame—"Grandma's Boysenberry Spread" SupplierID='3" CategoryID="2" QuaritityPerL)nit="l2 - a oz jars" UnitPrice="25" UnitsInStock="120" Unit50n0rder='0" ReorderLevel='25* Discontinued«"False" /> <z:row ProductED—"7" ProductJJame—"Uncle Bob's Organic Dried Pears' SupplicrID="3" Category ID-"7" Quantity PerUnit=" 12 - 1 lb pkgs," UnitPrice-"30" UnitsInStock-"15' UnitsOnOrder-"On ReorderLcvel-"10' Discontinued—"False" /> <z:row ProductID—"8" ProductName—"Northwoods Cranberry Sauce"

^ My Computer

Figure 29-23: Saving a recordset to an XML file with ADO produces an attribute-centric

XML file.

Part V

0 0

Post a comment