A more difficult scheduling question

  • Thread starter Thread starter Randy Doan via AccessMonster.com
  • Start date Start date
R

Randy Doan via AccessMonster.com

I am working on a laptop with too much security. The Admin group have
disabled the windows scheduler and do not allow installation of 3rd party
software. I need to schedule a macro to fire at a specific time every day
in access. I've searched the web but every scheduler I see either requires
installation of third party software or the use of windows scheduler. Is
there a way to do this ?
 
You could keep the database open at all times and have a hidden form open --
then use that form's Timer event to check the time and, if the time is the
right time, run your macro.

Or you could have EXCEL or WORD open and running at all times, and have a
macro in them check the time, and if it's the correct time, have the EXCEL
or WORD macro open ACCESS file that has an AutoExec macro in it that runs
your desired macro.

Other than that, no, I'm not aware of any other way to do it except by using
external scheduler program.
 
Ken,

Sounds like either suggestion would work however I'm still rather new to
this stuff, could you walk me through them both, that way if one does not
work for me, I can use the other as a backup. Not only that I've seen allot
of posts asking for something like this on this forum and on others. I'm
probably not the only one this would help out. Thanks in advance!
 
Seeing as this is an ACCESS group, I'll stay with the Timer event on an
ACCESS form.

Create a form in your ACCESS database. Call it frmScheduler. In design view,
open Properties window.

Go to Event tab. Next to Open, select "[Event Procedure]" from dropdown
list. Then click on the three dots at far right side of box. That will take
you to Visual Basic Editor. On the blank line that is shown between the
Form_Open and End Sub lines, type this:

Me.Visible = False

Go back to the form's design view. On the Event tab, go to Timer Interval,
type 1200000 (this is the value for two minutes -- 60000 milliseconds).
(This will make your timer event run every 2 minutes.) Next to Timer, select
"[Event Procedure]" from dropdown list. Then click on the three dots at far
right side of box. On the blank line that is shown between the Form_Timer
and End Sub lines, type this (replace my generic names with your real
names):

If Abs(DateDiff("n", Time(), #TimeWhenSubNeedsToRun#) <=2 Then
Me.TimerInterval = 0
Call NameOfTheSubroutineThatNeedsToRun
Me.TimerInterval = 120000
End If

Save and close the form.

Go to database window and select Tools | Startup. In the Display Form/Page,
select frmScheduler from the list.

Now, whenever you open this database, the frmScheduler form will be opened
and made invisible. Every two minutes, its Timer event will run and test the
time. At the proper time, the code will run your procedure.


In WORD or EXCEL, you'd have to run code that would do a similar time test
and if correct, open an ACCESS database that has an AutoExec macro that runs
a function that calls the subroutine. That AutoExec macro also then needs to
close the database file. But, I would stick with what I show above; much
fewer moving parts.
 
Only needed to do a little modification to this to get it working in my
app. Thanks Ken!
 
Back
Top