I created an App that consists of 6 Export Dataset queries. They should dump the results into a .mdb database. These work correctly when I run them individually.
When I attempt to schedule this App within Toad to run at a specific time, it does not work properly. The Execution Log will show that they ran at the selected time, but the .mdb does not show the results.
I’m curious if the problem exists within the Action tab of the scheduler. The Action is currently set to ‘Start a program’, which is Toad.exe. Should the 6 queries that the App consists of also be in the Action list?
I figured that since I was right-clicking on the App itself to even get to these scheduling screens, that those 6 queries were part of the task I was scheduling.
Can anyone help?
When the scheduled app runs, are you logged into windows? If not, try scheduling it it while you are logged in. That may make a difference.
The queries are built into the action, so you should not have to specify them in the scheduler. As long as the scheduler is calling Toad with the -a “app name” parameter, it should be running the actions (which, it sounds like it is, since you said you see that they were run in the execution log).
If you set them up as six actions within a single app inside the Automation Designer, you should see [-a “”] when you select the task and then go to “Properties -> Actions -> -> Edit…”. The task is telling Windows to launch Toad and run the application named “”. If you don’t see the parameter in the Task Scheduler’s action properties, that could cause it. However, if you do, then I suspect something is perhaps configured incorrectly in your defined app.
Have you tried right-clicking on the app and selecting “Run” to see if all six access tables get created?
I do see the parameter to open Toad as an Action in Task Scheduler. I just tested out right-clicking on the App and selecting Run–the tables were not updated but all 6 queries are listed in the Execution Log as Completed.
Take a look at the “If file exists” and “if table exists” property in the export dataset actions. Are they set how you want them?
Yes, that part looks right. And when I click them individually they work as I’d like them to and overwrite the existing tables.
Are they all 6 writing to the same mdb/accdb file or do they each have their own?
It’s the same mdb file. They each write to a separate table in that database.
I just tried creating 3 actions that write to the same .mdb file
I definitely see some weirdness regarding source and destination tables. Seems like there is some bug here.
It seems to work OK if I have options set like this:
- If file exists…write to existing file, using table options below.
- “Autmatically detect name” unchecked.
- Manually specificy table name.
- If table exists in file: overwrite.
Another thing that worked for me…if you are exporting entire tables, then just make one action, and on the “dataset” tab, check “export objects” and then add in all of the tables to be exported.
These are my current settings--it looks like they're already set to what you've listed.
Although I don't see a "write to existing file" for the "If file exists:" option. I see 'Create tables in MDB file' or 'Prompt for append/overwrite/abort'
You were able to run an App with these settings and it posted to the .mdb correctly?
Also for my purposes, I don't think the Export Objects for the tables will work. The data I'm exporting to the .mdb is from a select query drawing from multiple tables.
Thanks for all of your help with this.
Yes, I was able to make it work that way. But it sounds like you are using an older version than me.
Which Toad version are you using?
Hm, now with 12.5 (actually 188.8.131.52) I don’t have any problem.
I have 6 export actions, set up with the same options as yours.
All writing to the same mdb file, but each to a different table.
When I right-click the App on the left, and choose “Run”, all 6 tables are created correctly in the one mdb file.
I don’t think it should make any difference, but but MS Access engine that I am using is the 2016 version (64 bit), which I got from here: https://www.microsoft.com/en-us/download/confirmation.aspx?id=50040.