Exporting Entire Contents of an Access DB to Individual XML Files

Question

I have many tables that I needed to frequently export from an MDB file to XML, and I hated going through the built-in wizard for each and every table multiple times. So, I made a macro and thought I'd post it on here.

This is a community wiki post, so please add suggestions and other solutions!

This will export each table into its own XML file in a directory specified within the code.

To use, you'll have to fire up VB6 within the Access Environemnt (in Access2007 its under Database Tools). Create a module, paste this code in and you'll be good to go.

Option Compare Database
Public Sub ExportDatabase()
On Error Resume Next

Const acExportTable = 0

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "database.mdb"

    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    For Each obj In dbs.AllTables

            'theres about 5 system tables that also get exported with this code
            'they all start with msys. this excludes these tables from being exportd
            If InStr(1, obj.Name, "msys", vbTextCompare) = 0 Then
                Dim dir as String = "c:\foo\"

                'delete existing copies of file, to overwrite
                Kill (dir & obj.Name & ".xml")
                'actually overwrite

            objAccess.ExportXML acExportTable, obj.Name, dir & obj.Name & ".xml"
            End If
    Next obj

End Sub