469,267 Members | 954 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

export tables to xml

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.

Expand|Select|Wrap|Line Numbers
  1. Sub ExportCustomerOrderData()
  2. Dim objOrder As AdditionalData
  3. Dim objDetail As AdditionalData
  4.  
  5. Set objOrder = Application.CreateAdditionalData
  6. Set objDetail = Application.CreateAdditionalData
  7.  
  8. Set objOrder = objOrder.Add("Order")
  9. Set objDetail = objDetail.Add ("Detail")
  10.  
  11. Application.ExportXML 
  12. ObjectType:=acExportTable, _
  13. DataSource:="C:\Customer", _
  14. DataTarget:="Customer Orders.xml", _
  15. AdditionalData:=objOrder, _
  16. AdditionalData:=objDetail
  17. End Sub
  18.  
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
Jan 25 '12 #1
6 6805
NeoPa
32,171 Expert Mod 16PB
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.
Jan 25 '12 #2
Hi NeoPa,
I've made corrections to the code. the code is under the OnClick event from a command button names Command4
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command4_Click()
  2. Dim objOtherTbls As AdditionalData
  3.  
  4. Set objOtherTbls = Application.CreateAdditionalData
  5.  
  6. 'Identify the tables to export
  7. objOtherTbls.Add "Order"
  8. objOtherTbls.Add "Detail"
  9.  
  10. 'Here is where the export takes place
  11. Application.ExportXML ObjectType:=acExportTable, _
  12. DataSource:="Customer", _
  13. DataTarget:="C:\Learn_XML\CustomerOrderDetail.xml", _
  14. AdditionalData:=objOtherTbls
  15.  
  16. MsgBox "Export operation completed successfully."
  17. End Sub
  18.  
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
Jan 26 '12 #3
NeoPa
32,171 Expert Mod 16PB
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.
Jan 26 '12 #4
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:
Expand|Select|Wrap|Line Numbers
  1. <customer>
  2. <customerID>99999</customerID>
  3.    <Order>
  4.      <OrderID>99999000</OrderID>
  5.      <OrderDesc>99999000Desc</OrderDesc>
  6.    </Order>
  7.    <Detail>
  8.      <DetailID>9999000111</DetailID>
  9.      <DetailDesc>9999000111DetailDesc</DetalDesc>
  10.    </Detail>
  11. </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:
Expand|Select|Wrap|Line Numbers
  1. <customer>
  2. <customerID>99999</customerID>
  3. <OrderID>99999000</OrderID>
  4. <OrderDesc>99999000Desc</OrderDesc>
  5. <DetailID>9999000111</DetailID>
  6. <DetailDesc>9999000111DetailDesc</DetalDesc>
  7. </customer>
thanks,
bonzai
Jan 26 '12 #5
NeoPa
32,171 Expert Mod 16PB
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.
Jan 26 '12 #6
NeoPa, thanks for trying.
Bonzai
Jan 26 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

15 posts views Thread by Shyguy | last post: by
1 post views Thread by aaron.kempf | last post: by
4 posts views Thread by Richard | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.