How can I schedule a Macro to run automatically in Access 2007

Question

I'd like to have a macro, called Macro1, for example, run every day at 9 AM. It works great on its own from the VB code editor in Access 2007 but I would like it to be able to execute automatically without access being open.

Please note that I don't want there to have to be any human intervention, it needs to be able to run automatically without someone opening Access to trigger autoexec or onload or something similar.

Is this at all possible?

Malnutrition

this one is strange ... you want to run an Access macro without running Access! Can't you do the same trick in another language? – Terr

@Philippe: Basically, I need to pull data from another database on a schedule. Data is being entered in another database managed by some crappy software that I'm not allowed touching. I need to get some information that's entered every day and I want to be able to do so without having to have someone open access and click something every day. Not necessarily run it without running access, but instead open access and execute that macro then exit if that makes more sense. – Malnutrition

If you're just running queries, then you can do it with DAO via vbScript. For the macro, see my answer. – Streeter

Answer 1

You can use a MS Access command line switch to run a macro. If you search for "commandline" in Access help, the topic "Startup command-line options" gives you all the commandline switches. The switch for running a macro is x macro.

So, if you write your macro to run whatever you want and have it exit Access when it finishes, you can then create a commandline that will do the trick and put it in a batch file that the Windows Task Scheduler can execute.

However, as I said in a comment above, if you are just running some queries, I'd say it makes more sense to bypass Access entirely and use DAO directly in a scheduled vbScript to execute the queries.

Streeter

This sounds good. It's not just some queries although that is part of it. I need to query a linked table from another database and update content in the current database. – Malnutrition

I'm not sure what the differences is between running some queries and "querying a linked table...and updating content in the current database" -- isn't that done by running a query? If it's not, maybe it should be? – Streeter

Yeah, it is done by querying it. But then I need to use the results to update part of another local table. – Malnutrition

I'm still not getting why "using the results to update" something else prohibits what I'm suggesting. – Streeter

@Malnutrition -- I think what David's getting at is that your update can (and probably should) be done using an update query, which would mean that you're still "running some queries", nothing more. – Vandal

I've got one row that contains three fields that contain information from one row each from a linked table. That's why I can't just run an update query because each row in my table has to be updated three times, once in every field. Unless I run three update queries. I suppose it is unnecessarily complicated but the linked tables are managed by another software and it seems to like making things complicated. – Malnutrition

I don't get why you consider running three updates a problem. – Streeter

I suppose I just didn't think of it earlier. I'm studying in Computer Sciences and I'm not an Access pro just yet. I was asked to set up a few reports and it turned into this big mess. – Malnutrition

@alexcoco: Welcome to the real world! – Streeter

@David-W-Fenton: Hah, yes, indeed. Thanks for the help! – Malnutrition

Answer 2

You must create vbscript to run your macro and create a batch file to schedule your vbscript.

• vbscript code, save this as schedule.vbs file

  Dim accessApp
  set accessApp = createObject("Access.Application") accessApp.OpenCurrentDataBase("fullpath\msaccessdb")

  accessApp.Run "Macroname",param1, param2,param3
  accessApp.Quit
  set accessApp = nothing

• THEN create file.bat

  @echo off
  cscript schedule.vbs

and your ready to schedule it using the windows task scheduler http://www.thewindowsclub.com/how-to-schedule-batch-file-run-automatically-windows-7

hope this help :D

Trictrac

You're almost 3 years late and this project is no longer relevant for me. There is already an accepted answer. Perhaps your answer will still help someone else though. Thanks for posting. – Malnutrition

@noodle, what's the point of the vbscript file? Why not just run the macro directly? – Praetor

Answer 3

You can use Windows Task Scheduler and VBScript to run code or start Access.

Eunuchoidism

So I would essentially schedule a script to run every day at a certain time and then have that script open access and execute a macro? Is this doable? – Malnutrition

...assuming, of course, that what the macro ultimately does is something that can be done through DAO or via Access automation. – Streeter