Using Access VBA to write table data to an XML file

I want to click a button in Access and output a Table (or data from a Query) to an XML file. I have tried successfully doing this via the GUI but I need to do it in VBA. This would need to work in 2003 as well as 2007. I will also need to EITHER specify san existing XML file and location OR create a new XML file in a specific location. A brief code example would be really helpful. Thanks.
DataTrainAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
JezWaltersConnect With a Mentor Commented:
This should give you the general idea of where I'm coming from:
Option Explicit
Option Compare Database

Public Sub ExportTable()

    Const TABLE_NAME As String = "YourTable"
    Const EXPORT_SPEC As String = "ExportSpecification"

    Dim iexExportSpec As ImportExportSpecification
    Dim strExportSpec As String

    Set iexExportSpec = Nothing
    On Error Resume Next  ' Ignore missing spec
    Set iexExportSpec = CurrentProject.ImportExportSpecifications(EXPORT_SPEC)
    On Error GoTo 0
    If Not iexExportSpec Is Nothing Then
        iexExportSpec.Delete
    End If

    strExportSpec = "<?xml version=""1.0"" encoding=""utf-8"" ?>" & _
                    "<ImportExportSpecification Path = ""C:\Temp\" & TABLE_NAME & ".xml"" xmlns=""urn:www.microsoft.com/office/access/imexspec"">" & _
                    "<ExportXML AccessObject=""" & TABLE_NAME & """ ObjectType=""Table"" Encoding=""UTF8"" FilterCriteria="""" ApplySort=""false"" LiveReportSource=""false"" SchemaTarget=""" & TABLE_NAME & ".xsd"" EmbedSchema=""false"" ExcludePrimaryKeyAndIndexes=""false"" IncludeExtendedProperties=""true"" >" & _
                    "</ExportXML>" & _
                    "</ImportExportSpecification>"
    Set iexExportSpec = CurrentProject.ImportExportSpecifications.Add("ExportSpecification", strExportSpec)
    iexExportSpec.Execute
    iexExportSpec.Delete

End Sub

Open in new window

0
 
JezWaltersCommented:
You should be able to achieve what you're after by creating an ImportExportSpecification on the fly, and then executing it.

If you supply a sample database, I'd be happy to show you how to do this.
0
 
DataTrainAuthor Commented:
That works OK. Thanks.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
JezWaltersCommented:
Glad I could be of assistance!  :-)

I take it you managed to work out how to check for an existing XML file/location then?
0
 
DataTrainAuthor Commented:
No actaully! I am just looking at the feasability of doing some work involving XML which I have never used before. I would need to get into the details of the code but I was just trying to get a feel for how easy it is to export some tables. However, any other hints you may want to offer will be greatfully received!
0
 
JezWaltersCommented:
You can use the Dir() function to test whether a given file/directory exists - hope that helps!  :-)
0
 
DataTrainAuthor Commented:
This is a very long short - but I had a friend living in London called James Walters. In the unlikely event that was you, you would know where "The Freddy" was! Anyway, this isn't lost friends, so thanks for your help.
0
 
JezWaltersCommented:
No relation, I'm afraid!  :-)
0
All Courses

From novice to tech pro — start learning today.