Home > Articles > Web Services > XML

  • Print
  • + Share This
Like this article? We recommend

Like this article? We recommend

Exporting Information from an Access Database to a File

It's remarkably simple to export the information from an Access table to an XML file. With the table selected (see Figure 3), simply choose File, Export from the menu. Figure 4 shows the resulting dialog box. Specify the target location, select XML in the Save As Type drop-down list, and enter a filename (defaults to the name of the table you're exporting).

Figure 3Figure 3 For this example, we want to export the Products table.

Figure 4Figure 4 Exporting the Products table as XML.

Figure 5 shows the next dialog box. You can choose to export just the data from the table (the resulting file will get an XML extension), the schema of the data (creating an XSD file) and/or the presentation of the data (saved in an XSL file). Access assumes that you want both the schema and the data but not the presentation.

Figure 5Figure 5 Options for exporting.

If you click the More Options button, you get the dialog box shown in Figure 6. On the Data tab, you can specify whether you want to export all records in the table, or just the first record. The encoding method for the data defaults to UTF-8, or you can choose UTF-16.

Figure 6Figure 6 Data export details.

Figure 7 shows the Schema tab. Not much detail is associated with the export of the schema, and your choices are limited to the export of the referential integrity information, the primary key-foreign key information, and the desired location for the resulting information—embedded within the XML document or stored as a separate document.

Figure 7Figure 7 Schema export details.

Figure 8 shows the Presentation tab options. You can create an HTML document with which to view the associated information in the XML document (Figure 9 shows the result from the Crafty database), or create an Active Server Pages (ASP) file. The export location of the XSL file will also be the location of the resulting HTML file.

NOTE

If you're exporting an Access report as XML, export the graphics information with the options in the Include Report Images section of the dialog box (inactive in Figure 8).

Figure 8Figure 8 Presentation export details.

Figure 9Figure 9 HTML presentation of XML information. Because this presentation isn't a dynamic page, of course, the numbers are unreliable.

Figure 10 shows the XML version generated as a result of the export.

Figure 10Figure 10 The XML file generated from the Access export.

TIP

The XML file generated from an Access database can be extended to include more than one table and to take into account the relationships between tables that are exported.

Here's the actual XML:

<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2004-02-28T05:24:07">
- <Products>
 <ProductID>1</ProductID>
 <ProductName>seaside cottage</ProductName>
 <ProductDescription>painting</ProductDescription>
 <Catagory>artwork</Catagory>
 <UnitsInStock>3</UnitsInStock>
 <UnitPrice>75</UnitPrice>
 </Products>
- <Products>
 <ProductID>2</ProductID>
 <ProductName>victorian bonnets</ProductName>
 <ProductDescription>cotton vicrotian style bonnets</ProductDescription>
 <Catagory>baby clothes</Catagory>
 <UnitsInStock>15</UnitsInStock>
 <UnitPrice>20</UnitPrice>
 </Products>
- <Products>
 <ProductID>3</ProductID>
 <ProductName>custom rattle</ProductName>
 <ProductDescription>stuffed embroyered rattles</ProductDescription>
 <Catagory>baby items</Catagory>
 <UnitsInStock>32</UnitsInStock>
 <UnitPrice>9</UnitPrice>
 </Products>
- <Products>
 <ProductID>4</ProductID>
 <ProductName>snowflake stars</ProductName>
 <ProductDescription>stars with snowflake pattern</ProductDescription>
 <Catagory>christmas</Catagory>
 <UnitsInStock>87</UnitsInStock>
 <UnitPrice>2</UnitPrice>
 </Products>
- <Products>
 <ProductID>5</ProductID>
 <ProductName>flower pot candles</ProductName>
 <ProductDescription>candles in a flowerpot 3 inch</ProductDescription>
 <Catagory>candles</Catagory>
 <UnitsInStock>54</UnitsInStock>
 <UnitPrice>9.5</UnitPrice>
 </Products>
- <Products>
 <ProductID>14</ProductID>
 <ProductName>flower pot candle</ProductName>
 <ProductDescription>candle in a flowerpot mini</ProductDescription>
 <Catagory>candles</Catagory>
 <UnitsInStock>46</UnitsInStock>
 <UnitPrice>2.75</UnitPrice>
 </Products>
- <Products>
 <ProductID>15</ProductID>
 <ProductName>flower pot candle</ProductName>
 <ProductDescription>patio sized candle in flower pot</ProductDescription>
 <Catagory>candles</Catagory>
 <UnitsInStock>2</UnitsInStock>
 <UnitPrice>35</UnitPrice>
 </Products>
- <Products>
 <ProductID>16</ProductID>
 <ProductName>custom bulbs</ProductName>
 <ProductDescription>hand painted customized glass bulbs</ProductDescription>
 <Catagory>christmas</Catagory>
 <UnitsInStock>75</UnitsInStock>
 <UnitPrice>12</UnitPrice>
 </Products>
 </dataroot>

And here's the presentation code that produces a more attractive result:

<HTML xmlns:signature="urn:schemas-microsoft-com:office:access">
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=UTF-8"/>
</HEAD>
<BODY ONLOAD="ApplyTransform()">
</BODY>
<SCRIPT LANGUAGE="VBScript">
   Option Explicit

   Function ApplyTransform()
     Dim objData, objStyle

     Set objData = CreateDOM
     LoadDOM objData, "Products.xml"

     Set objStyle = CreateDOM
     LoadDOM objStyle, "Products.xsl"

     Document.Open "text/html","replace"
     Document.Write objData.TransformNode(objStyle)
   End Function

   Function CreateDOM()
     On Error Resume Next
     Dim tmpDOM

     Set tmpDOM = Nothing
     Set tmpDOM = CreateObject("MSXML2.DOMDocument.5.0")
     If tmpDOM Is Nothing Then
        Set tmpDOM = CreateObject("MSXML2.DOMDocument.4.0")
     End If
     If tmpDOM Is Nothing Then
        Set tmpDOM = CreateObject("MSXML.DOMDocument")
     End If

     Set CreateDOM = tmpDOM
   End Function

   Function LoadDOM(objDOM, strXMLFile)
     objDOM.Async = False
     objDOM.Load strXMLFile
     If (objDOM.ParseError.ErrorCode <> 0) Then
        MsgBox objDOM.ParseError.Reason
     End If
   End Function

</SCRIPT>
</HTML>

Now it's just a matter of uploading the information and files to the hosted web site, and arranging access to the information by buyers or end users.

  • + Share This
  • 🔖 Save To Your Account