Automating Append Query via SQL and Scheduled Tasks (1 Viewer)

I have a database that is importing information from txt files using an import macro tied to a scheduled task. This is working perfectly fine.

However, what I then need to do is run an Append query to get some of that data that is imported into another table for export. The issue I am having is that if I have the RunQuery in a macro the scheduled task (using the /x switch) does not run, it just hangs.

I had a similar issue with getting the imports to run, I resolved that by changing the imports to a pure SQL statement and it all now runs fine. What I am struggling to find is a way to run the query from a SQL statement as all the INSERT INTO information that I find seems to be wanting to move data from a table into a table rather than running a query and submitting that data into an empty table which is what I am doing here.

The reason for this is that we need the import and export of data to be run automatically without any manual intervention. We cannot do this natively within one system hence the import and export.

Help me Obi-wan Kenobi!

I've done complex import routines from 30 csv files each night for many years using scheduled tasks.
The routines include both SQL statements and queries together with logging data changes and compiling a text info log file which is then automatically emailed to the program administrator.

The main routine is run by a standalone app but I have other routines that are run from a macro created just for that purpose. None of these require user intervention.

So as you can see, what you want is definitely achievable. Does your code work if run manually?

Yes, the macro that I have works perfectly fine within an Access environment, it's just when we run the scheduled task that it sits there indefinitely "running"

Try breaking it down to run each section individually and hopefully identify the problem(s).
Also check your scheduled task settings. Maybe try another simple scheduled task with a straightforward procedure to run

As I was explaining, my routines are very complex involving many sub procedures, use of temporary tables to process the raw data before finalising the import and thousands of lines of code. Typically the process takes 30-45 minutes on a network in the middle of the night and involving both SQL Server and Access datafiles.

I realise telling you all that doesn't provide a magic wand to solve your issues.

It's definitely the Append Query that stops it. Everything else runs fine through the Task Scheduler. I just have no idea why it doesn't run through Task Scheduler, when the macro itself works fine in Access.

After a lot of wailing and gnashing to teeth I've got passed the problem I was having.

OK. Apart from wailing and gnashing, what else did you do that fixed that issue?

Split the macro down into several smaller operations and then did the final file copy to a network drive via batch file.

Definitely the right approach. Divide and conquer!

As things can and will go wrong occasionally due to network issues etc, I do recommend using a log file created as the routine runs.
Attached is an example from one of my apps. A start/end time is displayed for each procedure and where there is a problem a message is shown. I've deliberately used an example with one error

Hope that helps