MS Access export to specific XML format

Question

I use Access to update a series of ~300 files between ~20 databases which I built/maintain the back end of, but the end users who operate the database are novice at best when it comes to anything tech related. This has been working great for the exports I am allowed to submit in CSV or XLSX, but now I need to tackle the large chunk of exports that must match a specific XML template on export. I can import XML fine, but I am struggling to get it back into the exact same XML format ~100 times without needing the user to know how to manually update files. Currently I have the export in CSV and the user just copies and pastes the column into the XML file, but now we are at a point where we are talking about doing that for ~100 files a week. It's not pretty.

The XML file format I need to match has set text in the first 7 rows that needs to remain consistent each time and has nothing to do with updates in the database. Row 8 is the field names and the remaining rows are the export of what the database is updating. I should also add that VBA isn't my comfort zone but willing to work on it with guidance.

Any help is appreciated! I've been researching this a lot of and coming up with nothing.

Does this answer your question? Exporting Access Table into XML using VBA — June7
– June7, Commented Dec 13, 2020 at 20:58
Or stackoverflow.com/questions/31659881/… or stackoverflow.com/questions/38891403/… — June7
– June7, Commented Dec 14, 2020 at 1:16
If an xlst file can't work, alternative is writing strings to text file vbahowto.com/how-to-generate-a-xml-file-with-access-vba — June7
– June7, Commented Dec 14, 2020 at 1:32

Albert D. Kallal · Accepted Answer · 2020-12-14 10:04:50Z

0

one really neat trick?

Create a blank access database. Then import the xml file. What do you get?

If it spits out one table - then you can use that SAME table, fill the data, and export.

And in fact if the import creates several tables? You can again use that SAME set of tables, and with some code, fill out the tables, and then export as XML - you should get the same output format.

How well the above works? Well, it really depends on how complex the xml data is in the first place. If it is quite simple, then this can be quite a nice solution.

So, once you import the xml file in that test database? Look at the tables. And now try a export of the xml - if the output is the same format - then this can really help you.

answered Dec 14, 2020 at 10:04

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jessica Gordon Over a year ago

Hi, thank you for the suggestion. I imported the file into a blank database which resulted in 14 tables and most are blank.

Albert D. Kallal Over a year ago

ok - so now try a export - do you get the same resulting file? If you do, then you can use code to fill out the tables from "your" data - and you now have conversion system in place. If this does not work, then you going to have to build the output file on your own - that will take code - using the MSXML library + VBA code then would be the next best step. So this xml import trick may or may not help - but it DOES give you an idea of how the xml translates into base related tables.

Jessica Gordon Over a year ago

Yeah this definitely didn't work. I can only export the 14 tables one by one and they all result in gibberish.

Collectives™ on Stack Overflow

MS Access export to specific XML format

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related