?
Solved

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

Posted on 2005-04-08
17
Medium Priority
?
573 Views
Last Modified: 2012-05-05
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
Comment
Question by:charming
  • 6
  • 5
  • 5
  • +1
17 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 13736950
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
 
LVL 18

Expert Comment

by:Billystyx
ID: 13736990
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
 
LVL 58

Expert Comment

by:harfang
ID: 13736996
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 18

Expert Comment

by:Billystyx
ID: 13737029
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
 
LVL 58

Expert Comment

by:harfang
ID: 13737562
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
 
LVL 18

Expert Comment

by:Billystyx
ID: 13737608
it does actually work - I tested it first before posting:)

Billystyx
0
 

Author Comment

by:charming
ID: 13737792
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
 

Author Comment

by:charming
ID: 13737944
As far as the Task Scheduler is concerned I have never used it.  Where do I find it and how does that work
0
 
LVL 58

Expert Comment

by:harfang
ID: 13739105
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
 
LVL 17

Expert Comment

by:Arji
ID: 13739188
Set your timer interval to 30000 where each increment is 1 mS.  30000 = 30 minutes, etc
0
 
LVL 18

Assisted Solution

by:Billystyx
Billystyx earned 400 total points
ID: 13741116

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
 

Author Comment

by:charming
ID: 13761910
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
 

Author Comment

by:charming
ID: 13762040
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
 

Author Comment

by:charming
ID: 13762072
My Database name and path is C:\ACH_Development.mdb

Function name is Function ExportCSV()
0
 
LVL 18

Expert Comment

by:Billystyx
ID: 13762299
Not sure - I haven't used it - sorry:)

Billystyx


0
 

Author Comment

by:charming
ID: 13762908
Also how do you Quit the app after task runs?
0
 
LVL 58

Accepted Solution

by:
harfang earned 400 total points
ID: 13764308
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question