Running Microsoft Access as a Scheduled Task

Question

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

Answer 1

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:

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

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