Invoking a Database From the Command Line

[NeoPa]

Introduction:

Sometimes, it may be necessary to open a database from the command line, or even from a shortcut. Often, in such cases, a specific routine is required to be run in this mode, yet when opened normally, the database should not execute this same code. We will cover both of these scenarios in this short tutorial.

When we've covered that, we will look at scheduling a specific database to open, execute some code, and close, at a predetermined time. The scheduler, by default, has no user credentials associated with it (It runs by default as the SYSTEM account), and this tutorial will not cover changing that, as it is a deeper issue with associated ramifications. What this means, in effect, is that networked resources are generally unavailable to normal scheduled processes.


Command Line & CMD File:

A command line can be set up that invokes Access in conjunction with an individual database, as well as a parameter to be passed to it if required. We'll get onto that later. Such a command (single) can be associated with a shortcut, or even a scheduled task, all by itself. More complex sets of instructions however, need either to be included as code invoked by the database itself (a possible yet rather clumsy solution), or grouped together into a CMD file which itself need only be invoked in a variety of standard ways (Command Line; Start / Run; Shortcut; Scheduler; From within some VBA code; etc).

As a simple example of the sorts of things that can be done in a CMD file I include one I use. I've doctored real-life sensitive data but the format's the same.
NB. As the network address used here (Line #5) is actually the local machine, even the SYSTEM account can run this successfully.

REM V: Drive should be available.
REM Make sure it is added if not.
REN V:\Vecta\Imports\Done Done
IF NOT ERRORLEVEL 1 GOTO Cont1
NET USE V: \\STP01NTS003\Vecta
 
:Cont1
CD /D V:\Vecta
IF NOT ERRORLEVEL 1 GOTO Cont2
ECHO Unable to map correct drive.
GOTO EndCmd
 
:Cont2
REM Start Access stuff here
REM The AccessPath Environment Variable must be set for this to work.
"%AccessPath%\MSAccess.Exe" V:\Vecta\Vecta.Mdb ;Auto
 
CD Imports
REN Vecta*.Csv *.*
IF NOT ERRORLEVEL 1 GOTO Cont3
ECHO No files to process.
GOTO EndCmd
 
:Cont3
REM WinZip CMDLine must be installed for this to work.
"C:\Program Files\WinZip\WZZip.Exe" -a+ -ex -ybc StatPlusVecta.Zip *.Csv
DEL *.Csv
START "Upload to {remote server}" /DV:\Vecta\Imports /LOW FTP.Exe -n -s:V:\Vecta\VectaWebDaily.Ftp -w:16384 {DummyFTPAddress}
 
:EndCmd
REM PAUSE
ECHO ON

I don't plan to go through this in detail as it is only to give a flavour of what's possible, however this, in it's undoctored state, works perfectly as a scheduled task. Line #16 is an example of invoking Access with an associated database and also passes a parameter to that database.


Starting Access into a Database:

To handle calling Access and passing both the database AND the parameter to the database, you need a command with essentially 4 elements in it :

1. The address of the Access executable itself. If it is in the Path Environment Variable then this is not necessary, but it's best not to assume that. It generally isn't in my experience. This value may be got by enquiring of the registry, but I find that setting an Environment Variable for each PC where this is to be used works fine. Somehow or another, you need to handle this requirement whatever.
2. The name of the executable file itself. Generally and simply "MSAccess.Exe".
3. The name of the database to be passed.
4. The parameter you want passed to the database. This should follow a ";" character (This is a shortcut for /cmd). In the CMD example (Line #16) above the parameter is "Auto".

If the fourth element (the parameter) isn't passed the database should open normally for full interaction with the user.
A useful reference for invoking Access can be found at How to use command-line switches in Microsoft Access.


Designing the Database to Handle the Parameter:

There are two ways for a database to run code automatically upon opening :

1. An AutoExec macro. I would never personally, recommend using macros in grown-up software, whatever they try to foist upon you in Access 2007.
2. In the Tools / Startup form there is the option of selecting a form to run automatically open opening of the database. The chosen form should contain an OnOpen event procedure. This runs automatically when the form is opened, hence also when the database is opened.

In my example code which follows, I check the Command() function to see the passed parameter, and execute specific code if it turns out to be the predefined value "Auto".
On Line #4 I invoke a procedure which is also used when the database is opened normally, hence the parameter (varOpenArgs) which is passed to tell the procedure thet we are running in Auto mode. Lines #5 & #6 ensure that the normal processing of the form (showing on the screen and awaiting operator input) are to be ignored, and that the database should close down upon completion of the process. This particular database is used with the scheduler and anything along the lines of operator intervention would fail as there will be none available to intervene.

Private Sub Form_Open(Cancel As Integer)
    'Some standard stuff
    If Command = "Auto" Then
        Call clsTo.ShowForm(strTo:="frmUpdate", varOpenArgs:="Auto")
        Cancel = True
        Call Application.Quit(Option:=acQuitSaveNone)
    End If
End Sub

Scheduling:

Although there are other methods available, I will stick here with the basic NT Scheduler which has been around for many versions of Windows NT (Inc 3.0; 3.5; 4.0; 2000; XP; Vista; etc). A useful reference for the Command Line Scheduler (AT) can be found at How To Use the AT Command to Schedule Tasks.

My command line follows. I will explain the elements used, below that :

AT 2:30 /INTERACTIVE /EVERY:M,T,W,Th,F,S "D:\Vecta\VectaWebDaily.Cmd"

1. AT.EXE is the command scheduler itself.
2. 2:30 specifies the task should run at that time in the morning on those days it runs.
3. /INTERACTIVE allows any (presumably insomniac) operator on the console to see and interact with the task. Relying on interaction is highly dubious, though for testing purposes (and possibly debugging if it errors) it is a handy option.
4. /EVERY:M,T,W,Th,F,S means each specified day of the week. The reference above lists the code for each day.
5. The executed task should always be in quotes. The commands available are not restricted to CMDs. Pretty well any executable command can be used here.

Further Comment:

In the typical setup where a CMD file is used to invoke the Database process, it can also be used to do further processing on any files output or updated by the Database process.

To accompany this tutorial I knocked up a very small example (attached).

Although it's small it does include all the elements mentioned.

OpenAuto.Cmd :

@ECHO OFF
REM The database is opened from this CMD file.
REM Before running, the Environment Variable AccessPath should be set.
REM Without this the process will abort.
REM EG SET AccessPath=C:\Program Files\Microsoft Office\OFFICE11
REM The access database (OpenAuto.Mdb) should be in the default db folder.
REM To use a different folder than Access's default, specify it explicitly.
 
IF "%AccessPath%" == "" GOTO ERR_NAP
"%AccessPath%\MSAccess.Exe" OpenAuto.Mdb ;Auto
ECHO This should have run and terminated automatically.
GOTO END
 
:ERR_NAP
ECHO AccessPath not set!
 
:END
ECHO Hit any key to continue.
PAUSE
ECHO ON

Form_Open() :

Option Compare Database
Option Explicit
 
Private Sub Form_Open(Cancel As Integer)
    If Command() = "Auto" Then
        Call MsgBox("The database is opened automatically, " & _
                    "and will close as soon as you click on OK", _
                    vbOKOnly, _
                    "AutoMessage")
        Cancel = True
        Call Application.Quit(acQuitSaveNone)
    End If
End Sub