July 8, 2011, 7:20 p.m.
posted by osi
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.
"Import Varied XML Data into Access" [Post #63]
"Export XML Data Sanely" [Post #64]
Simon St. Laurent