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?
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.
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?
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.
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...
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.
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.
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?
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!
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 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?
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?
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...¯\_(ツ)_/¯