RUNNING MACROS

Question

RUNNING MACROS

krazylain

SSC-Addicted

Points: 452
More actions
June 28, 2006 at 11:53 am

#168809
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:
1. update the main database
2. remove data from the reporting database
3. update the reporting database
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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

tom menacher SSC Enthusiast Points: 192 More actions · Answer 1

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.

grovelli-262555 SSCrazy Points: 2633 More actions · Answer 2

I see you set me.timerinterval=0

doesn't that disable the OnTimer event so the check is not carried out the next day?

macrocharlie Ten Centuries Points: 1055 More actions · Answer 3

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.

tom menacher SSC Enthusiast Points: 192 More actions · Answer 4

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.

grovelli-262555 SSCrazy Points: 2633 More actions · Answer 5

As to getting the program to shut-down, I suppose using

Application.Quit after having the macros run should do the job.

krazylain SSC-Addicted Points: 452 More actions · Answer 6

Thanks guys for all your responses....I'll make a go at it during the day and let you know my progress..

Thanks

Randy Helms SSC Enthusiast Points: 139 More actions · Answer 7

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

krazylain SSC-Addicted Points: 452 More actions · Answer 8

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"

Perry Harts Ten Centuries Points: 1271 More actions · Answer 9

I have tried similiar solutions and found dts more reliable.

grovelli-262555 SSCrazy Points: 2633 More actions · Answer 10

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?

krazylain SSC-Addicted Points: 452 More actions · Answer 11

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

Randy Helms SSC Enthusiast Points: 139 More actions · Answer 12

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()