automated overnight queries 3

[nickinbristol]

I have a small access database running on a server used by several operators. I wish to query the database each day after all users have finished their transactions and write the results to a table. Is it possible to automaticly run a query at night once everyone is in bed? If so how is this achieved

 

[scriverb]

Yes, you can do this by monitoring the time of day using the Timer Interval of an always open form in the database. I have done thisby creating a small "hidden" form which opens on my system only. The application is left up and running overnight. The special form is only opened and hidden on the PC where a special logon name is used. This way you only have one form monitoring the time.

The timerinterval property of the form is set to 1 hour or 3,600,000 ms(milliseconds). The code for the OnTimer event would monitor the time and at a pre-determined time during the night execute your running of the report. If you have more than one report or process to run you could get creative and create a table with the reportnames, queries, etc. that need to be executed. As you execute the reports update a field for that report with the date/time to indicate that it is done.

If you have any questions on this process please post back with your concerns.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[4946]

Running unattended ACCESS reports

We are running scheduled batch files for various reports at 30 min., daily, weekly and monthly intervals using this method. It may seem a bit of a kludge, but it does not require ACCESS to be running all the time, nor do you have to worry about what might start running when you manually start ACCESS. When the scheduler calls the application, it will automatically start, run and exit. If you are working with an instance of ACCESS open when the schedule kicks-off, it will just open another instance of ACCESS, then close it when it has finished. It will not interfere with what you have open other than grab some processor time and perhaps the focus.

Step 1.
Create a macro that will run your report using the actions "OutputTo" and "Quit".
When you select the OutputTo action format, file-and-path, and encoding, etc., be sure to set AutoStart to "NO"
When you select the Quit action, choose "EXIT" from its options.

Step 2.
Create a batch file (anyname.bat) with wordpad in the same directory as your database file containing the following information. Be sure that the info on the lines beginning with "path" and "start" does not word-wrap to a second line(turn off word-wrap).

c: (or drive name where your application resides)

path = %PATH%;<drive name>:\windows\command;<drive name>:\<path to your database file's location>;<drive name>:\<path to your msaccess.exe file>

start /wait msaccess.exe "<drive name>:\<path to your database file's location>\<your database file name>" /x "<name of Macro that will run your report>"

exit

Step 3.
Using whichever form of scheduler you have, set it up to call the batch file as often as needed.

You can test the batch file by clicking on it in the windows directory. The scheduler will give you an option to manually test the file as well.

WinN.

 

[MasterLu]

Hi 4946

Thanks so much for the extremely detailed and thorough explaination! I have tried several examples given throughout several other threads with no success. Your example is working perfectly for me!

Thanks again for the explicit instructions - it's exactly what some of us novices need to learn it and get things working!

-Lu

 

[4946]

Hi MasterLu,

Glad this helped out. faq701-5099 has been set up if anyone else needs to be referenced to it outside of this thread.

WinN

 

[scriverb]

There are times when access to the system Scheduler is not available to all on the server. It was my situation when I wrote the access database program to act as a scheduler as I described above. There may be others in that same situation and that option should be explored if necessary.

The scheduler example provided here is excellent and should serve you well.

In the even there are others that do not have access to the system scheduler capabilities or on a standalone system that requires some scheduling capabilities the following links to software that provides for scripting of just above anything you need to do on your system. One is FREE and the other is running at $75. Give these a try.

System Scheduler FREE

http://www.splinterware.com/products/wincron.htm

Macro Scheduler 7.3 $75

http://www.mjtnet.com/

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JerryKlmns]

4946,

I tried to do the same a couple years ago with no luck. The objective was to open a database at 06:30 every working day, run all the daily update procedure (about 20 minutes time) and close. My problem was that the database uses user-level security. How should I modify the code, in order to pass a UserID and Password to open the database and run my macro (the daily update procedure)?
I m aware of showing that Password, opens a backdoor to the security allready set, so think of a UserID with Open Database rights and Open/Run right on the only form that activates the update procedure with no data.
Since then, the first user that arrives at the office, opens that db and presses the button.

Thanks in advance!

 

[4946]

JerryKlmns,

I pieced this procedure together about a year ago from several microsoft knowledge database articles and discussions from this and other forums and news groups. Because I am a technical user, not a programmer, I am not too deeply astute about things outside just those necessary for me to extract the desired data and reports from our databases. That's why I am a very frequent visitor to this great forum!!! Hopefully one of the programmers or other members of the forum who is familiar with the command line execution of macros, etc. can provide some insight and advice on your UserID/Password question.

Regards,
WinN

 

[scriverb]

The Macro Scheduler that I pointed you to above should provide you with the ability to execute your data base at a preset time as well as enter the appropriate user name and passwords. It has a record feature that when you start the process it records all mouse and key movement and entries until you end the macro. It then converts them to script for the program and you then can setup the execution parameters to run the program.

I believe you can get a temporary test version from the site to see if it will do the job for you without buying it. If it works then you can purchase it and you are in business.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

I did some more checking about the Macro Scheduler. After you have the Macro working correctly with the userID and password as part of the script you can protect anyone from seeing this password and userID by encrypting the macro script. To get in and edit the macro where this info is stored you must know the password that you have set for it.

Looks pretty good and has many many more features.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[4946]

Just ran across this recent post of command line options with a couple that reference user and password. thread181-1027471 Haven't had a chance to try them out yet.

WinN