Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 861
ProtoVB Avatar asked Nov 27 '13 14:11

ProtoVB


People also ask

Can Microsoft Access be used for scheduling?

You can use Microsoft Access to create a group work schedule that you can save and update at any time. To create your schedule in Access, download one of the many available templates from the Microsoft Office website.

Can Microsoft Access be automated?

Microsoft Access is a COM component that supports Automation, formerly called OLE Automation. Microsoft Access supports Automation in two ways. From Microsoft Access, you can work with objects supplied by another component.

Can you schedule a macro to run in Access?

So, if you write your macro to run whatever you want and have it exit Access when it finishes, you can then create a commandline that will do the trick and put it in a batch file that the Windows Task Scheduler can execute.

Is Access still relevant in 2020?

No, certainly not anytime soon. Microsoft Access is used by millions of businesses and organisations throughout the world and Microsoft have stated that they remain committed to not only supporting Access but to continue to enhance it.


4 Answers

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
like image 148
Gord Thompson Avatar answered Oct 23 '22 20:10

Gord Thompson


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.

like image 36
Albert D. Kallal Avatar answered Oct 23 '22 18:10

Albert D. Kallal


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.

like image 6
Andrew Thackray Avatar answered Oct 23 '22 18:10

Andrew Thackray


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.

like image 3
Karl Hoaglund Avatar answered Oct 23 '22 18:10

Karl Hoaglund