Automate Queries? 5

[rpangel]

hello everyone,

i was wondering if anyone knew if it is possible to automate queries in MS Access to run daily and create a report?

thanks in advance,
angel

 

[pjm]

You could put code in a form's on timer event to have it run your query but then you'll have to leave the database running all the time. Another way would be to use the task scheduler in windows and have it run up your db with an autoexec macro to run your query and then quit the database.

Less problems with open files and backups that way...

HTH
pjm

 

[zevw]

From The Automatic Scheduler set the time you want it to run daily. create a moacro that opens the report and then put this command in command line in Automatic Scheduler as follows "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "[DatabaseName].mdb" /x[macroname]

 

[rpangel]

thanks, but where is the "task scheduler" or "The Automatic Scheduler" located?

 

[JoeMiller]

The Task Scheduler (and Auto Scheduler, same thing) come with Windows 98 and up. If you go into Control Panel and click Sheduled Tasks you'll find the program they are talking about.

HTH Joe Miller
joe.miller@flotech.net

 

[rpangel]

i've never used them before.

 

[JoeMiller]

Well here's your chance! The help system will tell you all about them. All it does is launch a specified program at a specified time if the computer is turned on. Many people use it to backup their system at a predetermined time, or run scandisk, etc... You'll use it to launch your db that runs the query and creates the report at the specified time. Joe Miller
joe.miller@flotech.net

 

[rpangel]

i don't know if i'm just not looking in the right place but i looked in the control panel for Scheduled Tasks but i still cannot find it. i'm on an NT workstation, could it be possible that it isn't installed on my computer?

 

[JoeMiller]

Ahhh.. Windows NT doesn't have Task Scheduler. Only Windows 98, 98Se, Millenium & 2000 have it. However if you have the Windows NT Resource Kit there is a program that allows you to schedule tasks. If you don't have the Resource Kit, you might check with your IT dept as they most likely do have it. Or you could search the internet for a scheduling program, there are some out there that fill the gap for Windows systems that don't have the MS task scheduler.



Joe Miller
joe.miller@flotech.net

 

[THoey]

Joe and Rp,

On NT, it is called the AT command and is setup to run from the command line. I have heard of problems running this though. Might as well give it a try...

Hope that helps... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[pjm]

In NT the Scheduled Tasks folder is actually installed as part of the Internet Explorer "Desktop Update", and will read in any jobs you already had set using "At".

pjm

 

[rpangel]

well i have 5 different queries that i want to run in Access. the first four do all the updating and computing and the last query selects the values that i use to create the report. is there something i would have to do in Access to run the queries in a specific order? like a macro? and if so how do i do that? and i understand that in order to run a macro or something similar to that Access has to be open. i wanted to do that and then do an AT command to open Access at a specific time in the morning and when Access opened the queries automatically ran and created the report. does any one think this is feasible? will the "desktop update" do the job?

 

[pjm]

rpangel,

Just enter your queries in the macro in the order you want them run. e.g. openquery 1, openquery 2, etc, then openreport yourreportname, then quit as the last line.

You can either use AT or the task scheduler that comes in the IE4 desktop update for NT and is much easier to use. Just specify the full path and filename of your database as the program to be run. e.g. c:\etc\mydb.mdb

I have a database that runs every day to analyse and print our incoming emails and it works fine.

HTH,
pjm

 

[rpangel]

thanks pjm, but where you have openquery1, openquery2, etc. would that be the actual names of my queries and am i supposed to type those names in under the Action column in the Macro design screen? i'm sorry if i'm being too clueless, but i've never used macros before, let alone in Access.

 

[rpangel]

the macro won't let me type anything in. i have to pick from a list and i don't see an openquery or openreport option.

 

[rpangel]

never mind, i think i figured it out with your help of course. i can't thank you enough but i have one more question. is there someway to turn the notification off so that it won't ask if i want to update or run or , etc. after each query is opened? i was hoping that it would run without asking me yes or no after each query.

 

[rpangel]

guess what i figured that out as well, one last question, i promise this time, ormaybe i shouldn't.... but how can i have it so that when i do the AT command to open Access, the macro automatically runs. is it enough if i just put my database name at the end of the path? will it know to find the macro and run it or do i have to do something else?

THANK YOU AGAIN

 

[robmkimmons]

rpangel,

To automatically execute a macro when Access opens, name the Macro "Autoexec" and whenever that DB is opened it will automatically run it. (if you can't change the name of the macro you want to run b/c it is being used elsewhere as well then create a new autoexec macro that calls your other macro)

~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[rpangel]

thanks, but do you know what i should type in the command prompt for the AT command? i want the report to be created everyday at 9:30 a.m. i have the macro figured out now, i just can't seem to figure out the correct syntax for the AT command to make it do what i want above. any suggestions?

 

[pjm]

rpangel

Looks like you are nearly there! Type AT /? at the command prompt and you'll see the syntax. You need something like

AT 09:30 /every MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY "c:\mydbpath\mydb.mdb"

Don't forget to make sure the task schedule service is running.

(Hope this word wraps ok...)

Good luck.
pjm