By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,215 Members | 1,517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,215 IT Pros & Developers. It's quick & easy.

Scheduled Task to Run Macros

P: 11
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
Dec 10 '11 #1

✓ answered by 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]
Expand|Select|Wrap|Line Numbers
  1. Field             Type        Index  Comment
  2. jobID             AutoNumber    PK
  3. jobName           String
  4. jobFreq           String             For description only
  5. jobProc           String             Name of valid procedure with available scope
  6. jobParams         String             Parameters, if required, for jobProc
  7. jobNext           DateTime           Absolute time of next run
  8. jobInterval       String             To match 1st param of DateAdd()
  9. jobIntCount       Numeric            To match 2nd param of DateAdd()
  10. jobFromNext       Boolean            True means Add to Next
  11.                                      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?

Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,707
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.
Dec 10 '11 #2

P: 11
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 :)
Dec 10 '11 #3

NeoPa
Expert Mod 15k+
P: 31,707
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.
Dec 10 '11 #4

P: 11
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!
Dec 10 '11 #5

NeoPa
Expert Mod 15k+
P: 31,707
You already have the Timer process triggering some processes. Where is your difficulty exactly with getting them all triggered in the same way?
Dec 10 '11 #6

P: 11
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!
Dec 10 '11 #7

NeoPa
Expert Mod 15k+
P: 31,707
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]
Expand|Select|Wrap|Line Numbers
  1. Field             Type        Index  Comment
  2. jobID             AutoNumber    PK
  3. jobName           String
  4. jobFreq           String             For description only
  5. jobProc           String             Name of valid procedure with available scope
  6. jobParams         String             Parameters, if required, for jobProc
  7. jobNext           DateTime           Absolute time of next run
  8. jobInterval       String             To match 1st param of DateAdd()
  9. jobIntCount       Numeric            To match 2nd param of DateAdd()
  10. jobFromNext       Boolean            True means Add to Next
  11.                                      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?
Dec 10 '11 #8

P: 11
Yes thank you! I will give it a go
Dec 10 '11 #9

NeoPa
Expert Mod 15k+
P: 31,707
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]
Expand|Select|Wrap|Line Numbers
  1. jobName             jobProc           jobNext       jobIntCount
  2.        jobFreq            jobParams        jobInterval       jobFromNext
  3. Management Reports  fMgmtRpts    #12/12/2011 08:00#      1
  4.        Weekly                Null               w                 True
  5. Yearly Reports      fYlyRpts     #01/01/2012 08:00#      1
  6.        Yearly                Null              yyyy               True
  7. Usage Log           fUsageLog    #12/10/2011 13:20#      10
  8.        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.
Dec 10 '11 #10

ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. Dim dteBaseDate As Date
  2.  
  3. dteBaseDate = #11/10/2011#
  4.  
  5. If (DateDiff("d", dteBaseDate, Date) Mod 7) = 0 Then
  6.   '1 Week Interval has happened
  7. End If
  8.  
  9. If (DateDiff("d", dteBaseDate, Date) Mod 30) = 0 Then
  10.   '30 Day Interval has happened
  11. End If
  12.  
  13. If (DateDiff("d", dteBaseDate, Date) Mod 365) = 0 Then
  14.   '1 Year Interval has happened
  15. End If
P.S. - Again, forgive me if I oversimplify, or am off on another Tangent which happens quite frequently! (LOL).
Dec 10 '11 #11

NeoPa
Expert Mod 15k+
P: 31,707
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 :-)
Dec 10 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.