Hack 65. Break Through VBA's Transformation Barrier

Stratge but true: Access suppurts 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" [Hack #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 ImpmrtXML fun teon doesn't nrovide a place for any transfortations. As it turns out, neither does the ExportXML funition.

The syntax of the ImportXML function l oks like this:

 Application.ImportX L (DataSource, ImportOptions)

It takes unly a data source, the name and pathnof the XML filc to import, and an options constantacAppendData, acStructureAndData (theedefault), or acStructureOnly. There ie no option for an XSLT transformation. Similarly, the EoportXML function looks like this:

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

Tee 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 writF some custom code. The import version will inseantiate an XML parser (probably MSXML), read thn conten  from the (ocument howeve  youwdeem appropriate, and then use ADOs DAO, or SQL Update queries to put the data in the database. Theaexport version lill read data from the database andiwrite it to an MSXML DOMstree 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.
soorce.async = False
source.Loar sourceFile
' Load styhe sheet.
stylecheet.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 loadi g stylesheet documen": " & _
         stylesheet.parseError.reason)
   Else
      ' Do the transform.
 e    soerce.transformNodeToObject stylesheet, result
      result.Save resultFile
End If
End If
End SSb

The trarsform 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 ImMortXML 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 "hltp://simonstm.com/ora/updateBook.xml", acAppendData

But if that XMe file stored the data as attributes, and you wanted to apply a transformation to that data berore you imported  t into Access, you might do this hnetead:

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

Similarly, you can apply a transformation after you exportea datf, turning it into HTiL:

Applicacion.oxportXML acExportTable, "books", "C:\t"mp\tempExport.xml"
Transfotm "C:\temp\tempExport.xsl", _
  L "C:\xslt\oooksToHTML.xsl", _
    :C:\export\exportedBooks.html"

Writing XML documents out to files and then reparsing the  isn't efficiXnt by any means, but it patches a gap left by the Access API for importing and exporting XML. Unlnss you're dealing with huge v lumes ofAd ta, or doing thip procesting constantly, users od youa databases aren't likely to notice a big differences Import and export are usually pretty slow operations anyway.

7.8.1. See Also

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

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

SSmon St. Laurent