How to call an Access Module function from VB?

What's the best way to call a function in an Access Module from a VB Application.  Using Access97,  I converted an existing macro into a Module using Access's "ConvertMacrosToVisualBasic" menu option.  Have been trying to use a "Database" object in VB to connect to the .mdb file and access the function that I want to call.  No luck.  Not that familiar with VBA and could use a suggestion.  Thanks,
JonJon
jonjonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jonjonAuthor Commented:
Adjusted points to 200
0
chewhoungCommented:
Try this:
 
'*************************************************************************** ' RUN MODULE CODE
'*************************************************************************** Private Sub Run_Module_Code()
    Dim accApplication As Application
 
    On Error GoTo WHOOPS
    Set accApplication = CreateObject("Access.Application.8")     Call accApplication.OpenCurrentDatabase("C:\My Documents\test.mdb")     Call accApplication.Run("My_Module_Code")
 
ERROR_EXIT:
    On Error Resume Next
    Call accApplication.CloseCurrentDatabase
    Set accApplication = Nothing
    Exit Sub
WHOOPS:
    Call MsgBox(Err.Description, vbCritical)
    Resume ERROR_EXIT
End Sub
'**********************************************************************
 
You should make sure you have a reference to the MS Access 8.0 Object Library in the references dialog (get there from the Project menu).
 
Note that you can have additional arguments if your module requires them. They go after the module name in the .Run command:
 
  Call accApplication.Run("My_Module_Code", arg1, arg2, arg3)
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jonjonAuthor Commented:
Excellent!  That was exactly what I was looking for.  Thank You.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.