export tables to xml

[777bonzai]

I have an access db with 3 tables: Customer, Order, Detail. These three tables are linked via a field calls customerID. I would like to export these three tables to xml but I do not want to export the field "customerID " from the table Order and table Detail.

Sub ExportCustomerOrderData()
Dim objOrder As AdditionalData
Dim objDetail As AdditionalData
    
Set objOrder = Application.CreateAdditionalData
Set objDetail = Application.CreateAdditionalData
    
Set objOrder = objOrder.Add("Order")
Set objDetail = objDetail.Add ("Detail")
    
Application.ExportXML 
ObjectType:=acExportTable, _
DataSource:="C:\Customer", _
DataTarget:="Customer Orders.xml", _
AdditionalData:=objOrder, _
AdditionalData:=objDetail
End Sub

somehow this code only export out tables customer and table Order but not table Detail. In addition, I do not know how to code so that only certain fields from tables Order and table Detail to be exported because I do not want export CustomerID field from table Order and table Detail

could someone please help?
thanks,
bonzai

[NeoPa]

We can't work with your code because it's not what you say it is. It won't even run as it's missing necessary elements. Please read When Posting (VBA or SQL) Code before posting code again.

Each table exported can be a separate command. Without reliable code to look at I have no idea why it's not doing as you expect. The link above will help you avoid many of the more obvious coding problems.

If only selected fields are required in an export, then you can create a query (QueryDef object) that includes just the required fields, and export that in place of the table.

[777bonzai]

Hi NeoPa,
I've made corrections to the code. the code is under the OnClick event from a command button names Command4

Private Sub Command4_Click()
Dim objOtherTbls As AdditionalData

Set objOtherTbls = Application.CreateAdditionalData

'Identify the tables to export
objOtherTbls.Add "Order"
objOtherTbls.Add "Detail"

'Here is where the export takes place
Application.ExportXML ObjectType:=acExportTable, _
DataSource:="Customer", _
DataTarget:="C:\Learn_XML\CustomerOrderDetail.xml", _
AdditionalData:=objOtherTbls

MsgBox "Export operation completed successfully."
End Sub

my db has three tables: Customer, Order and Detail. The code above was able to export data from all three tables to xml format. But I would like to find away where I could only include certain fields from the table Order and Detail.

thanks for your help,

Bonzai

[NeoPa]

NeoPa:
If only selected fields are required in an export, then you can create a query (QueryDef object) that includes just the required fields, and export that in place of the table.

I answered that in post #2.

[777bonzai]

yes, I could export a query instead of a table; however, my goal is to export the data to xml with a hierarchical structure as follow:

<customer>
<customerID>99999</customerID>
   <Order>
     <OrderID>99999000</OrderID>
     <OrderDesc>99999000Desc</OrderDesc>
   </Order>
   <Detail>
     <DetailID>9999000111</DetailID>
     <DetailDesc>9999000111DetailDesc</DetalDesc>
   </Detail>
</customer>

The 3 tables are linked based on customerID. If I create a query and export from the query, xml format does not carry over the <Order>, </Order>, <Detail> and </Detail> tag. It'll just show as a flat file:

<customer>
<customerID>99999</customerID>
<OrderID>99999000</OrderID>
<OrderDesc>99999000Desc</OrderDesc>
<DetailID>9999000111</DetailID>
<DetailDesc>9999000111DetailDesc</DetalDesc>
</customer>

thanks,
bonzai

[NeoPa]

I can't really help there I'm afraid. My XML experience is very thin :-( My responses were based on general database theory where a query (QueryDef) can be used in place of a table in most situations. Maybe someone with more XML experience will come along to help.

[777bonzai]

NeoPa, thanks for trying.
Bonzai