RUNNING MACROS

  • 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

     

  • 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.

     

  • I see you set me.timerinterval=0

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

  • 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.

  • 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. 

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

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

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

    Thanks

  • 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

  • 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"

     

  • I have tried similiar solutions and found dts more reliable.

  • 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?

  • 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

  • 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