How to automatically update/append Database Tables based on XML File being updated (1 Viewer)

Hi all,

I have a situation that doesn't seem to match most examples that I have found via a google search and it might be that I am searching for the wrong thing. We are using a piece of 3rd party software which is pretty restrictive in how it can link to other pieces of software like Tableau / PowerBI. Unfortunately it is very good at other stuff and there is no way I can get around having to use this software.

What it can do, is delete the previous month's data file and then create/save a file on the network with whatever filepath we would like. There are various file formats we can choose, but we are thinking XML would be best, but Excel/CSV is also an option.

The Problem I Am Trying To Tackle:

We want to have an database that stores our utilisation details. These details are currently calculated and exported by the 3rd part software every month. I would like to come up with some sort of solution where it automatically updates/appends the data in the database.

The 3rd party software saves the last month's utilisation data on the 7th of each month. Currently the file name is always the same name and the previous month's data is overwritten, but if a better solution needs to, we can change the filename to just be a date identifier e.g May2019.

The Questions:
How would you suggest I go about achieving this? Should I use VBA and Macros? Is there something already built into Access to help do this? (the External Data -> New Data Source -> From File -> XML File does the intent, but is not automatic - note I want the database to automatically append the last month's data)

Happy to discuss and clarify where required. I have attached a Test Database created using the External Data -> New Data Source -> From File -> XML File method to show the desired data structure and I have also attached some fake test data.

You can accomplish what you want with a macro inside Access and windows task scheduler (https://en.wikipedia.org/wiki/Windows_Task_Scheduler). The macro would do all the work and windows task scheduler would trigger the macro to run whenever you wanted.

The 3rd party software saves the last month's utilisation data on the 7th of each month. Currently the file name is always the same name and the previous month's data is overwritten

Click to expand...

That's perfect. Since its always the same file Access can just be pointed at that location and grab whatever's there and do its process. The next step for you is moving your import process to a macro. Once you can click on the macro and it does what you want you will be ready to set up the task scheduler to execute it.

When I initially posted it cut off my reply saying thanks for the suggestion and that I had vaguely heard of Task Scheduler, but I have never used it at all for anything, but it looks very intriguing.

If for example I had the database on a shared network drive, is it possible to use Task Scheduler to run without a user needing to be logged in / Access open on anyone's computer so long as the shared network drive is up an running?

Yes. Just set up a task on a computer and it can run as long as that computer is on.

We had so many of these processes we used an old system, threw it in a corner and had one daily task be an email from the system saying everything was fine with it. If we didn't get that email, it was time to check that computer.