Access Hacks

Hack #65. 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" [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 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 constant—acAppendData, 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 ...