Windows Scheduler

[Roger Converse]

Hello,

Has anyone ever used windows scheduler to launch a routine witin Access. I
have a routine, that runs on an "on click" event. I would like to have this
routine kick off at 4:00 a.m. every morning. Through the schedule wizard, I
have been able to get my application launched, but I can't trigger the event
so nothing happens. I do not want to change this to run when the DB is
opened, because I do not want this routine to run every time the DB is
launched. Hope this makes sense.

Any suggestions would be appreciated.

Thank you,
Roger

 

[Arvin Meyer [MVP]]

One of my clients used the Windows 98 scheduler to download a SQL-Server
file every night at 12:30 AM. I've used AT commands to have a server
automatically FTP a file to another server..

To trigger an event upon startup, all you need is an autoexec macro with a
RunCode action, or better yet a startup form with code on the Load, Open, or
Current event.

 

[Dale Fye]

Roger,

If you have a main form (maybe a splash screen, or something like that),
then you can set the forms TimerInterval to some value other than zero (1000
= 1 sec), but start out setting it to something like 10 or 1000, to get the
Timer event code to run almost immediately after loading the form. Then you
can use the forms Timer event to check to see what the current time is, and
at the appropriate time, run your code. If you are going to continue to use
the Windows scheduler to open your application, then you could also add code
to the Timer event to close the database as well. It might look something
like:

Private Sub Form_Timer()

Static dtPrevious As Date
Static dtCurrent As Date
Static tmPrevious As Date
Static tmCurrent As Date
Dim tmRunAt As Date

If Me.TimerInterval < 1000 Then
dtPrevious = Date - 1
tmPrevious = TimeValue("12:00:00 AM")
Me.TimerInterval = CLng(1) * CLng(60) * CLng(1000)
End If

tmRunAt = CDbl(0.1666666667) 'converts to 4:00:00 AM

If dtCurrent <> dtPrevious Then
dtCurrent = Date
tmPrevious = TimeValue("12:00:00 AM")
End If

If dtCurrent <> Nz(dtPrevious) Then
tmCurrent = TimeValue(Now())
If Nz(tmPrevious, 0) < tmRunAt And tmCurrent > tmRunAt Then
MsgBox "run your code here"
Docmd.quit
End If
End If

End Sub

I'm sure there is something a little more elegant than this, but it should
do the job for you.

HTH
Dale

 

[Pat Hartman]

I don't use an autoexec macro or startup form to do this because that runs
every time the database is opened. I use the /x argument which allows you
to pass in the name of a specific macro. Look up "Startup command-line
options" in help for details on arguments you can pass when you open the
database. In the macro, don't forget to close the database as the last
step.

I also had run the app from within a .bat file to get it to work properly.
Of course that was three versions ago and a different operating system so
that may no longer be necessary.

 

[Albert D. Kallal]

I explain in detail how to use the windows scheduler and windows scripting
to
CALL a routine *inside* of your application.

The windows script can easily be placed in the windows scheduler.

The following article of mine should help you:

Batch processing in ms-access (how to run ms-access as a batch job).
By Albert D. Kallal
Friday, April 27, 2007
http://www.members.shaw.ca/AlbertKallal/BatchJobs/Index.html

 

[Roger Converse]

Hello,

Thank you very much for the reply.

This is exactly what I am trying to accomplish. I have gotten the lvbs file
to the point where when I double click on the file icon, the event runs.
What I can't figure out is now how to schedule this using the schedule wizard
(which seems to be the only way I can create a new scheduled task). When the
wizard comes up, I choose Access as the application. Access is opened, but
the process isn't triggered. Am I doing something incorrect? How do I get
this scheduled to read my .vbs file and then run the code inside the script?

Thank you,
Roger

 

[Pat Hartman]

You don't select Access as the application since you are opening a script
file. Choose Scripting Runtime if that is an option. I haven't done this
in a while so I don't know what your options are.

 

[Albert D. Kallal]

Just create your "vbs" script, and use that for the scheduler.

eg:

from windows go:

start->control panel

select the scheduled tasks icon.

just click on a add scheduled task..and choose your script that you made.
You don't need to do anything more. when you click on the schedule task
wizard, simply hit the browse button...and browse to your script. If you
don't have windows logon enabled, then entering a password and logon will
not work.

I suggest you simply "check" the box

[x] run only if logged on

The above thus assumes the machine is left on, and running. Only uncheck the
above check box if you acutally are running this on a box that prompts for
the windows logon, and also will be logged off.

You of couse will "test" the sciprt by dboule cliking on it..and ensureing
it works. Once that script is working the way you want, simply use the "add
schidled task" in the above contorl panel option..and you are home free.

 

[Roger Converse]

Got it!

Thank you very much. Great article, by the way. Very good stuff!

 

[Arvin Meyer [MVP]]

I almost always use a startup form. While the code runs every time the form
opens, you can use a recordset to make an entry in a table to tell the code
not to run again in that branch for a certain period of time. The scheduler
program can also be useful to run batch files that don't require starting
Access, such as ftp'ing a database early in the morning to another server. I
basically use Access to run database centric code, and VB or scripting (or
..bat files) to run non-database centric procedures.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

I almost always use a startup form. While the code runs every time the form
opens, you can use a recordset to make an entry in a table to tell the code
not to run again in that branch for a certain period of time. The scheduler
program can also be useful to run batch files that don't require starting
Access, such as ftp'ing a database early in the morning to another server. I
basically use Access to run database centric code, and VB or scripting (or
..bat files) to run non-database centric procedures.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Secret Squirrel]

Hi Albert,

I was reading your posts and I'm looking to do the same thing but I need to
run a macro from within my DB and then have the DB close. Would I put the
name of the macro where you have "accessApp.Run "MacroNameHere"? Also what if
my DB is secure. How would I have your script use a logon name and password
to access the DB?

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("C:\some path name\someMdb.mdb")

accessApp.Run "TimeUpDate"
accessApp.Quit
set accessApp = nothing