• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

Scheduling a Macro to run at a specific time of the day

I have a Macro which runs a function in my database.  I needed some module or trigger to automatically call or run this macro every day Monday - Friday at 4:00 PM.  How can I do that?
Thanks
0
charming
Asked:
charming
  • 6
  • 5
  • 5
  • +1
2 Solutions
 
harfangCommented:
Use the task manager.
You can create a shortcut there that will open the database and run the macro, which should end with "quit access" of course.
0
 
BillystyxCommented:
set the timer event of the main form to 1000 and on the ontimer event of that form put
if time="16:00" Then
'runmacro
endif

Billystyx
0
 
harfangCommented:
The full shorcut could be:

"C:\Program Files\Microsoft Office 97\Office\msaccess.exe" "C:\Boulot\Application.mdb" /x "macMondays"

It can also contain switches for a specifc workgroup information file, user name, password, etc.

Good Luck
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BillystyxCommented:
actually this might be better - to make sure you catch it:

If time > "16:00" And time < "16:01" Then
'run macro
End If
0
 
harfangCommented:
Actually, Billystyx, you are comparing a date/time with a string. Can you tell me whether the > will be treated alphabetically or numerically?
[pay attention, this question will be part of the exam :) ]

If you do want to use the Time() function, use this:

    If Time > #4:00:00 PM# And Time < #4:01:00 PM# Then

We are allowed to -type- it as "> #16:00#", but VB will convert it to more familiar US time format...

This being said, I still think this is a clear case for the task scheduler... :)

Cheers
0
 
BillystyxCommented:
it does actually work - I tested it first before posting:)

Billystyx
0
 
charmingAuthor Commented:
This works but the Timer Interval which i set to 1000 must not be high enough because it ran twice within the minute.  Also is there some way to check that the date is between Monday and Friday, perhaps by grabbing the weekday number off of the Current date?  Any ideas?

Here is the code
Private Sub Form_Timer()
If Time > #12:08:00 PM# And Time < #12:09:00 PM# Then
DoCmd.RunMacro "Export_CSV_timer"
End If
End Sub
0
 
charmingAuthor Commented:
As far as the Task Scheduler is concerned I have never used it.  Where do I find it and how does that work
0
 
harfangCommented:
The task manager is a part of windows. In Win2000, for example, you will find it under:
   Start / Programs / Accessories / System Tools / Scheduled Tasks
But there are often faster ways to access that list depending on the verision of Windows.

Any shortcut placed there will be run at the requested time and date, with many options.
This will work whether you have the application open or not, as databases are multi-user, so that you can open the same database twice from the same machine, unless you open it in "exclusive" mode, of course.

Good Luck!
0
 
ArjiCommented:
Set your timer interval to 30000 where each increment is 1 mS.  30000 = 30 minutes, etc
0
 
BillystyxCommented:

as arji says, set timer higher :

Private Sub Form_Timer()
myweekday = Weekday(Date, vbMonday)
myname = WeekdayName(myweekday, , 2)
select case myname
case "Saturday"
'do nothing
case "Sunday"
'donothing
Case Else    ' Other values.
  If Time > #12:08:00 PM# And Time < #12:09:00 PM# and Then
DoCmd.RunMacro "Export_CSV_timer"
End If
End Select

End Sub

Billystyx
0
 
charmingAuthor Commented:
billystyx that will work, but I am still trying to figure out how to do this in the Windows Task Scheduler.  Could I run your code in the Task Scheduler and ignore the Switchboard Event?
0
 
charmingAuthor Commented:
I am in the Task Scheduler I see

Run C:\PROGRA~1\MICROS~3\Office\MSACCESS.EXE

Start In C:\PROGRA~1\MICROS~3\Office

Where or how do i execute this code?
0
 
charmingAuthor Commented:
My Database name and path is C:\ACH_Development.mdb

Function name is Function ExportCSV()
0
 
BillystyxCommented:
Not sure - I haven't used it - sorry:)

Billystyx


0
 
charmingAuthor Commented:
Also how do you Quit the app after task runs?
0
 
harfangCommented:
Well.

Assuming you have already created a task in the task scheduler (the "create new task" wizard is not too smart anyway), follow these steps.

* find and select the task
* right click and select "properties" from the context menu
* in the first tab, "Task", enter the command line (see below)
* in the following tabs, enter the desired settings (weekly)

The full command line will be:

"C:\PROGRA~1\MICROS~3\Office\MSACCESS.EXE" "C:\ACH_Development.mdb" /x macScheduler

This will start access, open the ACH database and try to run a macro called "macScheduler"

To create this macro, click "new" on the macro tab and enter two commands:
1) run code "ExportCSV()" (i think it needs to be a function, but i'm not sure)
2) quit (quits access)

Test the macro and wait for the next scheduled event to check it worked.

Good Luck
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now