Exporting MS Access Date to a InfoPath XML formated file

kixelsyD
kixelsyD used Ask the Experts™
on
I need to create XML files for every entree in a Access 2007 Database table. The formate of the XML file needs to match the formate of a InfoPath Document template. This InfoPath template Doucment is published to a sharepoint Form Doucment.
What I am tying to do is to automate the proccess of Creating the InfoPath documents rather than entering all the information into InfoPath manually to create the Documents localy.
After the Documents are created, they are uploaded to a Form Document library on a Sharepoint Site or, have the InfoPath XML files saved directly to the Library.
Thank you,
Abe
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Abe,

You can Automate this process using the Application.ExportXML() and the Application.TransformXML() functionality in Access.

Check below link out of using XML features in Access:
http://msdn.microsoft.com/en-us/library/aa167844%28v=office.11%29.aspx

HTH,
Daniel

Author

Commented:
I've  tried the examples shown on the website, but they are not working. This could be because I am using Access 2007.
They should work in Access 2007, I use these myself in Access 2007.

Why are these not working for you? Any errors?

HTH,
Daniel
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
The script produces the xml, xsl, xsd, and htm files. When I run the htm file, it should give me the info listed in the XML file. But instead, it tries to open up a new IE Tab but it seems to be going through an endless loop, not showing anything.
I've added the code that I am using.
Thanks
Private Sub ExportRelTables()
   ' Purpose: Exports the Orders table as well as
   ' a number of related database 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 "Orders Details"
      objAD("Orders Details").Add "Orders Details Details"
      .Add "Customers"
      .Add "Shippers"
      .Add "Employees"
      .Add "Products"
      objAD("Products").Add "Product Details"
      objAD("Products")("Product Details").Add _
         "Product Details Details"
      .Add "Suppliers"
      .Add "Categories"
   End With
Application.ExportXML acExportTable, "Orders", _
        "H:\Orders.xml", _
      "H:\OrdersSchema.xsd", _
         "H:\OrdersStyle.xsl", AdditionalData:=objAD
End Sub

Open in new window

You cant use the OrdersStyle.xsl with the ExportXML() command, the transformation will be done with the TransformXML() command.

HTH,
Daniel

Author

Commented:
Hi Daniel,
I am awarding you the points. I am unable to create a working VBA script, but I know it's posible. Instead of using scripts, I am going to do a mail merge with MS Word. The XML code will be in the Word Document and I'll just point the data to the SQL DB. I've tested it and It works. It's not the best solution, but never the less, it works. "Poor Man's Coding"

Thanks for your help,
Abe

Author

Commented:
VBA is the way to go with this solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial