June 28, 2006 at 11:53 am
I run two Access databases (one is the main robust system and the second is a read only reporting tool).
There are several daily jobs I have to run to:
These jobs are in the form of macros which I have to manually click to run.
What's the best way to automatically run these macros, let's say in the middle of the night, without physically clicking on a 'run macro' button.
Thanks
Krazylain
June 28, 2006 at 4:14 pm
You could create a form, and set the timer interval to check the system clock every 10 minutes or so, until the system time reaches your target time (e.g. between 1:30 and 2:00 am). Then programmatically execute your queries in the OnTimer event. I would do it in VBA:
IF datepart("h",now())=intTargetHour and
abs(datepart("n",now()) - intTargetMinute) <15 then
me.timerinterval=0 ' turn off the timer
DoCmd.RunMacro strMacroName1
DoCmd.RunMacro strMacroName2
DoCmd.RunMacro strMacroNam3
end if
Of course, you have to leave the computer on with the Access form open on your screen.
June 29, 2006 at 2:54 am
I see you set me.timerinterval=0
doesn't that disable the OnTimer event so the check is not carried out the next day?
June 29, 2006 at 5:41 am
Instead of leaving your program running fulltime, you could use Scheduled Tasks in Control Panel to open the application and run your macro. If you type "Startup command-line options" in the search box for Microsoft Access Help you will get several articles on how to do this. Now getting the program to shut-down when it's through might take more research.
June 29, 2006 at 6:32 am
Regarding setting the timerinterval =0. Yes, it would prevent the procedure from running the next day. But you also need to protect against it repeating itself in ten minutes. After the macros have run once, the timer may fire again, and if the system clock is still within the designated parameters, the macros will be executed again. You really need to shut 'er down after you have accomplished the task the first time.
I do like the idea of using a scheduled task to automatically launch the Microsoft Access application on a daily basis.
June 29, 2006 at 6:40 am
As to getting the program to shut-down, I suppose using
Application.Quit after having the macros run should do the job.
June 29, 2006 at 7:20 am
Thanks guys for all your responses....I'll make a go at it during the day and let you know my progress..
Thanks
June 29, 2006 at 9:28 am
Create a form that loads when the database is opened. Use this code in the form load event. Open the database using scheduler. If you schedule it for 1 am it will run and close.
Private Sub Form_Load()
If Time() >= #1:00:00 AM# And Time() <= #1:10:00 AM# Then
DoCmd.RunMacro MacroName1
Application.Quit
End Sub
June 29, 2006 at 12:25 pm
I had partial success with the following code (i converted the macro to a module and called that module using runcommand:
Private Sub Form_Load()
If Time() >= #2:09:00 PM# And Time() <= #2:20:00 PM# Then
DoCmd.RunCommand TestModule
End If
Application.Quit
End Sub
The above works perfectly (and executes the command - all files are exported) up until Application.Quit. It appears that access does not want to quit - a message box pops up with the following
"Runtime error '2501' The Run
The RunCommand action was canceled"
June 29, 2006 at 12:30 pm
I have tried similiar solutions and found dts more reliable.
June 29, 2006 at 12:39 pm
With RunCommand you can only use an intrinsic constant that specifies which built-in menu or toolbar command is to be run.
I suppose you've converted the macros into subroutines so you just have to specify the name of the sub(with possible arguments)
Isn't dts used when importing data into SQL Server and exporting data from it?
June 29, 2006 at 12:44 pm
hmm...I'm not sure how DTS fits in the picture; hope I'm not in the wrong forum..
The data is stored in an Access database and not in SQL Server
July 3, 2006 at 7:32 am
Private Sub Form_Load()
If Time() >= #2:09:00 PM# And Time() <= #2:20:00 PM# Then
call TestModule
End If
Application.Quit
End Sub
Make TestModule public and just call it don't use RunCommand
Public Sub TestModule()
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply