How do I run a macro at a specified time of day (unattended) 1

[Guest_imported]

I need to run some macros which update our database. The problem is the macros need to run in the middle of the night when everyone is out of the database and no transactions are taking place. Is there a way that I can set some code to fire off a DoCmd.RunMacro at 4:00 in the morning. An API call maybe????

I have come up with this solution:

Sub Run_Macro()
Dim oApp As Object
Set oApp = CreateObject("Excel.Application&quot
If oApp.Wait(Now + TimeValue("0:12:00&quot) Then
Application.DoCmd.RunMacro "WAMU Daily Statistics Update"
End If
End Sub

Here I have created an instance of Excel just so I can use the "Wait" Method.
Obviously this is a pretty tacky solution and does not sufficiently address the problem. Any Suggestions???

Please Help
jzcomputer

 

[GingerR]

we've been doing this for years. used to have the hack-job similar to yours but have grown up over the years.

Do you know about Autoexec macros? if not, if you name a macro Autoexec, it will launch when you open a db. there are other ways but this is simplest. so you can have all of your refreshing code in one 'backend' db that opens in the middle of the night and does it's thing and closes.

so...how to launch in the middle of the night?

what OS do you have? if you have Win2k there is a scheduler available with the OS and you can launch a db that just has the data-refresh code in it. if you have win2k tell me and i'll give you details.

g

 

[Guest_imported]

Thanks GingerR,
I do know about autoexec but could use some help with the scheduler. I am running win2K from my local machine but the database (Access97) is on our server which is NT4.0.
Thanks for any details you can give.
jzcomputer

 

[GingerR]

doesnt matter that the db is on a server.

go to Scheduler and go add new task. go thru the wizard-it comes up with a list of .exe files it finds on your machine. Choose BROWSE and browse to your db. In the wizard, choose OPEN ADVANCED PROPERTIES WHEN FINISHED (or you can always right-click on the task later and choose PROPERTIES).

1) if you only have one version of Access on your machine:
in the RUN area: You will want to put the full path to the db instead of just the drive letter, of course in case you ever map the server to a diff drive.
"\\app-evt-23\Data\Backend.mdb"
in the START IN area:
"\\app-evt-23\Data\"

2) If you have more than one copy of Access on your machine (we do) you have to first put the path of the Access version you want the db to open in. you'll have to mess around with it, but put in this format, substituting the path to your Access.exe and then the path to the mdb file:
in the RUN area:
"Path to Access.exe" "Path to .mdb file" (space in between)

"C:\Program Files\Office97\Office\msaccess.exe" "\\app-evt-23\Data\Backend.mdb"

in the START IN area:
"C:\Program Files\Office97\Office\"

ok? this should be all you need. It will fill in your domain/userid and prompt you for your password. Then just do your schedule. If you only want it done M-F, choose WEEKLY then M,T,W,Th,F. you can also goof around with multiple schedules.

let me know how it goes
g

 

[PantoKing]

Another way to do this is to have a db with a form open and have an OnTimer event on this form that checks the time. When a certain time is reached it runs the code. You can use NOW() to find out the time and have an If statement to trigger when the time is passed. Make sure that one of the first thing the code does is change the Timer Interval to something quite large so that it doesn't keep trying to run the code. Then make sure that you close the form to stop it running again.

HTH

Steve. Make things as simple as possible — but no simpler.

 

[Guest_imported]

Thanks all,
I am using the scheduler approach and the first run is tonight. All test runs have gone perfectly so I am anticipating success.
Thanks again.

 

[GingerR]

Glad to hear it.

The scheduler gig is better i think, for me anyhow. I have about a dozen things a co-worker and I run between midnight and 8 am, so it's excellent for juggling all of those tasks.

good luck--g