Automatically importing an XML file to Access?

[SomethngWicked]

Hi Access Wizards,

I have a MS Access 2010 Database that I use to import an XML file on a daily basis. The XML file is generated each morning via FTP and lands in the same folder as the Access database. Is it possible to have Access automatically pick up/import this file? The XML file name stays the same with the exception of the end date (e.g.: FileName_03182019). This is a somewhat tedious process and it would be awesome to somehow automate this step.

I've done some preliminary research online, but I can't find anything conclusive. Thanks for the help!

 

[andrewmrichards]

Hi Access Wizards,

I have a MS Access 2010 Database that I use to import an XML file on a daily basis. The XML file is generated each morning via FTP and lands in the same folder as the Access database. Is it possible to have Access automatically pick up/import this file? The XML file name stays the same with the exception of the end date (e.g.: FileName_03182019). This is a somewhat tedious process and it would be awesome to somehow automate this step.

I've done some preliminary research online, but I can't find anything conclusive. Thanks for the help!

Yes, it is possible, but not in a particularly elegant way...

3 options:

1. If the database runs every day (because at least one person logs in every day) then you could have, as part of the startup code a check to see whether the XML folder has been scanned yet today, or whether the XML file was last imported (depending on what exactly you want). I'd use a table which holds values such as "Date XML feed last imported" and if this is not "today" then go and import / scan for a new file and import it.

2. If you have a copy of the database that runs 24/7 (not everyone does, but I had a client who had a copy of the database with a whole series of this sort of task, scheduled to run at different times each day) then you can use the OnTimer event to check once a minute "Are there tasks to run at this time today that haven't already run" and one of these tasks could be to import the XML file.

3. You can buy a commercial solution, such as FMS Inc's Microsoft Access Scheduler (Total Visual Agent): https://www.fmsinc.com/MicrosoftAccess/Scheduler.html

Hope this helps!