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.

 

[Jet Ledgard]

I just read your answer, and I agree. I have a vbscript that runs an 'array' of functions, updating various tables one at a time. All works well except when I'm already on the database.

Originally, I had the Create Access.Application followed by OpenCurrentDatabase... However, sometimes it would bomb, so I found that you should use a GetObject (Access.application) and then, if error 429, do the Create Access.Application. However, when I first got this, the GetObject included the specifc MDB., so now it's ok, when I'm on any other access db, or in none. But NOT if i'm on the exact database where all this updates happen.

Is there a similar code that will ensure that these functions run NO MATTER if I'm in access, in the database, or none?

(-- not so important... similar issue but notepad...
Also, how do I avoid the script to bum if the log text file is in use? I have more than one task scheduled and sometimes I may be running a single bat file section and the task bombs just coz the log is inuse..???)




here's my current code:


option explicit

On Error resume Next

dim objFSO
dim objFile
dim accObj
dim MyArray
dim MyModule
Dim item

Const ForAppending = 8

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Test.log", ForAppending, True)

Const ERR_APP_NOTRUNNING = 429

' Attempt to reference running instance of Access.
Set accObj= GetObject("H:\home\CCHS\Call Center Operations\" _
& "SNAD- Service Network Development\Regional Coordinators\" _
& "BackEnds\DiscvrRepSched\SNADupdatesFE.accdb", "Access.Application")

' If Access isn't running, create a new instance.
If Err = ERR_APP_NOTRUNNING Then
Set accObj= New Access.Application
accObj.OpenCurrentDataBase "H:\home\CCHS\Call Center Operations\" _
& "SNAD- Service Network Development\Regional Coordinators\" _
& "BackEnds\DiscvrRepSched\SNADupdatesFE.accdb"
End If


accObj.visible = False

myArray = Array("UpdateVendClaimDetl","UpdateVendorReassignments","UpdateCurrentQ")

For each item in myArray

accObj.run item

If Err.Number <> 0 Then
objFile.Writeline "Access Update of " & item & " failed somewhere ! " & Now _
& " " & Err.Number & " Error Description: " & Err.Description
Err.Clear
Else
objFile.Writeline "Access Update of " & item & " was completed " & Now
End If
Err.Clear

Next

accObj.CloseCurrentDatabase
objFile.Close

' Clear memory

Set accObj = Nothing
Set objFSO = Nothing
Set objFile = Nothing