ms-accessbatch-filevbscriptauto-update

Running Microsoft Access as a Scheduled Task

I am seeking comments on how to schedule auto updates of a database (.accdb) since I am not very comfortable with the process I have set up.

Currently, it works as follow:

  1. Task Scheduler calls a .bat
  2. .bat calls a .vbs
  3. .vbs opens the database and calls a macro
  4. The macro calls a function (VBA Level)
  5. The function calls the update Subroutine

I consider there are too many steps and the fact that it requires 2 external files (.Bat and .vbs) related to the database and stored on the system increase the risk that the procedure would break.

Apparently (but please tell me that I am wrong and how I can change it) .vbs cannot call a subroutine but only a macro. Identically, an access macro cannot call a subroutine but only a function if the user is expecting to enter the VB environment of the database. This is the reason why I called a function (VBA Level) that then calls the subroutine.

Hope some of you know how to shorten the steps and eventually get ride of the .bat and .vbs

Solution

  • To the best of my knowledge the shortest path for a Windows Scheduled Task to "do something useful in Access VBA" is:

    Create a Public Function (not Sub) in the database. For example:

    Option Compare Database
Option Explicit

Public Function WriteToTable1()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    cdb.Execute "INSERT INTO Table1 (textCol) VALUES ('sched test')", dbFailOnError
    Set cdb = Nothing
    Application.Quit
End Function

    Create a Macro in the database to invoke the function:

    Macro.png

    Create a Windows Scheduled Task to invoke MSACCESS.EXE with the appropriate parameters

    SchedTask.png

    In the above dialog box the values are:

    Program/script:

    "C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE"

    Add arguments (optional):

    C:\Users\Public\schedTest.accdb /x DoSomething