Break Through VBA's Transformation Barrier





Break Through VBA's Transformation Barrier

Strange but true: Access supports XSLT transformation on input when you use the GUI, but not when you automate the process with VBA. The same goes for output. Fortunately, you can work around this by calling the MSXML parser directly.

The examples in "Import Varied XML Data into Access" [Post #63] give some ideas for how to get information into your Access tables even if the data arrives in a format other than the simple element-only form Access expects. However, if such data arrives on a regular basis, you probably don't want to be clicking through forms every time you need to import more data.

Unfortunately, converting these steps to an automated VBA process is a challenge because the ImportXML function doesn't provide a place for any transformations. As it turns out, neither does the ExportXML function.

The syntax of the ImportXML function looks like this:

	Application.ImportXML (DataSource, ImportOptions)

It takes only a data source, the name and path of the XML file to import, and an options constantacAppendData, acStructureAndData (the default), or acStructureOnly. There is no option for an XSLT transformation. Similarly, the ExportXML function looks like this:

	Application.ExportXML (ObjectType, DataSource, DataTarget, SchemaTarget, PresentationTarget, ImageTarget, Encoding, OtherFlags)

The PresentationTarget argument does have something to do with transformation, but it's only for output. It identifies where Access will put a stylesheet for turning the XML into HTML based on its own expectations, not yours.

You can get around these problems in two ways. First, you can write some custom code. The import version will instantiate an XML parser (probably MSXML), read the content from the document however you deem appropriate, and then use ADO, DAO, or SQL Update queries to put the data in the database. The export version will read data from the database and write it to an MSXML DOM tree as necessary.

This might be appropriate if you have complicated cases, but it's a lot of code for what's most likely a simple problem, and you can't test how it works (or reuse that work) outside of Access.

A more likely approach, if you can stand working with XSLT, is to add a step before the import or after the export that performs an extra transformation. Because Access doesn't let you pass objects to the import or get objects from the export, you need to work with temporary files to produce the results you want. Conveniently, you can use the same function for both cases.

A simple version of this function looks like this:

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

Dim source As New MSXML2.DOMDocument30
Dim stylesheet As New MSXML2.DOMDocument30
Dim result As New MSXML2.DOMDocument30

' Load data.
source.async = False
source.Load sourceFile

' Load style sheet.
stylesheet.async = False
stylesheet.Load stylesheetFile

If (source.parseError.errorCode <> 0) Then
   MsgBox ("Error loading source document: " & source.parseError.reason)
   Else
If (stylesheet.parseError.errorCode <> 0) Then
      MsgBox ("Error loading stylesheet document: " & _
         stylesheet.parseError.reason)
   Else
      ' Do the transform.
      source.transformNodeToObject stylesheet, result
      result.Save resultFile
End If
End If

End Sub	

The transform function takes three arguments: the path of a source file holding the original XML, the path of a stylesheet file holding the XSLT that will be used to transform it, and the path to which the resulting document should be saved. Typically, you'll want to call transform before using Access's native ImportXML function or after you've used the ExportXML function.

For example, you might import XML files to a table directly with this call:

	Application.ImportXML "http://simonstl.com/ora/updateBook.xml", acAppendData

But if that XML file stored the data as attributes, and you wanted to apply a transformation to that data before you imported it into Access, you might do this instead:

Transform "http://simonstl.com/ora/updateBook.xml", _
    "C:\xslt\attsToElem.xsl", _
    "C:\temp\tempImport.xml"
Application.ImportXML "C:\temp\tempImport.xml", acAppendData

Similarly, you can apply a transformation after you exported data, turning it into HTML:

Application.ExportXML acExportTable, "books", "C:\temp\tempExport.xml"
Transform "C:\temp\tempExport.xml", _
    "C:\xslt\booksToHTML.xsl", _
    "C:\export\exportedBooks.html"

Writing XML documents out to files and then reparsing them isn't efficient by any means, but it patches a gap left by the Access API for importing and exporting XML. Unless you're dealing with huge volumes of data, or doing this processing constantly, users of your databases aren't likely to notice a big difference. Import and export are usually pretty slow operations anyway.

See Also

  • "Import Varied XML Data into Access" [Post #63]

  • "Export XML Data Sanely" [Post #64]

Simon St. Laurent


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows