Running an Access Procedure from excel. Procedure takes parameters.

[ChrisOswald]

Hi,

I think this just a syntax question, but I couldn't figure it out via trial and error.

In excel, I'm attempting to run a sub in an Access .mdb. The access .mdb is declared as follows:

Sub DiscReportingMain(DistNbrStr As String, PeriodStr As String, YearStr As String)
'with much vba afterwards, some of which requires maintaining work.

in Excel, I've tried calling this with

Sub TestAccessrun()
    Dim apAcc                       As Object
    Dim db                          As Object
    Set apAcc = CreateObject("Access.application")
    apAcc.opencurrentdatabase ("C:\Documents and Settings\xxxxxxx\My Documents\" _
                               & "Distributor Reporting\Refund Report.mdb")
 
 
    'attempt one, doesn't work
    apAcc.Run "DiscReportingMain(""056609149"",""02"",""2011"")"
    'attempt two, doesn't work
    apAcc.Run "DiscReportingMain ""056609149"",""02"",""2011"""
    'attempt three, doesn't work
    apAcc.Run "call DiscReportingMain(""056609149"",""02"",""2011"")"
    'attempt four, doesn't work
    apAcc.Run "call DiscReportingMain ""056609149"",""02"",""2011"""
    'this parameterless sub does work  (TestIt just throws up a msgbox)
    apAcc.Run "TestIt"
    apAcc.Quit
    Set apAcc = Nothing
End Sub

All the not working attempts throw run-time error '2517':
Microsoft Office Access con't find the Procedure '<WHATEVER in. put I string the>'

I also tried testing it on a couple of variations using the module name that the procedure resides in: this approach also broke apAcc.run "ModTest.TestIt"

At the moment, the only thing I can think of (which is too bizarre to be an appropriate method) is to write the parameters to a little table in the database, and call a parameterless sub that reads those values back and calls DiscReportingMain with them.

 

[ChrisOswald]

Err, umm. Figured it out via help in access VBA (I guess I should expand my research techniques past "Google It", "Press buttons until it works")

apAcc.Run "DiscReportingMain", "056609149", "02", "2011"

works.