Export TABLE as an XML file

[LasseH]

Hi,

Im trying to create a macro in MS ACCESS 2003 that exports a table to an XML file. It works fine doing the export manually but I get problems doing it by using a macro as the XML export option does not exist as a valid export format in the macro editor. I tried to do it by using send keys and then calling the menue command EXPORT but the sendkeys syntax does not work properly yet. Any suggestions on how to do this?

Thanks in advance,

Lars

 

[wiklendt]

have you tried VBA?

this is from F1 help in Access 2007 - i don't know if it will work in previous versions.

Access Developer Reference How to: Export Data, Schema, and Related Tables to XML
The ExportXML method can be used to export the data and formatting contained in a table, along with any additonal data that you specify.
To specify the additional data to export, you must must use the CreateAdditionalData method to create an AdditionalData object. Then, use the Add method to add additonal tables to export along with the main table.
The following procedure illustrates how to include additional data when exporting a table to XML. The Orders table is exported along with several other tables. The schema and the formatting are also exported as separate .xsd and .xsl files, respectively.

     Private Sub ExportRelTables()
   ' Purpose: Exports the Orders table as well as 
   ' a number of related databases to an XML file.
   ' XSD and XSL files are also created.

   Dim objAD As AdditionalData

   ' Create the AdditionalData object.
   Set objAD = Application.CreateAdditionalData

   ' Add the related tables to the object.
   With objAD
      .Add "Order Details"
      objAD(Item:="Order Details").Add "Order Details Details"
      .Add "Customers"
      .Add "Shippers"
      .Add "Employees"
      .Add "Products"
      objAD(Item:="Products").Add "Product Details"
      objAD(Item:="Products")(Item:="Product Details").Add _
         "Product Details Details"
      .Add "Suppliers"
      .Add "Categories"
   End With

   ' Export the Orders table along with the addtional data.
   Application.ExportXml acExportTable, "Orders", _
       "C:\Orders.xml", "C:\OrdersSchema.xsd", _
       "C:\OrdersStyle.xsl", AdditionalData:= objAD
End Sub

 

[LasseH]

have you tried VBA?

this is from F1 help in Access 2007 - i don't know if it will work in previous versions.

Thanks for the hint, this will be something ti digest.... I'll give it a try!

 

[LasseH]

Thanks for the hint, this will be something to digest.... I'll give it a try!

It worked! based on your suggested solution I just created a module that worked great.

Sub test()
Application.ExportXML _
ObjectType:=acExportQuery, _
DataSource:="test", _
DataTarget:="c:/test.xml"
End Sub

Thanks for helping me out!

 

[wiklendt]

glad it worked for you. thank you very much for posting your success, i was interested to know whether previous versions could handle xml

the first thing i always try is F1 - was really hard to understand when i first got into access, but makes a lot more sense now i've been doing it a year and a half

 

[LasseH]

glad it worked for you. thank you very much for posting your success, i was interested to know whether previous versions could handle xml

the first thing i always try is F1 - was really hard to understand when i first got into access, but makes a lot more sense now i've been doing it a year and a half

Oki, I haven't tried working with modules and VB before so this gave me a good push to explore this option for future developments