How Do I Export my Tables as a Single XML File nested the way I need it?

[MForrest1]

Hello All,

I'm new to Microsoft Access and I'm having difficulty getting an output file I want. I have tried using things I've found from forums, google searches, etc and nothing seems to work.

Goal: I need to output my Access Database as one XML File. My issue is I need to have two tables mapped back to one table. When I try to nest them, one table is nested as it should be, but the other one is just added on the end of the file; not nested like I need it to be.



Any thoughts?

Attached is a picture to show my relationships.

Board Stacks is the upper end Table. Calibration and Station_Captesters needs to be nested inside it. On top of that, I also want to add they lower two tables in series. One would be nested into the other.

[ranman256]

build a query to get the data you need,qsQuery,
then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qsQuery", vFile, True, "tab1"

[MForrest1]

Option Compare Database
Option Explicit


Sub XmlExportTest()
Dim objCal As AdditionalData, objStations As AdditionalData
Set objCal = Application.CreateAdditionalData
objCal.Add "Station_Captesters"
objCal.Add "Calibration"
Application.ExportXML _
ObjectType:=acExportTable, _
DataSource:="Board_Stacks", _
DataTarget:="C:\Users\mdf01\Desktop\Captest_II.xml ", _
AdditionalData:=objCal
End Sub

Above is what I was using to do this, but calibration was showing at the end of the file, instead of being nested. I was trying to stay away from queries as I don't know much about queries, and I have to have all the data in every table I'm using. These tables are configurations, calibration information, part numbers, etc for a machine. I need the software to be able to reference an XML file that Access generates.

[ItsMe]

I don't have an answer for you but my experience is Access, and Office in general, has limited capabilities in XML. My understanding is that any serious formatting requires a third party add in. Because of all this, I have mostly avoided using XML and Access together and opted for alternative API's.


Having said that, this link may be of some assistance. It seems someone went ahead and created some functions to assist with things. The functions are for Excel. You would have to change some things around. For instance, using a Range as a parameter is not going to work. I did not download the code but it is likely to offer insight or maybe a solution.
http://www.codeproject.com/Articles/...-to-XML-in-VBA

[June7]

If export wizard doesn't provide desired output, option is writing lines to a text file. I took a quick look at the code sample referenced by ItsMe. It does have sub for writing constructed string to a text file but not sure how it fits with the other code. Here is excerpt showing that:

Sub sWriteFile(strXML As String, strFullFileName As String)
Dim intFileNum As String
intFileNum = FreeFile
Open strFullFileName For Output As #intFileNum
Print #intFileNum, strXML
Close #intFileNum
End Sub

Bing: VB6 write to xml file
http://www.vbforums.com/showthread.p...te-an-XML-file