Export several tables out to one .XML file and bring it back

[Kelly]

Does anyone know if it is possible to export several tables from a database
into a .XML file and then be able to bring that same data back into a
database. Strange question but since .XML is kind of standard the person who
stores our data would like it in this format for the future. Any thoughts....
Kelly

 

[Jeff Boyce]

Kelly

I may not be understanding your situation ... take my observations with a
grain of salt.

It sounds like you are trying to export the contents of several tables into
a single .XML file. Wouldn't this imply that the several tables have the
same structure (i.e., if they all fit in a single .XML file)?

If this is your situation, your table structure may need further
normalization. In a well-normalized relational database, it would be
unlikely you'd have more than one table with the same structure.

Or maybe I just don't understand...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kelly]

We have an administrator that would like the data in an .XML format for
storage. I read a posting where you can combine several files into one .XML
but bringing it back would be a nightmare. Does anyone know if 2007 offers
such a thing or maybe another file format that can be exported and saved?
Kelly

 

[Jeff Boyce]

So you're saying that you have several (?tables), each one different?

If they are related, you could use a query to "flatten" the data that needs
to be exported.

When you bring it back in, you would have to "normalize" it (you could use
queries to 'parse' it into your well-normalized table structure).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kelly]

Thanks for the reply Jeff. Could you expalin how to do that.
Kelly

 

[Jeff Boyce]

First, your tables have to be related (if "Relational" and "normalization"
are unfamiliar terms, your tables may not be 'related').

If your tables can be "JOIN"ed together via common fields they share (i.e.,
a pair of tables both have PersonID as a field), then create a query, pull
in the related tables, join them on their shared field(s) and select all the
fields you wish to export to .XML -- when you run this query, you get all
the values you'd be exporting.

I don't have any experience exporting to .XML, but once you have the data
set you need, I suspect it's fairly straightforward -- try searching on-line
for "MS Access export to XML".

Regards

Jeff Boyce
Microsoft Office/Access MVP