Windows Vista Forums

Run Public Function

  1. #1


    Jeff C Guest

    Run Public Function

    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

      My System SpecsSystem Spec

  2.   


  3. #2


    ekkehard.horner Guest

    Re: Run Public Function

    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

      My System SpecsSystem Spec

  4. #3


    Jeff C Guest

    Re: Run Public Function


    --
    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!!

      My System SpecsSystem Spec

  5. #4


    ekkehard.horner Guest

    Re: Run Public Function

    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?





      My System SpecsSystem Spec

  6. #5


    Bob Barrows [MVP] Guest

    Re: Run Public Function

    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.



      My System SpecsSystem Spec

  7. #6


    ekkehard.horner Guest

    Re: Run Public Function

    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.

      My System SpecsSystem Spec

  8. #7



    Newbie
    Join Date : May 2009
    Posts : 1
    Windows XP Professional 32bit
    Local Time: 04:43 AM


     

    Re: Run Public Function

    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!!!

      My System SpecsSystem Spec


Run Public Function
Similar Threads
Thread Forum
Public folder moved to another directory no longer Public Network & Sharing
Public folder not public! Vista General
Remove Public directory after disable Public Folder Sharing Vista networking & sharing
How to move public folders (public pictures etc) to a different dr Vista file management
BUG: Redirecting function contents to a file truncates function lines at the width of the console PowerShell