none
What is the best way to use Access 2010 to export table data to XML specified by an external (vendor) XSD? RRS feed

  • Question

  • I recently started job that requires me to collect data/reports of differing formats (Excel reports along with .csv formatted records) from HR/timekeeping systems, do a handful of calculations, and then export those results to an XML file as defined by an external vendor's XSD (provided).

    The majority of this work was done manually prior to my hire. I would like to automate this work with a script that can work on the imported data tables (in Access 2010) and export an XML file according to the supplied schema.

    I'm a programmer with experience in C/C++/Perl (mostly for embedded applications on Linux) but this is a new arena for me and, after a bit of research, I decided to use Access to manage the data sources and write a VB script using LINQ to export the data to XML...

    What are the general classes I should consider (or not waste time) writing?

    Are there any good examples out there (or templates within Access) that can guide me?

    What (avoidable) pitfalls should I be aware of?

    Any advice would be much appreciated...

    Thanks!




    Wednesday, December 9, 2015 8:32 PM

Answers

  • >>>I think what I am gearing towards is writing an add-in in VB using Visual Studio. I've used MSVC 2012 before, indeed, one of the questions I've run into since starting this job is if I will be hampered too much by using older versions of Access/Visual Studio (2010) <<<

    Firstly, if you mean create VSTO add-ins in VB, I am afraid of saying that VSTO add-ins don't support Access.

    For more information, click here to refer about Features Available by Office Application and Project Type

    Secondly, if you want to export Data, Schema, and related Tables to XML, you could refer to this helpful link:

    https://msdn.microsoft.com/en-us/library/office/ff193785.aspx

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:34 PM
    Friday, December 11, 2015 9:04 AM

All replies

  • The built in XML features of Access are limited. However, what I would consider is in place of a XSD, is I would get a sample data file that conforms to the spec. So get/grab a XML file based on the XSD. I would then import this file into Access and see what occurs.

    The reason for above is such a simple import will create standard tables in Access. It is then far easier to work with these table structures, fill the data, and then do an xml export which should result in the same format.

    If the above works, then you really don’t have to write much code if any at all.

    However, most XML tends to have repeating data, and Access tends to not deal too well with such child tables of data. Attempting to import a sample file will often tell you the story as to how well such data is going to work inside of Access.

    Beyond above, then another great way to deal with XML data is to consider using the MSXML library. This library can be used from VBA, and nearly all computers have MSXML installed.

    Another approach would be to consider writing an add-in with Visual Studio – but then that assumes you have good experience with Visual Studio (if you do, then the many xml libraries are available).

    So first step would be to import a sample XML data file into Access. You thus can see what Access does with the XML file. You want to try this, since if Access consumes the file, then likely Access can also export the same tables and result in the correct XML format that conforms to the XSD def file. (so this may be a lucky break – it depends!).

    Use of MSXML has no more to do with Access + VBA, then say VBS scripting, or say c++ or vb.net – all such dev tools can use the MSXML library. You could even write such code in c++ and use the MSXML library if that is your cup of tea. And thus then call that code from Access (especially if you expose the c++ code as a class library (COM object)).

    And another quick and dirty was to import XML data? Pull it into Excel – it rather remarkable how well Excel imports such data. The resulting Excel data can then be imported into Access.

    Much of the success of working with XML data will mostly depend on what tools you are familiar with, or which ones you have available, or which ones you are willing to adopt (they all have learning curves – pick the one you used the most).

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Thursday, December 10, 2015 5:01 AM
  • ...

    The built in XML features of Access are limited. However, what I would consider is in place of a XSD, is I would get a sample data file that conforms to the spec. So get/grab a XML file based on the XSD. I would then import this file into Access and see what occurs.

    The reason for above is such a simple import will create standard tables in Access. It is then far easier to work with these table structures, fill the data, and then do an xml export which should result in the same format.

    If the above works, then you really don’t have to write much code if any at all.

    However, most XML tends to have repeating data, and Access tends to not deal too well with such child tables of data. Attempting to import a sample file will often tell you the story as to how well such data is going to work inside of Access.

    Beyond above, then another great way to deal with XML data is to consider using the MSXML library. This library can be used from VBA, and nearly all computers have MSXML installed.

    Another approach would be to consider writing an add-in with Visual Studio – but then that assumes you have good experience with Visual Studio (if you do, then the many xml libraries are available).

    ...

    Thanks much for your response!!

    Most immediately I will follow you advice and find a tool that can generate a sample XML file from the XSD I've been provided. It will have to be something a bit more robust than most of the web-based tools I've found thus far since my XSD is spread across two files (a Report.xsd that references an Enumerations.xsd).

    I think what I am gearing towards is writing an add-in in VB using Visual Studio. I've used MSVC 2012 before, indeed, one of the questions I've run into since starting this job is if I will be hampered too much by using older versions of Access/Visual Studio (2010) 

    Will the version of Access/Visual Studio I use affect at all the version of MSXML or Visual Basic that is available and how might this factor into my overall approach?

    Reading about this process online I've found numerous articles detailing LINQ to XML:

    https://msdn.microsoft.com/en-us/library/bb384460.aspx

    Where does LINQ fit into all of this... is this useful for what I am trying to achieve? 

    The quick and dirty way to get this done with regards to deadline may be to see if I can get Access to accept a sample XML file. If I can get the XML into table form then will I be able to write a (VB?) script that will pull the necessary data from the tables I have already.


    Thursday, December 10, 2015 10:33 PM
  • >>>I think what I am gearing towards is writing an add-in in VB using Visual Studio. I've used MSVC 2012 before, indeed, one of the questions I've run into since starting this job is if I will be hampered too much by using older versions of Access/Visual Studio (2010) <<<

    Firstly, if you mean create VSTO add-ins in VB, I am afraid of saying that VSTO add-ins don't support Access.

    For more information, click here to refer about Features Available by Office Application and Project Type

    Secondly, if you want to export Data, Schema, and related Tables to XML, you could refer to this helpful link:

    https://msdn.microsoft.com/en-us/library/office/ff193785.aspx

    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:34 PM
    Friday, December 11, 2015 9:04 AM