Previous Page
Next Page

Hack 87. Provide Complete XML Control to Any Version of Access

Use the MSXML Parser to make XML native to your applications.

Support for XML has been growing through successive Access releases, but complete XML control still isn't available. For instance, Access 2003 supports importing and exporting XML, but even so, the level of functionality is limited. For example, you can't import attributes (a type of XML data).

Referencing an external XML parser not only improves the XML support, but also provides the same level of support to any version of Access. This is possible because the parser is an independent piece of technology. As long as you can reference it, you can use it to its fullest.

8.17.1. Referencing the Parser

In the Access VB editor, use the Tools References menu to open the References dialog box, shown in Figure 8-28.

Scroll through the list, and find Microsoft XML. The reference will include a version number; any version will do. If you are curious how the parser versions differ, visit Microsoft's web site (http://www.microsoft.com).

Figure 8-28. Adding a reference to the XML parser


If you don't find the Microsoft XML reference on your computer, download the MSXML parser from Microsoft (http://www.microsoft.com/xml).


With the reference set, you can work with XML in many sophisticated ways.This hack isn't the place to learn how to use the parser (see the end of the hack for some resources). Instead, we'll preview using the parser to load XML data and insert it into an Access table. Along the way, we'll accomplish a couple of tricks: filtering XML data and loading attributes.

An XML file filled with employee information has been prepared, as shown in Figure 8-29.

8.17.2. The Code

In an Access code module, the following code has been entered. This code uses objects available from the parser reference:

    Sub read_xml()
      On Error GoTo err_end
      Dim conn As New ADODB.Connection
	  Set conn = CurrentProject.Connection
	  Dim xmlobj As DOMDocument
	  Dim xml_list As IXMLDOMNodeList
	  Dim xml_node As IXMLDOMNode
	  Set xmlobj = New DOMDocument
	  xmlobj.async = False
	  xmlobj.Load "C:\Employees.xml"
     Set xml_list = xmlobj.selectNodes _
       ("Employees/Department/Employee")
     For Each xml_node In xml_list
        ssql = "Insert Into tblEmployees Values (" & _
          xml_node.childNodes(0).Text & ", '" & _
		  xml_node.childNodes(1).Text & "', '" & _
		  xml_node.parentNode.Attributes(0).Text & "')"
     conn.Execute ssql
	 Next
	 MsgBox "done"
   err_end:
     MsgBox Err.Description
   End Sub

Figure 8-29. The Employees.xml file


The XML file is loaded into the xmlobj object variable:

        xmlobj.Load "C:\Employees.xml"

Typical XML objects are nodes and node lists. A list is a collection of nodes. The actual nodes are the employee elements, which are children of the department nodes:

       Set xml_list = xmlobj.selectNodes _ 
           ("Employees/Department/Employee")

Employee nodes have two children: EmployeeID and Name. These child elements and the parent department element are the basis from which a SQL Insert statement is created.

 ssql = "Insert Into tblEmployees Values (" & _
             xml_node.childNodes(0).Text & ", '" & _
			 xml_node.childNodes(1).Text & "', '" & _
			 xml_node.parentNode.Attributes(0).Text & "')"

After the routine runs, the tblEmployees table is populated with the XML data, as shown in Figure 8-30.

Figure 8-30. The XML data now in Access


So, in just a short routine, we've accomplished two things that are typically taken for granted as being impossible. One is that now, only Access 2003 can work with XML in a robust way, and the other is that attributes can't be imported. The routine in this hack will work with any version of Access that references the parser and clearly has no problem putting an attribute's value into an Access table.

8.17.3. See Also

  • "Use Access as an XML Database"[Hack #95]

  • XML Hacks(O'Reilly)

  • Office 2003 XML(O'Reilly)

    Previous Page
    Next Page