Prev: Access Not Responding
Next: Microsoft Office Spreadsheet 10.0
From: Brad on 21 Apr 2010 15:52
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" wrote:

> Brad wrote:
> > 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.
> .
>
From: Hans Up on 21 Apr 2010 16:13
Brad wrote:
> 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?
From: Brad on 21 Apr 2010 18:10
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" wrote:

> Brad wrote:
> > 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?
> .
>
From: Hans Up on 21 Apr 2010 20:19
Brad wrote:
> 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?
From: Brad on 22 Apr 2010 13:37
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


"Hans Up" wrote:

> Brad wrote:
> > 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?
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Access Not Responding
Next: Microsoft Office Spreadsheet 10.0