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

Rift

(1) Is either your .bat or your .vbs script anything more than a single line of code? If not, then you probably could just update your Scheduled Task to run MSACCESS.EXE with the required parameters. (2) Yes, a Macro in Access can only invoke a Function, but you could probably convert your Sub to a Function that doesn't return anything (or returns some arbitrary value like True). – Hosbein

I tried calling MSACCESS.EXE from the Task Scheduler but I was not successful and that's the reason why I used the .bat .vbs method. Will try again. I supopose I need to have a timer in Access then that triggers the update... – Rift

No, you invoke MSACCESS.EXE with the -x command-line switch and the name of the macro you want to run. Details here. – Hosbein

Thanks Gord - looks like my .bat and .vbs scipts are about to vanish :) – Rift

vbs can most certainly call a standard VBA subroutine, and in fact I hard pressed to figure out any other way. As a result, you should ONLY need the one vbs script. I posted an answer to this. – Branum

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

Hosbein

For the arguments, it needs quotes around the file path. "C:\Users\Public\schedTest.accdb" /x DoSomething" – Haun

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.

Branum

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.

Soane

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.

Glomerulonephritis

Answer 5

none of the above work ms access DB with task scheduler will not work as to open the db run code and quit the application the solution I found is to avoid task scheduler and have the ms access db open all time and have a timer in msaccess do the job

Roommate