Scheduled Task to Run Macros

[Will James]

Hi All,

I am wondering if there is another way to run macros at a scheduled time?

Current I have setup Windows Scheduled Tasks but each time they run they open the database and then close it. But I want to be able to leave the database open and still have the macros run.

I have the following Macros:

Run_Weekly - which appends into another table and then deletes the main table and prints
Run_Monthly - which does the same as above but deletes the Weekly Table
Run_Yearly - which prints the yearly report and deletes the data in the yearly table

is it possible for this to be done via VBA? I'm just not sure who I specify to run once a week, month and year.

There might not be an easy answer but any help would appreciated.

Thank You

[NeoPa]

Just to be clear here Will, you're talking about a database which is literally left running all through the year yes? 24/7 52 weeks in the year?

Just to start the ball rolling, Invoking a Database From the Command Line can help you with scheduling jobs from the O/S. If it really is required from a running database just confirm the answers to the questions above and we can look into it for you. There is certainly a way to do it.

[Will James]

Hey NeoPa,

Ideally yes, I mean the computer is set to sleep in the evening, but if it needs to remain open and running for me to able to to schedule macros to run via VBA then I can do that, I have created a clock-in, clock-out database where our customers can sign themselves in and out. At the mo windows scheduler is doing the work, so its set to close the database at 18:00 so the macros can run as its has to open the database, the macros don't seem to run when the database is open!

is it a problem for the database to run 24/7? If you have a better Idea I'm open to suggestions :)

[NeoPa]

Give me a few minutes to gather my thoughts. Certainly scheduling can be done from within Access too, as long as the form managing it stays open (which is easy enough to ensure). I would suggest this form close the database whenever it detects itself (the form) being closed. That way you will always know whether or not the code is active. Alternatively, for full confidence, a flash of something should occur every n seconds to notify the operator that all is still active if that's required. Forms can stay visible even after the project has been reset. Let me know if this is an issue while I make preparations to explain how it all should work.

[Will James]

I have a 'Welcome Screen' form that remains open, with the options to sign in and sign out. that form can't be closed unless a pin number is entered to execute the form close command. I have disabled the right click and shortcut menu along with the close button.

on the welcome form I have a timer event that checks the time and if its between 17:00 and 17:06 it will run the end of day, which appends the Daily table into the Weekly table and then prints the daily report.

But currently as the windows scheduler runs that macros I have to set the database to close at 18:00 to enable the macros to run!

I have also disabled the ALT + F4 keyboard shortcut to prevent people being able to close the application.

I think I have just repeated myself there!

[NeoPa]

You already have the Timer process triggering some processes. Where is your difficulty exactly with getting them all triggered in the same way?

[Will James]

I am having trouble specifying each macro to fire at a specific time, like once a week, once a month and one a year.

I'm not even sure if thats possible!

[NeoPa]

I guess you have the timer checking a specific, hard-coded, time period. What I would suggest is a table with multiple records (one for each trigger point) that is processed by the timer code. Due to the latency of checking the data this should be somewhat less frequent than once every second certainly. Only you can decide the best balance based on what you know is required, but for daily / weekly / monthly / yearly processes I'd start that at around ten minutes and work from there.

Also due to latency, I would work on the basis of disabling the timer while any particular process is under way. As soon as it finishes the Timer should be re-enabled.

Table = [tblSchedule]

Field             Type        Index  Comment
jobID             AutoNumber    PK
jobName           String
jobFreq           String             For description only
jobProc           String             Name of valid procedure with available scope
jobParams         String             Parameters, if required, for jobProc
jobNext           DateTime           Absolute time of next run
jobInterval       String             To match 1st param of DateAdd()
jobIntCount       Numeric            To match 2nd param of DateAdd()
jobFromNext       Boolean            True means Add to Next
                                     False means add to Now() after process

You can trigger the process by using the Run() method and passing jobProc and jobParams as parameters.

Does this all start to fit into place now for you?

[Will James]

Yes thank you! I will give it a go

[NeoPa]

I suppose some dummy data for that table might help you to form a picture of what's required (It's a bit wide so I've fitted it on two lines) :

Table = [tblSchedule]

jobName             jobProc           jobNext       jobIntCount
       jobFreq            jobParams        jobInterval       jobFromNext
Management Reports  fMgmtRpts    #12/12/2011 08:00#      1
       Weekly                Null               w                 True
Yearly Reports      fYlyRpts     #01/01/2012 08:00#      1
       Yearly                Null              yyyy               True
Usage Log           fUsageLog    #12/10/2011 13:20#      10
       Ten mins              Null               n                 False

I've also assumed dates in USA format for you. This is only some example data, to illustrate some of the options you could use.

[ADezii]

I have not read the entire Thread in detail, so forgive me if I oversimplify, but couldn't the entire Scheduling Issue be resolved by simply calculating from a Base Date? To Execute a Code Segment at specific Intervals starting from #11/10/2011# (30 Days today) would require the following Logic:

Dim dteBaseDate As Date
 
dteBaseDate = #11/10/2011#
 
If (DateDiff("d", dteBaseDate, Date) Mod 7) = 0 Then
  '1 Week Interval has happened
End If
 
If (DateDiff("d", dteBaseDate, Date) Mod 30) = 0 Then
  '30 Day Interval has happened
End If
 
If (DateDiff("d", dteBaseDate, Date) Mod 365) = 0 Then
  '1 Year Interval has happened
End If

P.S. - Again, forgive me if I oversimplify, or am off on another Tangent which happens quite frequently! (LOL).

[NeoPa]

From within the timer procedure that code could work. It's hard-coded though, and inflexible. I would suggest a data-based solution would probably be required (as the OP's database is already somewhat sophisticated already). It could work though :-)