Automating Queries

[jcros]

Im looking to schedule/automate a query I have built. For example, I'd like for it to automatically run during the morning on the 1st of every month. And, export to a network drive if feasible. Any input?

Thanks.

 

[dhookom]

Find a Window's task scheduler program that can open your application. You can add command line parameters to run a macro that could perform your task.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[jcros]

Ive created the macro and it exports to the network path I built into it. But Im stuck on the scheduling of it. I see Scheduled Tasks as a Program of mine (Windows XP). How do I 'connect' the two - the scheduler and the db? I believe that's what you're saying regarding the command line parameters. And that's what I don't understand at this point.

 

[jsteph]

jcros,
What I do is copy the path to MSAccess.exe into the "Run" section of the task.

You can get this by either using the Task Wizard, which has Access as one of it's options, or manually make a shortcut and use that. What's bad is that MS does *not* use 'real' shortcuts anymore, so you can't go to your desktop or menu shortcut for Access and right-click, and find the path in Properties. But usually it's in C:\Program files\Microsoft office\Office## where ## is 11 for 2003, Office10 for 2002, and so on. I'm not sure if Access 2007 is Office12 but I'd assume so.

Then paste the path to your .mdb following the Msaccess.exe, separated by a space. Enclose both separate paths in quotes if there are any spaces in the path or dbnames, (as there will be in Program Files).

Then optionally, after the path to the actual .mdb, put /cmd "Some message" This will be a string you can fetch in the program using the command() function. You can control what it does by making multiple tasks with the same mdb, but different /cmd options.

Further, I'd suggest against a macro, since you can't trap errors that way. I think there's a command line that will allow the execution of a function, but I just use a dummy form in the Startup form (startup options menu), and call a function from the Open event.
--Jim

 

[JerryKlmns]

Jim

You can olny run macros from command line arguments.But if a macro calls a function which has error trapping (and a lot of VBA) then it is ok all the way.

---------------------
Happy New Year to All

 

[mp9]

If it's not too late, take a look at the Handy Access Launcher which, amongst other things, can schedule macro runs at predefined times/intervals. Make sure the macro that runs your query has a 'quit' action at the end and HAL will start you db and run the macro which will then, in turn, run your query and close Access.