This is HOW TO pass Access parameters to Task Scheduler

[ThomasAJ]

Rather than answering my own post further down I thought I'd post a new
question to help others.

After trying various combinations of "path\MSaccess.exe" "path\myDB.MDB"
/cmd "Event" DIRECTLY into Task Scheduler

AND

In a VBSCRIPT I tried: (don't get too hung up on the correct "quote" numbers)

set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("path\myDB.MDB" /cmd "Event")

AND also

set objShell = CreateObject("Shell.Application")
objShell.ShellExecute "C:\Program Files (x86)\Microsoft
Office\OFFICE11\MSACCESS.EXE"" "path\myDB.MDB" /cmd "Event"

I finally got it right with:
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run """C:\Program Files (x86)\Microsoft
Office\OFFICE11\MSACCESS.EXE"" ""path\myDB.MDB"" /cmd ""Event"""

The above quote numbers are correct.

The failed attempts' quote numbers are not the problem (I tried many
different combinations). The problem is they cannot handle passing
parameters/arguments to MSACCESS.EXE along with the 'called' MDB.

The biggest surprise is that the method 'OpenCurrentDataBase' cannot pass
parameters. What were they thinking? (er long Friday lunch I suspect...coke
and pizza eh)

 

[ThomasAJ]

I forgot to say Task Sceduler runs the script.

 

[david]

The biggest surprise is that the method 'OpenCurrentDataBase' cannot pass

Yes, always a big problem with most methods.

When using OpenCurrentDataBase, you set most parameters first, rather
than using the command line. For example, you set the default mdw file,
user name and password rather than using command line parameters.
That works for most parameters, but some, like /cmd, you set after you
get the object.

(david)

 

[Albert D. Kallal]

Passing parameters on the command line is not very flexible.

Why not just load a copy of ms-access, and then have your script "run"
whatever code you want?

just go:

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("C:\some path name\someMdb.mdb")

accessApp.Run "TimeUpDate"
accessApp.Quit
set accessApp = nothing

Look how simple the above was. What happens if your batch system
needs to run TWO vba routines?

If yes, then we simple add to the above such as:

accessApp.Run "TimeUpDate" ' step 1 to run
accessApp.Run "PrinttimeReprots' ' step 2 to run

So, if we have 4, or 5 different batch systems, then how can you modify your
code system to have some batches run the "2nd" routine after the first when
you using:

path\myDB.MDB" /cmd "Event"

You could modify "event" to run the 2nd part, but in some cases you might
NOT want to run the 2nd case. What are you going to do now, create a "new"
code routine in the mob that runs event + "some other" event? This approach
gives you ZERO control over time to add, remove or have batch files run
"several" routines one after another.

I would not bother with shell nor some /exec command. Simply write a
windows script as per above that lets you CALL ANY vba routine you want and
then correctly shuts down ms-access.

in your case, "which" subroutine is going to be responsible to shut-down
ms-access? As I said, the instant you need to call more then "one" routine
in that batch process then the problem of which routine will shut down
ms-access becomes a problem. Again, with he above simple script..you call
as many vba subroutines as you need that exist in standard code modules,
then your script correctly shuts down ms=access.

So, the problem of using shell is messy, gives no control over having more
then one routine, and worse your approach means that each code routine your
written over time when called in your shell must shut down access and
that makes the code far less flexible.

I think it better to write the code routines as such they can be used by
both your batch processing routines and by your access
application in its regular operation. So those routines should be useable
by both the batch file and the application. Even if that code to be run
during the batch is never to be used by your application during regular use,
you at least have code that does not have to shut down ms-access and "can"
be used by other routines because they are not responsible for shutting down
ms-access.