# schedule an access macro to run automatically once a day



## CATHYGO (Jul 28, 2005)

I am setting up a daily to do list that runs of my appointment diary in access. I have set up a macro to mail me the next day's schedule in snapshot. what I need to do is to automate this macro so that it runs at 4pm every afternoon without any input from me as this schedule must also go to other partners. I have used windows scheduler and can open the database on time, but am flummoxed when it comes to getting the macro to run. I do not want to use autoexec as I open and close the database several times during the day. Any bright ideas?? Thanks


----------



## OBP (Mar 8, 2005)

I would use "autoexec" to run another macro or piece of VBA that checks the system time. If it is 4.00 to 4.10 then run the email Macro, if it is not that time period do nothing.


----------



## CATHYGO (Jul 28, 2005)

Ok - but I am a bit handicapped in that department! Please can you give more detailed instruction! In the meantime what I did was create a new database and linked the relevant table and put the macro in that as autoexec then set the scheduler to open the new database. A bit Heath Robinson but seems to work...


----------



## OBP (Mar 8, 2005)

Cathygo, please find the attached zipped database called timer1.1.
This database has two methods of timing your email.
The first uses an Autoexec Macro to open a form in "hidden" mode which has some VBA in it's "On Current" event procedure that tells you the system time with a msgbox, checks that the system time is between 14:00 and 16:45 and then calls a Macro (macro1) which has a messag box saying "time". This is obviously adjustable.
The second method is a Macro (Macro2) that calls a function which calls some VBA code in Module1.
The messages are not necessary, they are just there to demonstrate that it is working your email sending can either go in Macro1 or in the VBA itself.


----------



## CATHYGO (Jul 28, 2005)

thanks - think I've got it.


----------



## OBP (Mar 8, 2005)

If you need any help just shout.


----------



## EAG (Feb 26, 2008)

CATHYGO or OBP,

Please forgive me for being so dense, but I'm not following what I need to do to have a macro run on a scheduler.

Your help would truly be appreciated.

Thanks.

p.s. I've set up a macro that runs a series of queries before exporting a text file. I want to be able to put the macro on a scheduler.


----------



## slurpee55 (Oct 20, 2004)

Hi and welcome to the forum!
In the future, please don't piggyback on an old thread - this one is from 2005, after all. It's much better to start your own thread, even if another one that is current is similar to yours.
As for what OBP did earlier, he built a form and in the design view, went to view code (okay, he did this another way, but not worth the trouble explaining).
The form, which he called Form 1, had the following code:

Private Sub Form_Current()

MsgBox Time
If Time > "14:20:00" And Time < "16:45:00" Then
DoCmd.RunMacro "macro1"
DoCmd.Close acMacro, "macro1"
Text0 = Time
End If
End Sub

What it does is, if the time is between 2:20 and 2:45 in the afternoon, it runs a macro called macro1.
Since you have a macro already, just build a new form, change the name to what your macro is called and the time to when you want it (use a 24 hour clock).
In the current database, if you open the form at any other time, nothing happens, but in between those two times, clicking on the form brings up the macro with the time of day in it.


----------



## OBP (Mar 8, 2005)

To expand on that, the Scheduler only Opens the Access database, it does not run the Macro. access does that when it is opened.


----------



## slurpee55 (Oct 20, 2004)

Good to have you here -please explain in more detail exactly what to do, if you think the OP needs it.


----------



## cristobal03 (Aug 5, 2005)

It's a combination of methodologies. To completely automate the job, you use a batch or VBScript routine to open the file. Within the file, you have an autostart or autoexec macro/routine that checks the system time. If it's the correct time window, fire the routine that needs to be scheduled. Then use one of a number of different mechanisms to close the file if no active user is present. (Probably the best way to do that last part is to have a MsgBox and a Timer; if nobody clicks OK on the MsgBox within, say, 60 seconds, close the file.)

Since it's an implementation that utilizes both internal and external mechanisms, it's extremely important to document all this somewhere for a solid legacy.

chris.

[edit]
A batch job/VBScript that is executed by the Windows Scheduler, I should say.
[/edit]


----------



## slurpee55 (Oct 20, 2004)

Thanks Chris - as you can guess, I am just shooting in the dark...


----------



## Rollin_Again (Sep 4, 2003)

You can also use the built in Windows task scheduler in the control panel to call a script to open the DB and run the macro or have the macro run automatically when the DB is opened.

Regards,
Rollin


----------



## Zack Barresse (Jul 25, 2004)

I'm not sure about Access, but with Excel it is generally better (if you have WSH - Windows Scripting Host) to run the scheduler rather than run an Excel-based VBA sub routine, which is ultimately problematic. Take a look at this thread (here: http://forums.techguy.org/business-applications/636786-set-email-reminder-base-excel.html) showing how to do something like this in Excel via VBS.

HTH


----------

