Solved

Using Access VBA to write table data to an XML file

Posted on 2010-11-23
8
885 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:DataTrain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 17

Expert Comment

by:JezWalters
ID: 34198566
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
 
LVL 17

Accepted Solution

by:
JezWalters earned 125 total points
ID: 34198691
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
 

Author Closing Comment

by:DataTrain
ID: 34206170
That works OK. Thanks.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 17

Expert Comment

by:JezWalters
ID: 34206226
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
 

Author Comment

by:DataTrain
ID: 34206481
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 34206558
You can use the Dir() function to test whether a given file/directory exists - hope that helps!  :-)
0
 

Author Comment

by:DataTrain
ID: 34206997
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 34207115
No relation, I'm afraid!  :-)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question