export tables to XML

[54uydf]

Hey everybody!
I'll start by saying I'm so happy I found an Access forum!!
Because I need some advice from people who know Access!
I need to export 3 tables to an XML file.
Just to let you know- I know nothing about XMLs and just started using access.
I know I can do File-> Export.....
But I want to make a button on a form that does that.
A. can I export 3 tables together in 1 click?
B. HOW?? I made a quick search about this and I see people talking about VBA and Macros and I don't know any of that.
Is there an easy way to do that without knowing VBA?

 

[dallr]

See if this works for you.
You need to do what you are requesting in code.

If you want to to export specific records then you can use the following.
Code:

Application.ExportXML ObjectType:=acExportTable, _
DataSource:="Customers", _
DataTarget:="C:\Learn_XML\OneCustomer.xml", _
WhereCondition:="CustomerID = 'GROSR'"


If you want to export a table or query then use the following.
Code:

Dim objOtherTbls As AdditionalData

On Error GoTo ErrorHandle
Set objOtherTbls = Application.CreateAdditionalData

'Identify the tables or querys to export
objOtherTbls.Add "Orders"
objOtherTbls.Add "Order Details"

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

MsgBox "Export operation completed successfully."

 

[54uydf]

thanks for the reply, but can u mark the words that I need to replace? like the actual name of the table I want to export etc.
and also what exactly do I do with this code (where to put it?!)
and I didn't see any reference to a button click that makes the code run

btw I want to export all the records from 3 different tables

 

[hinke]

objOtherTbls.Add "Orders"
objOtherTbls.Add "Order Details"

DataSource:="Customers"

Replace the text inside the quotes and put your table names there.

Create a button on a form and create an On Click Event for the button. Place the code there.

 

[777bonzai]

Hi Dallr,
I used your sample codes as reference and I was able to export multiple tables to xml format; however, how could I exclude certain fields or include certain fields from a table? I would like to exclude one field from a table (I do not want to export this particular field), could you please help?

thanks,
Bonzai

 

[777bonzai]

could anyone please help?
thanks,

bonzai

 

[777bonzai]

Does anyone know how to solve this problem? I would like to use VBA code to export two tables from an access database into xml format, but in the code, I would like to exclude certain fields (or include certain fields from the 2nd table). Both tables are link with a primary keys of IDNum. (see my code in the prior posts)

Please help, THIS IS URGENT.

Thanks,
Bonzai

 

[spikepl]

[B]If you want to export a table or [SIZE=6]query[/SIZE] then use the following. [/B]
Code: 
 
Dim objOtherTbls As AdditionalData
 
On Error GoTo ErrorHandle
Set objOtherTbls = Application.CreateAdditionalData
 
'Identify the tables or [SIZE=6]querys [/SIZE]to export
objOtherTbls.Add "Orders"
objOtherTbls.Add "Order Details"

 

[777bonzai]

hi spikepl,
I did, I wrote the code to export the tables, but I am stuck with the part where I would like to export only certain fields form the table. here is my code:
-----------------
Private Sub Command4_Click()
Dim objOtherTbls As AdditionalData
'On Error GoTo ErrorHandle
Set objOtherTbls = Application.CreateAdditionalData
'Identify the tables or querys to export
objOtherTbls.Add "customer"
objOtherTbls.Add "order"

'Here is where the export takes place
Application.ExportXML ObjectType:=acExportTable, _
DataSource:="main", _
DataTarget:="C:\Learn_XML\main.xml", _
AdditionalData:=objOtherTbls
MsgBox "Export operation completed successfully."
End Sub
------------------

any idea that I could include certain fields or exclude certain from table customer? I do not want to export the data out as a query because I want the hierarchical structure from the table relationship.
thanks,
Bonzai

 

[spikepl]

I think you are confusing yourself needlessly. A table is a table - you want to export less, then make a temp table (a bit pointless, though) or a query.

Access does not export relations to XML, only the tables/queries you indicate. Relations are buried in the data in the shape of keys, no matter whether tables or queries.

 

[777bonzai]

spikepl,
Thanks for helping me out. I am so glad that you are spending time to review the problem and provide feedback.
Here is the detail of my problem.
I have an access db with two tables. 1st table is Customer and 2nd is Order.
Customer table has 3 fields CustomerD, Name, Address
Order table has 3 fields, CustomerID, OrderID, and Detail.
These two tables are linked via key CustomerID.
Now I would like to export out the two tables but exclude field CustomerD from table Order. The expected xml data output structure as follow:
<dataroot>
<customer>
<customerID>9999</customerID>
<name>John Doe</name>
<address>999abc stret</address>
<order>
<orderID>89898</orderID>
<detail> abcdef</orderdetail>
</order>
</customer>
'continue the other customers in the tables and then close dataroot.
</dataroot>
My VBA code exports out the tables and carry out the the tag <order>, but then the CustomerID is repeated. I want to exclude this customerID from the Order table.
Now, this sounds like a flat file to me (like you said, why not create a query and export the query out, but then query won't be able to make the order tag for me in the xml data output). I am not sure why the xml structure is designed that way, but my customer wants it that way.

Any idea/help/suggession would be greatly appreciated.

thanks,
bonzai

 

[777bonzai]

spikepl,
just want to add another detail:

when I create a query an export into xml the xml data structure looks like this
<dataroot>
<customer>
<customerID>9999</customerID>
<name>John Doe</name>
<address>999abc stret</address>
<orderID>89898</orderID>
<detail> abcdef</orderdetail>
</customer>
'continue with the rest of the customer records in the query, and then close data root
</dataroot>

The above xml data missed tag <order> and </order>.

thanks,
Bonzai

 

[spikepl]

You speak of "expected" output. Did you actually TRY? First try exporting the tables and then come back. I suppose you are expecting the relation in the data between customer and order to be reflected in the structure of the XML. I do not know how to accomplish that using the built-in export tool of Access. From what I have tried it does a flat dump of data of each table involved.

Since you know excactly what you need, nothing prevents you from looping over your records and related records, and creating the XML file line by line in whichever way you want, by writing each tag yourself.

I have no idea what you query contains, so cannot comment. Remember that the contributors here cannot read minds or guess what is on your screen. But I suspect that you have made one query, and then the query will of course export each line of the recordset created. One option could be to do that, and thereafter manually, or by code, or perhaps a Word -macro, which is easily created, to change the text results to fit you needs.

 

[777bonzai]

spikepl,
I did try to export the data out both ways: using tables and using query. That's how I know the exact output of the xml data for both ways. When I said "expected" output, I meant the expected output from my customer.

Since you know excactly what you need, nothing prevents you from looping over your records and related records, and creating the XML file line by line in whichever way you want, by writing each tag yourself.
could you please explain more about this? I am new to xml so I do not know how to write/control the tag. I just learned how to export tables and queries from access to xml using vba codes. Once the data is exported, the xml file is open in an html format.

thanks,
Bonzai

 

[777bonzai]

when I exported the two tables to xml using vba xml export method, access carried every fields in the table; where I only wanted to carry certain fields. I guess access bulit in xml method could not do this feature????
thanks,

Bonzai

 

[spikepl]

You are mixing different issues here.

Access exports what you tell it to export. A query allows you to select columns of a table, and the export tool can then export that, instead of the entire table. That is the difference between query and table, when you use them to feed the ExportXML method.

Youi want the tool to make an XML file reflecting your structure between the two tables. I don't know how to do that, and I suspect the tool cannot do that. The issue is not exporting less data, but structuring the XML file reflectign teh relations. And theExportXML apparently cannot do that.

What do you mean you don't know how to write/control the tag? You have written what you want the output to look like. You know where the information comes from. All you have to do is in code to loop over alle the records and related records, extract the fields of interest, add a "<"& tagname & ">" & WhateverContent & "</" &" tagname & ">"and dump it in a textfile, all wrapped in some Root-tag that you can grab from a file exported by the ExportXML tool.

 

[swk003]

Hi i am newbie and am trying to get the following code to work from a button object OnClickEvent():

Private Sub Command0_Click()

Dim objOtherTbls As AdditionalData

Set objOtherTbls = Application.CreateAdditionalData

'Identify the tables or querys to export
objOtherTbls.Add "Cycle"
objOtherTbls.Add "Cycle1"


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

MsgBox "Export operation completed successfully."
End Sub

However I am getting a runtime error 2467:
The expression you entered refers to an object that is closed or doesnt exist.

The debugger is pointing to this line:
'AdditionalData:=objOtherTbls'
can anyone helP?

 

[Rx_]

Just so you can get started:
scroll down to links at bottom:
http://www.access-programmers.co.uk/forums/showthread.php?t=248439