Automatic Macro Execution

[baadams]

I know this subject has been discussed multiple times. I've searched and can't seem to find the answer.

How do you automatically run a Macro at set intervals during the day?

Here's the situation I'm in now. We have an ODBC connection to an Oracle db. The problem is I'm the only user with an account on that db. So I've got a macro that refrehes the tables in the backend so the 30+ users can run reports from the front end. Getting each user a Oracle account is currently not an option. But to keep the report information current I'd like to run the macro a couple times a day. I can easily do it manually, but if I forget it would be nice for the update to be automatic. I'd also like to know if I could get it to run when I'm away and my computer is locked. Thanks for the help.

 

[xenou]

I do so by using the windows scheduler using a vbs script. An example follows. You save it exactly as it is in a text file using the .vbs extension (I.e., C:\SomeFolder\MyScript.vbs) then run your script as a task. Obviously, make the necessary changes for your DB and macro.

[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Call[/COLOR] Macro_Go

[COLOR="SeaGreen"]'________________[/COLOR]
[COLOR="Navy"]Sub[/COLOR] Macro_Go()
    [COLOR="SeaGreen"]'-----------------------------------------------------------------[/COLOR]
    ret = 1
    [COLOR="Navy"]Set[/COLOR] objAccess = CreateObject("Access.Application")
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] objAccess [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]With[/COLOR] objAccess
            .OpenCurrentDatabase "C:\SomeFolder\SomeDB.mdb", False
            [COLOR="Navy"]Call[/COLOR] .Run("Macro1")
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

    [COLOR="SeaGreen"]'-----------------------------------------------------------------------[/COLOR]
    [COLOR="SeaGreen"]'Exit code - close Access[/COLOR]
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] objAccess [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]With[/COLOR] objAccess
            .CloseCurrentDatabase
            .Quit
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Then you schedule your task in Windows Scheduler. Just add the path to the script in the run field: C:\SomeFolder\MyScript.vbs The scheduler will let you run the same task several times a day under advanced settings (I've never tried it but you could say repeat 4 times every 2 hours, I think).

 

[baadams]

Great! I'll try that ASAP. Thanks.

 

[baadams]

Xenou,
I appreciate the help. The Macro executes as expected, with exception to the "Call .Run("Macro1")".

When this call is executed I get an error saying Macro1 could not be found? Eventhough when the database opens I can see it under the Macros object header? I've checked the spelling 10 times to make sure it's correct. If I comment this one line out, the db opens and closes as expected.

Thanks.

 

[xenou]

Is the macro a VBA procedure or an Access "Macro"? I'm assuming the former. I think if it's an Access "Macro" that you would need to run it through the DoCmd.RunMacro function. Possibly: objAccess.DoCmd.RunMacro "Macro1"

The lingo can be confusing. VBA "macros" and Access "Macros" are slightly different but both can be used in Access.

 

[baadams]

This is a Access "Macro". I'll try the DoCmd, that should probably work.

Thanks for all the help