Solved

Using Access VBA to write table data to an XML file

Posted on 2010-11-23
8
863 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
  • 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now