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

Answer 2

A VBS script can call any standard VBA SUBROUTINE with the following:

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("C:\MyApp\MultiSelect.mdb")

accessApp.Run "TimeUpDate"

accessApp.Quit
set accessApp = nothing

Note that the sub TimeUpDate is a standard VBA subroutine. This means no Autoexec macros, and no macros at all - only pure VBA sub calls + this VBS script.

Answer 3

There is a little known trick dating back to the earliest years of access to allow it to run as a process which still works. Access will always on startup look for a macro called "Autoexec". If it finds it it will immediately start executing this macro. I find this is extremely useful if I need to initialise the program before opening forms or, as in the case of the original questioner, run access as a scheduled process with no user I/O.

Answer 4

After beating my head against the wall for about four hours, I finally got this to work:

1) Create a DOS batch file with one line it. The line is composed of three parts a) the full path to Microsoft Access (msaccess.exe), b) the full path of the Microsoft Access database with the code in it, and c) the Access command line argument "/x MacroName". The first two items should be surrounded with quotes. Mine looks like this:

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "C:\MyPrograms\ProdDB Reports\ProdDB Reports.accdb" /X DailyTestReportsRun

2) Create a macro inside of Access with the name you used in your batch file. It has one command, RunCode, with an argument of the name of a VBA function you want to call. This should be followed by open/close parenthesis "()". I didn't try passing any parameters to the function; I think this would be problematic.

4) Make sure the VBA function you call has a Docmd.Quit command at the end, or that you add this as a second line to your macro. These will make sure that Access doesn't stay open after your process runs.

5) In Windows Task Scheduler, select "create a basic task" (which invokes a wizard). Set the program name to the name of your DOS batch file. There's a helpful check box labeled something like "Open the properties window when I'm finished." Check that so you that go to the properties window.

6) Set the task to run regardless of whether the user is logged on or not. Also check on the "Run with highest privileges" box, which one friend on here suggested.

You can now test everything by right-clicking the scheduled task and selecting the Run command.

I liked Albert Kallal's script and tried it. Everything worked great until I tried to schedule it. Then, for some mysterious reason the scheduler would not kick it off.