How to Pass a Parm from VBS script to Access 2007

[Brad]

We have a VBS Script that initiates a Sub in an Access 2007 DB.

This works nicely.

We now would like to pass a parm from our VBS script to this Sub. It is our
understanding that the "command" field in our VBA code will contain the
passed data.

What we can't figure out is how to code the VBS script to pass the parm to
Access.

An example would be most appreciated.

Thanks,

Brad

 

[Tom van Stiphout]

On Tue, 20 Apr 2010 18:59:01 -0700, Brad

The way I understand it:
Your vba script invokes the db like this:
<path_to>msaccess.exe <path_to>your.mdb /cmd myCommand
In your startup code in the Access application, you can use the
Command function:
select case Command()
case "this"
'TODO: run this code
case "that"
'TODO: run that code
end select

-Tom.
Microsoft Access MVP

 

[Tony Toews [MVP]]

The way I understand it:
Your vba script invokes the db like this:
<path_to>msaccess.exe <path_to>your.mdb /cmd myCommand
In your startup code in the Access application, you can use the
Command function:

The problem being that the VBS script likely doesn't know the location
of msaccess.exe and/or the location of msaccess.exe might vary from
system to system or from system to Terminal Server.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Brad]

Tom and Tony,

Sorry, I didn't ask my question very well.

We are getting hung up on the syntax of the VBS Script.

We have the Windows Scheduler fire up a little VBS Script which
in turn fires up a test Access 2007 DB. This works nicely.

We know how to work with the "Command" field once it gets to Acesss.

What we can't figure out is how to code the parameter (that we want to pass
to Access) in the VBS Script that fires up the Access application.

Brad

 

[Hans Up]

We know how to work with the "Command" field once it gets to Acesss.

What we can't figure out is how to code the parameter (that we want to pass
to Access) in the VBS Script that fires up the Access application.

I experimented with this and I'll show you what I came up with, but not
sure it's what you're after.

My VBS script:

Dim objShell
Dim strExe
Dim strDb
Dim strParam

Set objShell = WScript.CreateObject("WScript.Shell")
strExe = "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
strDb = "C:\Access\wip\version_control\vc.mdb"
strParam = "Hi!"
ObjShell.exec(strExe & " " & strDb & " /cmd " & strParam)
Set ObjShell = Nothing

I have a startup form assigned in my database. So I put this in the
form's open event:

Private Sub Form_Open(Cancel As Integer)
If Len(Command()) > 0 Then
MsgBox "Started with '" & Command() & "'"
End If
End Sub

Tom's Select Case suggestion offers more interesting possibilities.

If this is not helpful, please show us your VBS to help us understand
what you want.

 

[Brad]

Hans,

Here is what we are trying to do.

1. Use the Windows Scheduler to fire up a Windows Script (VBS).

2. Have this Windows Script fire up a specific SUB in an Access 2007
Database and pass in a parm that this SUB can access.

Brad

 

[Hans Up]

Hans,

Here is what we are trying to do.

1. Use the Windows Scheduler to fire up a Windows Script (VBS).

2. Have this Windows Script fire up a specific SUB in an Access 2007
Database and pass in a parm that this SUB can access.

Is it the same SUB every time? Or do you need to call it with one SUB
one time and a different SUB the next time?

 

[Brad]

Hans,

Same Sub each time, but the Parameter that we would like to pass from the
Windows Script to this Sub in Access may have a different value each time.

Thanks!

Brad

 

[Hans Up]

Hans,

Same Sub each time, but the Parameter that we would like to pass from the
Windows Script to this Sub in Access may have a different value each time.

OK, the same Sub each time would make it simpler. But while I was
waiting I tried an approach inspired by Tom's suggestion to offer more
flexibility.

You need something which fires when the database starts to accept the
parameter you're feeding with the /cmd switch. In my first try, I used
my form's open event because I already had that set up and I have little
experience with macros.

You could use an autoexec macro, but I chose to create a macro I called
"mcrStartController" and trigger it with the /x command line switch.

The macro consists of a single RunCode line, and the Function Name box
contains Controller()

Essentially all the Controller function does is break the strings out
from the Command() function and feed parameters to the appropriate sub:

Public Function Controller()
Dim varArguments As Variant
Dim i As Integer
Dim strMsg As String
varArguments = Split(Command())

Select Case varArguments(0)
Case "YourSub"
YourSub varArguments(1)
Case "DoubleIt"
DoubleIt varArguments(1)
Case Else
'log this if nobody will be around for the MsgBox
strMsg = "'" & varArguments(0) & "' not usable"
MsgBox strMsg
End Select
End Function

And here are two subs which can be called from Controller:

Public Sub YourSub(ByVal pstrVbsParam)
Dim strMsg As String
strMsg = "Hello " & pstrVbsParam
MsgBox strMsg
End Sub

Public Sub DoubleIt(ByVal pstrNumber As Double)
MsgBox pstrNumber & " * 2 = " & CStr(Val(pstrNumber) * 2)
End Sub

And this is the VBS which starts everything:

Dim objShell
Dim strExe
Dim strDb
Dim strParam
Dim strMacro

Set objShell = WScript.CreateObject("WScript.Shell")
strExe = "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
strDb = "C:\Access\wip\version_control\vc.mdb"
strMacro = "mcrStartController"

'strParam = "YourSub World"
strParam = "DoubleIt 5.2"
'strParam = "Deliberate failure here"

ObjShell.exec(strExe & " " & strDb & " /x " & strMacro & _
" /cmd " & strParam)
Set ObjShell = Nothing

It seems kind of fiddly, but it works. I couldn't see a simpler route
to get there. In your case, since you're dealing with only one sub, you
can simplify this thing.

I'm curious how you will feed it different parameters each time. Will
you modify the VBS script to change parameters values, or have you
worked out a slick alternative?

 

[Brad]

Hans,

Thanks for the help and the great examples.

We haven't figured everything out yet, but I believe that we are one step
closer.

We are in the process of automating the running of several reports during
"off hours".

Your assistance will help us put the foundation pieces in place.

Thanks again,

Brad

 

[Steve]

Brad,

One way you can do this is to use Windows Scheduler to open your database
file at a preset time. Then in your database have an autoexec macro that
runs your reports. You could even have a separate database with linked
tables and just your reports. The purpose of this database would be to just
run your off hours reports.

Steve
(e-mail address removed)

 

[Hans Up]

We are in the process of automating the running of several reports during
"off hours".

Hey! That's what I was thinking of trying.

My plan is to store the names of the reports in rows in a job queue
table. The sub fired by the database macro would fetch those names in a
recordset, then loop through the recordset to print out each report.
That approach will avoid the need to feed a parameter to my sub from a
VBS script.

 

[Brad]

Steve and Hans,

Thanks for the great ideas. I really appreciate your help.

I like the idea of the Windows Scheduler initiating an Access DB that
contains a table of report names. The info in this table would then control
which reports are run. We are planning to automatically e-mail some reports
and/or store some reports in folders in PDF format.

The one piece that I still don't quite understand is how to use the Access
DB that has the "report" table to link to a second database that has the
table and reports. I understand how a person can link to tables, but I don't
understanding linking to reports that are in a second Database. I must be
missing something.

Thanks,
Brad

 

[John W. Vinson]

The one piece that I still don't quite understand is how to use the Access
DB that has the "report" table to link to a second database that has the
table and reports. I understand how a person can link to tables, but I don't
understanding linking to reports that are in a second Database. I must be
missing something.

I'd certainly suggest using a split database (tables in a shared backend,
forms, reports, queries and code in a frontend on each user's desk), quite
independent of this automation issue. See
http://www.granite.ab.ca/access/splitapp.htm or
http://allenbrowne.com/ser-01.html for details.

However, it's probably not necessary to have the report table in a separate
database from the reports themselves. You could either use a separate,
dedicated frontend just for the batch job, or even include the table of
reports and the code in your general-purpose frontend, and have it activated
with a command line switch from the Scheduler, as suggested earlier in this
thread.

 

[Steve]

Hi Brad,

You don't need a table of report names! Just put a copy of the reports you
want to run off hours in your off hours database. Put any queries you use
for record sources for the reports in the off hours database. Link to all
tables you use in the queries and/or in the reports. Then all you need is
code to run all the reports in the off hours database.

Steve

 

[Hans Up]

Steve and Hans,

Thanks for the great ideas. I really appreciate your help.

I like the idea of the Windows Scheduler initiating an Access DB that
contains a table of report names. The info in this table would then control
which reports are run. We are planning to automatically e-mail some reports
and/or store some reports in folders in PDF format.

You can use an autoexec macro, as suggested elsewhere, to kick off your
operation. Just remember if anyone opens the database without holding
down the shift key, the reports will start spitting out. That is the
reason I suggested using a separate macro and including its name after
the /x switch.

Sounds like your version of a "WhichReports" table might benefit from an
additional field for output destination: email; PDF; paper. Or maybe
put the output destinations in a related table if one report may ever
have more than one destination. I was thinking about something simpler
.... just a field for report name, and a Yes/No field to indicate whether
that report should be included in the next scheduled print cycle.

The one piece that I still don't quite understand is how to use the Access
DB that has the "report" table to link to a second database that has the
table and reports. I understand how a person can link to tables, but I don't
understanding linking to reports that are in a second Database. I must be
missing something.

The way I imagined it is the db started by Windows Scheduler contains
both the WhichReports table and the actual report objects. If you
absolutely need them in separate databases, consider linking
WhichReports into the database which contains the reports and have the
scheduler start the database which contains the reports.

Regards,
Hans

 

[Brad]

Steve,

Thanks!

Brad

Hi Brad,

You don't need a table of report names! Just put a copy of the reports you
want to run off hours in your off hours database. Put any queries you use
for record sources for the reports in the off hours database. Link to all
tables you use in the queries and/or in the reports. Then all you need is
code to run all the reports in the off hours database.

Steve





.

 

[david]

Rem Create a app object:

set app=createobject("access.application")

app.opencurrentdatabase(strdb)

Rem Run function with parameters:

app.run(myPublicFunction,1,"a")


(david)