Access Macro Auto-Run

August 10, 2006 10:04 AM

Hello, I need an Access macro to run every Friday evening, automatically...can anyone help do this easily?

I am using Windows as well.
Thanks

posted by strangelove to Computers & Internet (8 answers total)

Name your macro autoexec. This will cause it to run when the database is opened.

Schedule a task to open the database at the desired time using "Scheduled Tasks" in the windows Control Panel.
posted by SteveInMaine at 10:18 AM on August 10, 2006

Option 1: You could set up the macro to run automatically upon opening the Access database by naming the macro to 'Autoexec', and then use the Windows Task Scheduler to open the Access database file at the desired date/time.

Option 2: If the Access database will be in continual use then you can use a form's TimerInterval property to execute the macro using VBA code. However, this will involve polling the form on a regular basis (depending on the TimerInterval set) to determine if the date/time for execution is correct and therefore it may be easier and less resource-intensive to go with option #1.
posted by mezzanayne at 10:19 AM on August 10, 2006

Sorry SteveInMaine, I should have checked the preview first.
posted by mezzanayne at 10:20 AM on August 10, 2006

The task scheduler/autoexec route is, I think, the best method -- but if you're on a locked-down corporate PC, don't count on the task scheduler working. I'd give the task scheduler a try before going down the option 1 route.
posted by treepour at 11:00 AM on August 10, 2006

Response by poster: Thank you all for the great and prompt responses, now a follow-up question:
How can I automatically email the macro generated file every Friday as well?
Thanks again! :)
posted by strangelove at 11:18 AM on August 10, 2006

You can either create a new macro and call it from the Autoexec macro or just append a new action to the existing Autoexec macro.

The action will be 'SendObject'. Do a search for 'SendObject' within MS Access Help for more information on the individual parameters.

If you're comfortable with VBA, here's a little example of the code that you can use (then you can call the sub name within the macro):

Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String

strRecipient = "[e-mail]"
strSubject = "This is the email subject"
strMessageBody = "Here is a whole bunch of interesting text to accompany the attachment."

DoCmd.SendObject acSendReport, "rptNameofReport", acFormatXLS, strRecipient, , , strSubject, strMessageBody, False

** ** ** **

This little sub assumes that the attachment is a report and will send the report results in .xls format. Of course other options are available.

Good luck!
posted by mezzanayne at 2:38 PM on August 10, 2006

Nice example, mezzanayne, but wanted to clarify one very small thing -- you called the VBA routine a "sub" -- but the routine would need to be declared as a function (e.g., "Function SendStuff" rather than "Sub SendStuff") in order to be called from a macro (unless that's changed in the last versions of Access). I mention it only because I recall that not knowing this had me banging my head against the keyboard back when I was first learning to use VBA in Access.
posted by treepour at 6:12 PM on August 10, 2006

Thanks treepour, you're right. I never use macros so that's something that I wouldn't have thought about.
posted by mezzanayne at 11:15 PM on August 10, 2006