Exporting Database to XML (1 Viewer)

LinksAwakener

Registered User.
Local time
Today, 03:42
Joined
Feb 6, 2019
Messages
12
Okay, I know how to export specific tables to XML using Application.ExportXML and I was able to successfully use that.

But it doesn't quite fit what I need...

I'm looking to export ALL tables to XML, and there are many. Many relationships, too. I just want to export the data from each table into its own XML element, no fancy nesting or anything. How do I do that? :confused:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:42
Joined
Oct 29, 2018
Messages
21,357
Hi. Are you saying, basically, you want to execute Application.ExportXML as many times as you have tables?
 

LinksAwakener

Registered User.
Local time
Today, 03:42
Joined
Feb 6, 2019
Messages
12
I would like one XML file to have all my tables exported into. So...
Code:
<dataroot ...>
   <tbl1>
      <tbl1 data...></tbl1 data...>
   </tbl1>
   <tbl2>
      <tbl2 data...></tbl2 data...>
   </tbl2>
...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:42
Joined
Oct 29, 2018
Messages
21,357
Okay, one way to do it is to export all tables into separate XML files and then combine them all into one. Another way is to somehow hold each exported XML table into a variable and then append it to a text file and process each table that way.
 

isladogs

MVP / VIP
Local time
Today, 10:42
Joined
Jan 14, 2017
Messages
18,186
If you try and export all tables to the same XML file, you will overwrite the existing data as each table is added
 

LinksAwakener

Registered User.
Local time
Today, 03:42
Joined
Feb 6, 2019
Messages
12
If you try and export all tables to the same XML file, you will overwrite the existing data as each table is added

Yeah, that's the entire issue. That, and relationship dependencies when importing. I just want a simple export/import capability...

Is there something I'm missing in the ImportXML method? Is there a way to import all the data simultaneously so I don't get dependency complaints?
 

Cronk

Registered User.
Local time
Today, 21:42
Joined
Jul 4, 2013
Messages
2,770
ImportXML? The thread started on ExportXML


Going back to the original post, seems to me that to conform with XML standards, you would have to make one query that combined all the table fields and then export that.


Alternatively, export all of the tables individually and concatenate the XML files.


Incidentally, can you say why you want to export all data to one XML file?
 

LinksAwakener

Registered User.
Local time
Today, 03:42
Joined
Feb 6, 2019
Messages
12
ImportXML? The thread started on ExportXML


Going back to the original post, seems to me that to conform with XML standards, you would have to make one query that combined all the table fields and then export that.


Alternatively, export all of the tables individually and concatenate the XML files.


Incidentally, can you say why you want to export all data to one XML file?

Yes, I began with questions about ExportXML, you're right, but ImportXML is its counterpart -- they go hand in hand. ExportXML won't do me any good if ImportXML can't work.

I can give broad strokes about why I want to do this, yes. The idea is this: I'll have different instances of this same Access program on different machines, and users will be able to send their database (as a single exportable file easily emailed or shared in other ways) to another user, who can then import the database. If there's an easier way to do this, I'm all ears.
 

isladogs

MVP / VIP
Local time
Today, 10:42
Joined
Jan 14, 2017
Messages
18,186
Export to XML is intended for individual tables ... NOT as a means of copying all tables in an entire database

What's wrong with using a standard split database with each user having their own copy of the FE and a SHARED BE on the network?

Nothing needs to be exported / imported

Even if these are standalone copies not on a network, there are much easier ways of copying the data to another PC
 

LinksAwakener

Registered User.
Local time
Today, 03:42
Joined
Feb 6, 2019
Messages
12
Export to XML is intended for individual tables ... NOT as a means of copying all tables in an entire database

What's wrong with using a standard split database with each user having their own copy of the FE and a SHARED BE on the network?

Nothing needs to be exported / imported

Even if these are standalone copies not on a network, there are much easier ways of copying the data to another PC

Each user needs a stand-alone back end.

Again, I'm all ears and open to a better solution if you have one. The users specifically asked for XML for future compatibility, but if an argument can be made for another format...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:42
Joined
Oct 29, 2018
Messages
21,357
Each user needs a stand-alone back end.

Again, I'm all ears and open to a better solution if you have one. The users specifically asked for XML for future compatibility, but if an argument can be made for another format...
Hi. Pardon me... I posted a reply to your other thread at MSDN as well. I was trying to understand the exact problem you're having. Okay, we can export each table into XML. We can also import each XML file into tables. What was the problem with "dependencies?" As a quick test, I exported the tables into XML and imported those XML files into tables but did not have any issues. So, there must be something I am missing about your particular situation. Would you care to elaborate further? Thanks.
 

LinksAwakener

Registered User.
Local time
Today, 03:42
Joined
Feb 6, 2019
Messages
12
Hi. Pardon me... I posted a reply to your other thread at MSDN as well. I was trying to understand the exact problem you're having. Okay, we can export each table into XML. We can also import each XML file into tables. What was the problem with "dependencies?" As a quick test, I exported the tables into XML and imported those XML files into tables but did not have any issues. So, there must be something I am missing about your particular situation. Would you care to elaborate further? Thanks.

No worries, I'd be happy to clarify!

RE: ExportXML; right now I have exported each table to a separate XML. This is less than ideal, but I'll take it.

RE: ImportXML; the issue is circular dependencies. I have something like 110 tables, and the relationships between them are...well it's like spaghetti. Not my design, but it is now my responsibility. Usually databases have a solution for this issue where it'll import ALL data before it commits the data, thereby eliminating the issue of missing data that just hasn't yet been imported.

Does that answer the question, or am I still not explaining my situation very well?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:42
Joined
Oct 29, 2018
Messages
21,357
No worries, I'd be happy to clarify!

RE: ExportXML; right now I have exported each table to a separate XML. This is less than ideal, but I'll take it.

RE: ImportXML; the issue is circular dependencies. I have something like 110 tables, and the relationships between them are...well it's like spaghetti. Not my design, but it is now my responsibility. Usually databases have a solution for this issue where it'll import ALL data before it commits the data, thereby eliminating the issue of missing data that just hasn't yet been imported.

Does that answer the question, or am I still not explaining my situation very well?
Hi. Thanks for the clarification. This helps understand the issue. Unfortunately, because of the dependency issues you're having, I guess simply using the built-in ImportXML method will not be enough. You could implement your own import procedure using transactions where you can take advantage of the COMITTRANS and ROLLBACK commands you were referring to earlier. However, it might be worth your time as well to simply fix the data integrity issues in the database itself, so that using the built-in ImportXML method will just work. In my own little experiment using only two tables with a one-to-many relationship, I didn't get an ImportErrors table when I deleted one parent record and imported the resulting XML files into a new database file. Sorry if I can't help any further. Good luck!
 

LinksAwakener

Registered User.
Local time
Today, 03:42
Joined
Feb 6, 2019
Messages
12
Hi. Thanks for the clarification. This helps understand the issue. Unfortunately, because of the dependency issues you're having, I guess simply using the built-in ImportXML method will not be enough. You could implement your own import procedure using transactions where you can take advantage of the COMITTRANS and ROLLBACK commands you were referring to earlier. However, it might be worth your time as well to simply fix the data integrity issues in the database itself, so that using the built-in ImportXML method will just work. In my own little experiment using only two tables with a one-to-many relationship, I didn't get an ImportErrors table when I deleted one parent record and imported the resulting XML files into a new database file. Sorry if I can't help any further. Good luck!

I appreciate all the help from everybody here!

I was able to progress further on this feature, thankfully. As stated before, I am using individual XML files for each of the tables. Not ideal, but it is what it is. The export works great, otherwise.

Now, for the import. I'm having some issues. The first step in importing would be to clear the data in the current tables so the imported data doesn't have any duplicate entries or mismatched IDs. I have a For Each loop to clear the data, but it doesn't seem to work perfectly. Here's the code:

Code:
Dim db As Database
Dim tbl As TableDef
Set db = CurrentDb
For Each tbl In db.TableDefs ' Remove each table in preparation for import
    If Not (tbl.Name Like "MSys*" Or tbl.Name Like "~*" Or tbl.Name Like "LocalConfiguration*" Or tbl.Name Like "Import*") Then
        Debug.Print "Clearing " & tbl.Name & " from the system"
        db.Execute "DELETE * FROM " & tbl.Name
    End If
Next
                
Set tbl = Nothing
Set db = Nothing

Running this removes the data SOMETIMES, but not ALL the time. VERY weird. For one of my tables, AttachmentTypes, it removed 2 out of 3 entries. For some reason, the first row didn't get deleted.

This has to be a 'me' issue. What am I doing wrong?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:42
Joined
Oct 29, 2018
Messages
21,357
Hi. Just a thought... If you add the dbFailOnError parameter, you might get a message why the code fails sometimes. For example:


db.Execute strSQL, dbFailOnError
 

Cronk

Registered User.
Local time
Today, 21:42
Joined
Jul 4, 2013
Messages
2,770
Re #8
I'll have different instances of this same Access program on different machines, and users will be able to send their database (as a single exportable file easily emailed or shared in other ways) to another user, who can then import the database.
This to me sounds like you have not split your database ie tables,queries,forms etc are all in the one database file and you only want to export the data.
Why not split the data tables into a back end file and send that accdb file to other users.


Your response above seems to imply several users are adding/editing data. How are you going to synchronize changes?
 

LinksAwakener

Registered User.
Local time
Today, 03:42
Joined
Feb 6, 2019
Messages
12
Re #8

This to me sounds like you have not split your database ie tables,queries,forms etc are all in the one database file and you only want to export the data.
Why not split the data tables into a back end file and send that accdb file to other users.


Your response above seems to imply several users are adding/editing data. How are you going to synchronize changes?

The database is split into back-end and front-end. The feature request from the users was specifically that they want to have the ability to back up and restore using XML files.

The users have no need to merge data; each person is going to have their own records. I can't get into much detail, but you can think of it like this; each user's version of this software is going to contain their own personal notes on a particular item. This is meaningful to share between users once in a while, but having everyone on one back-end would muddy the waters.



I was able to implement a solution, fully functional. I had to do things the hard way: test each table as it was imported for dependency complaints. When there was a complaint, rearrange the import procedure to import that particular table later and test again. I hate hard-coding all those tables like that, but when there's dependency constraints and it has to be done in a specific order...¯\_(ツ)_/¯
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:42
Joined
Oct 29, 2018
Messages
21,357
Hi. Congratulations! Glad to hear you got it sorted out. Good luck!
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:42
Joined
Sep 21, 2011
Messages
14,038
I hate hard-coding all those tables like that, but when there's dependency constraints and it has to be done in a specific order...¯\_(ツ)_/¯

So no doubt you could create a table which would show the order that they need to be imported and could be amended easily should that change.?
 

Users who are viewing this thread

Top Bottom