Can someone provide an example of how I can run VBA functions residing in an
Access Database?

Thanks in advance
--
Jeff C
Live Well .. Be Happy In All You Do

Jeff C schrieb:

> Can someone provide an example of how I can run VBA functions residing in an
> Access Database?
>
> Thanks in advance

Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
Dim sMFSpec : sMFSpec = oFS.GetAbsolutePathName( ".\activ.mdb" )
Dim oAccess : Set oAccess = CreateObject( "Access.Application" )

oAccess.OpenCurrentDatabase sMFSpec
oAccess.Visible = True
oAccess.DoCmd.RunMacro "macVBS"
WScript.StdIn.ReadLine
oAccess.Quit

--
Jeff C
Live Well .. Be Happy In All You Do


"ekkehard.horner" wrote:

> Jeff C schrieb:

> > Can someone provide an example of how I can run VBA functions residing in an
> > Access Database?
> >
> > Thanks in advance

>
> Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
> Dim sMFSpec : sMFSpec = oFS.GetAbsolutePathName( ".\activ.mdb" )
> Dim oAccess : Set oAccess = CreateObject( "Access.Application" )
>
> oAccess.OpenCurrentDatabase sMFSpec
> oAccess.Visible = True
> oAccess.DoCmd.RunMacro "macVBS"
> WScript.StdIn.ReadLine
> oAccess.Quit
>

Wonderful - Thanks very much!!

ekkehard.horner schrieb:

> Jeff C schrieb:

>> Can someone provide an example of how I can run VBA functions residing
>> in an Access Database?
>>
>> Thanks in advance

>
> Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
> Dim sMFSpec : sMFSpec = oFS.GetAbsolutePathName( ".\activ.mdb" )
> Dim oAccess : Set oAccess = CreateObject( "Access.Application" )
>
> oAccess.OpenCurrentDatabase sMFSpec
> oAccess.Visible = True
> oAccess.DoCmd.RunMacro "macVBS"
> WScript.StdIn.ReadLine
> oAccess.Quit

Hi Jeff,

[Answering here, because my newsreader can't cite your answer]

you are wellcome. In addition I just found

http://www.members.shaw.ca/AlbertKal...obs/Index.html

So

oAccess.Run "callSubFromVBS"

works also. This method avoids the roundabout way via the Macro
facility.

What I should have posted earlier:

' in active.mdb
' Macro: macVBS - "ExecCode callMacroFromVBS()"
' mdlMain:
' Public Function callMacroFromVBS()
' MsgBox "HereI am", , "callMacroFromVBS"
' callMacroFromVBS = Empty
' End Function
' Public Sub callSubFromVBS()
' MsgBox "HereI am", , "callSubFromVBS"
' End Sub

What I would like to know: Is it possible to call functions and/or
to pass parameters?

ekkehard.horner wrote:

> you are wellcome. In addition I just found
>
> http://www.members.shaw.ca/AlbertKal...obs/Index.html
>
> So
>
> oAccess.Run "callSubFromVBS"
>
> works also. This method avoids the roundabout way via the Macro
> facility.
>
> What I would like to know: Is it possible to call functions and/or
> to pass parameters?

Yes, the Run method accepts parameters and can call either subs or
functions (similar to Call in vbscript). It does not seem to be able to
retrieve values returned from the function. I'm not sure how it handles
ByRef arguments.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Bob Barrows [MVP] schrieb:

> ekkehard.horner wrote:

[...]

>> What I would like to know: Is it possible to call functions and/or
>> to pass parameters?

>
> Yes, the Run method accepts parameters and can call either subs or
> functions (similar to Call in vbscript). It does not seem to be able to
> retrieve values returned from the function. I'm not sure how it handles
> ByRef arguments.
>

Thanks! As to the ByRef:

Inserting this

Dim sName : sName = "Bill"
WScript.Echo "Before", sName
oAccess.Run "SayThankYou", sName
WScript.Echo "After ", sName

into the VBScript code and this

' Public Function SayThankYou(ByRef sName)
' sName = "Bob"
' MsgBox "Thank you, " & sName
' SayThankYou = "welcome!"
' End Function

into the Access module, gave me the *appropriate/correct* MsgBox
and the output

=== callAccessMacro: call access macro ==
Before Bill
After Bob

=== callAccessMacro: 0 done (00:00:19) ==

So you can let Access work for VBScript and get results via ByRef params.

Hi there

I've been looking for a topic like this one over the net on the last 2 weeks, I hope here I'll find what I need.

I have the same problem.. I'm trying to call a function that is on an access module from an vbs script outside, but I need to pass parameters. (actually on this order: long, string, int). And I really can't figure out how to do that.. can anyone please post an example for me?


Thanks a lot!!!