Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Importing and Exporting XML Data Using Microsoft Access

Although the Office Space column has been in business for several months now there are at least two topics we haven’t even touched on: Microsoft Access and XML. There’s no real reason for that; we just haven’t gotten around to them yet. And then we had a brainstorm when it came time to write this week’s column: let’s kill two birds with one stone.

OK, so then after all the animal rights activists finally stopped picketing our offices we had a better idea: let’s not kill any birds. Instead, let’s write a column that deals with both Access and XML.

And so we did. Today we’re going to talk about importing XML data into and exporting XML data out of Microsoft Access. Of course, that immediately leads to some questions: after all, if you’ve decided to use Access, why would you care about XML data? Conversely, if you’ve decided to use XML, then why bother with Access? Wouldn’t it be better to pick one format and stick with it?

To be honest, we don’t have the definitive answer to those questions. We do know, however, that we live in an XML world: many applications – especially Web-based applications – rely on XML data. In that respect, XML is hard to ignore.

On the other hand, Access has some really nice features for working with and analyzing data; the graphical query builder alone makes it a worthwhile application. Thus a quandary: XML is everywhere, but there aren’t a lot of good tools for analyzing data stored in XML files. (That’s changing, but for now ….) Access, by contrast, has excellent tools for analyzing data, but XML is becoming the de facto standard for data storage. What’s a poor script writer to do?

Well, here’s one suggestion: why not use XML and Access? Do you have data in an XML file that you’d like to work with? Then import that data into an Access database. Have data in an Access database that someone needs in XML format? Then just export that data to an XML file. This is better than having your cake and eating it, too; it’s like having your cake and someone else’s cake, and then eating both. Plus, you get an XML file out of the deal as well!

Let’s start off by showing you how to import an existing XML file into an Access database:

Const acAppendData = 2

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Test.mdb"

objAccess.ImportXML "c:\scripts\test.xml", acAppendData

In this script, we’re assuming the XML file has an outer element tag (say, Inventory) that references a table in the database C:\Scripts\Test.mdb. (In other words, there’s a table in the database named Inventory.) That means your XML file might start off looking something like this:

<?xml version="1.0" encoding="UTF-8"?>
    <Inventory>
        <ComputerName>atl-fs-01</ComputerName>

Because we want to append the XML data to that table we start the script by defining a constant named acAppendData and setting the value to 2. We’ll use that constant later on to tell the script to import the data and append it to the existing table.

We knew you were going to ask that: yes, there are other options for importing data. For example, we could define a constant named acStructureOnly and set the value to 0. In that case, Access would examine the XML file and create a table mimicking the structure of that file. However, no data would be imported; we’d simply have a blank table in the database.

Alternatively, we could define a constant named acStructureAndData and set the value to 1. If we did that, Access will not only create a new table, it will import data into that table as well. This works quite nicely depending on the nature of the data. For example, Access will create a table and, by default, set the data type of each field to Text. That could be a problem if you have data that needs to be stored in a Memo or Date field. But that’s something you’ll have to determine on a file-by-file basis.

After defining the constant we create an instance of the Access.Application object, then call the OpenCurrentDatabase method in order to open the file C:\Scripts\Test.mdb:

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Test.mdb"

All we have to do now is call the ImportXML method and pass it two parameters: the path to the XML file we want to import (C:\Scripts\Test.xml) and the type of import (as indicated by the constant acAppendData):

objAccess.ImportXML "c:\scripts\test.xml", acAppendData

That’s it. Give the thing a few seconds (depending on the size of the XML file), and the data will be imported into Microsoft Access. From there you can do anything you want with it. Including – we might add – exporting it back out as XML.

Do you need an XML version of one of your Access tables? Hey, why not:

Const acExportTable = 0

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Test.mdb"

objAccess.ExportXML acExportTable,"Inventory","c:\scripts\test.xml"

Once again we start out by defining a constant – in this case, that’s a constant named acExportTable – and we set the value to 0. Why do we use this constant? That’s easy: because we want to export all the data stored in a table. Alternatively, we could choose to export data from a query, a report, a function, or some other Access element. In fact, you can use any of the constants (and their corresponding values) shown in the following table:

After defining the constant we create an instance of the Access.Application object, open the database, and then call the ExportXML method:

objAccess.ExportXML acExportTable,"Inventory","c:\scripts\test.xml"

In this sample script, we include three parameters in our method call:

• acExportTable. The constant that indicates we’re exporting data from a table.

• Inventory. The name of the table being exported.

• C:\Scripts\Test.xml. The path to the XML file we’re creating. By default, Access will overwrite this file should it already exist.

That’s all we have to do. There are some additional options available to us (for example, you can simultaneously export just the schema information to a separate file), but we won’t worry about that today. For more information, see the Microsoft Access VBA Language Reference on MSDN.

One thing we did want to mention (seeing as how we haven’t talked much about Access in this column) is the fact that Access operates slightly different from Word and Excel. You’ll notice, for example, that we didn’t set the Visible property to True. That was done primarily because our test XML files were so small that they were imported before Access could fully show up on screen. However, it’s easy enough to make Access visible and thus allow you to watch the fun; just add this line of code after you create the Access.Application object:

objAccess.Visible = True

You might also have noticed that we never call the Quit method. Does that mean we have invisible copies of Access running amok on our computer? No. Like we said, Access operates a bit different from Word and Excel. When you start Access from a script, the application runs, does whatever you ask it to do, and then automatically terminates at the same time your script terminates. In other words, there’s no need to call the Quit method; Access will automatically quit without any prompting. Something one of the Scripting Guys does every day at 4:00 PM sharp.

Well, OK, sometimes a little before 4:00 PM. But only during baseball season.

OK, and sometimes during the baseball off-season.

At any rate, there might be times when you want Access to open up, do something (like import data from an XML file), and then remain on screen. In that case, you’ll need to add a second line of code, one that sets the UserControl property to True:

objAccess.UserControl = True

That line of code “tricks” Access into thinking it was started by a user (e.g., from the Start menu) rather than from a script. In turn, that causes it to remain onscreen when the script finishes.

And no, script or no script, you’ll never be able to trick our Scripting Guy into staying past 4:00 PM.