none
VBA - XML Export and Import. How to selectively update an MS access table from an XML file? RRS feed

  • Question

  • Looking for help on working with XML files.  My objective is to store user configuration data to XML files.  When I have to replace the MS Access database and all it's configuration tables, I would use the XML files to update the new database configuration tables.  I will need to update selected fields and add new records as necessary.  I'm sure there are a number of ways to achieve this.  Suggestions appreciated.

    My initial effort has been to use the 'Application.ExportXML' and 'Application.ImportXML' capabilities.  This has failed because the table schema is not included.  Manually Exporting XML allows me to include the schema, which works.  I would then use append and update queries to update the new database configuration tables.  Where can I find information on how to use VBA and work with XML files to do my updates? 

    Thanks for all your help.
    - Pat

    Sunday, April 29, 2018 9:02 PM

Answers

  • Found the solution to the Application.ExportXML to meet my needs.  It was another options swtich to export the embedded schema.  The export command shouldbe:

    Application.ExportXML ObjectType:=acExportTable, DataSource:="tblConfig", DataTarget:="C:\WTS\Config.xml", OtherFlags:=acEmbedSchema+acExportAllTableAndFieldProperties.

    The Imports now works well...
    - Pat

    • Marked as answer by PSD1953 Monday, April 30, 2018 8:46 PM
    Monday, April 30, 2018 8:46 PM

All replies

  • If you don't want to use the built-in functions like Application.ImportXML, then you might be able to use the msxml library from Microsoft. You can set a reference to it, then use that object model to work with the XML data.

    -Tom. Microsoft Access MVP

    Sunday, April 29, 2018 9:18 PM
  • I would love to use the built-in functions.  I just don't know how to make the import work correctly.

    I export the table and schema via

    Application.ExportXML ObjectTtype:=acExportTable, DataSource:="tblConfig", DataTarget:="C:\WTS\Config.xml", SchemaTarget:="C:\WTS\Config.xsd".

    I get two files: (C:\WTS\Config.xml, C:\WTS\Config.xsd). Exactly what I need.

    My problem is that Application.ImportXML "C:\WTS\Config.xml", acStructureAndData does not include the Schema.

    How can I read both the XML and XSD file to correctly import?

    Thanks,
    -Pat

    Sunday, April 29, 2018 11:18 PM
  • Hi PSD1953,

    From your last post, I can see that you are exporting schema and data to separate files.

    If you refer the documentation then you can see that, If you omit the 'SchemaTarget' parameter then it will be not exported to separate file.

    Then you when you import the file you can set the parameter 'ImportOptions' to 'acStructureAndData'.

    It will import both structure and data.

    Application.ImportXML _ 
     DataSource:="employees.xml", _ 
     ImportOptions:=acStructureAndData

    Reference:

    1. Application.ImportXML Method (Access)
    2. AcImportXMLOption Enumeration (Access)
    3. Application.ExportXML Method (Access)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 30, 2018 2:24 AM
    Moderator
  • Thanks for suggestion, but it doesn't do the job for me.  My table includes a 'password' field and it might make things messy.   If I right-click on the table and do an XML export with the schema included, the application.importxml method works as expected.  If I use the application.exportxml, the schema is not included and therefore importxml does not do the job.

    I'm am now researching how to work with XML.  Any help is appreciated.

    Thanks,
    - Pat

    Monday, April 30, 2018 5:44 PM
  • Found the solution to the Application.ExportXML to meet my needs.  It was another options swtich to export the embedded schema.  The export command shouldbe:

    Application.ExportXML ObjectType:=acExportTable, DataSource:="tblConfig", DataTarget:="C:\WTS\Config.xml", OtherFlags:=acEmbedSchema+acExportAllTableAndFieldProperties.

    The Imports now works well...
    - Pat

    • Marked as answer by PSD1953 Monday, April 30, 2018 8:46 PM
    Monday, April 30, 2018 8:46 PM