ArticlesMarch 3, 2014
A Macro is perfect for automating tasks in your MS Access reports, forms and controls. They can save time by performing actions such as starting reports or issuing clicks. To schedule macros in MS Access requires a certain level of knowledge of Access and maybe a bit of programming knowledge. Let’s look at 3 ways a macro could be scheduled in MS Access:
1. Schedule a Macro from within Access. This method is relatively clean and easy. Simple create a form that automatically starts with Access. Add a bit of code to your OnTimer event that details the schedule for your Macro, now Access will automatically run the Macro as desired. There is a big downside to this option. Access needs to be open all of the time as well as the form. In many cases users need Access to quit when the macro has completed, or the user is not allowed tinker with the Access system directly.
2. Use combination of command line switches and the Windows Task Scheduler. Users can schedule macros in MS Access externally as well. Knowing the path to your Access executable and the path to your database, create a scheduled task in Windows that passes the command line macro switch into Access. Windows will automatically trigger your macro based on the commands provided.
3. Use a 3rd party program to schedule macros in MS Access. Creating a shortcut to your macro and using a scheduling tool to trigger is one way to use a 3rd party application. There are dedicated tools that handle macros as well. MARS is a MS Access scheduling tool designed specifically for Access automation. Simply open MARS, and create a task to “schedule a macro.” Select the desired database, browse to your macro, and set the schedule. Besides simplicity, 3rd party programs offer automated exception handling and tracking for macros. With 3rd Party tools such as MARS, macros, reports, and queries can easily be managed and scheduled from a single interface across multiple Access databases.
There isn’t necessarily a “right” way to schedule macros in MS Access. It largely depends on your knowledge of access, the number of macros that need management, and how robust the scheduling must be. If the macro is a small process that is a miniscule part of the business, then the “DIY” approach is the best way. If you are looking for a way of centrally managing numerous macros and other Access functions across multiple databases then a 3rd party tool would be the most prudent (and sane) approach.
A good source to learn how to schedule MS Access macros can be found here: http://social.msdn.microsoft.com/Forums/zh/accessdev/threads
To learn more about MARS and other 3rd party MS Access schedulers visit: http://www.christiansteven.com/solutions/mars/
Republished by Blog Post Promoter
ChristianSteven Software Deepens Global Reach Through New South American Initiative
Automated Reporting For SQL Server – What’s the Point?
What is Business Automation?
Using SQL-RD to send SSRS reports to SharePoint
SQL Server 2012: How to Improve Business Information with SQL Server reporting and SQL Report Tools
Merge SSRS Report Output With SQL-RD’s Package Schedule
Using SQL-RD to upload/download files from a database (BLOB)
3 Ways SQL Report Scheduling Improves Cash Flow
How to use MARS to Automate Access queries, Access Macros, and Access Reports
Dedicated Access Reports Schedulers VS Windows Task Scheduler for Access Which works best?