Running Microsoft Access as a Scheduled Task

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

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 marocrs, and no macros at all - only pure VBA sub calls + this vbs script.

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.

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.