Run function / sub in different accdb

Hi all,

I need to, via VBA, call a sub in a different database (accdb). The sub has two optional parameters.

At the moment I've been using the following code, however this doesn't work in Access 2010 runtime (see Line "Set appAccess = CreateObject("Access.Application")" generates error 429  [ActiveX component can't create object]

What other method can I use to call an external function/sub with optional variables?

Many thanks!


Public Function fCallForeignProcedure(sDBPath As String, sProc As String, Optional sArgs1 As String, Optional sArgs2 As String)

Dim appAccess As Access.Application

' Create instance of Access Application object.
Set appAccess = CreateObject("Access.Application")

' Open MyDatabaseName.mdb database in Microsoft Access window.
appAccess.OpenCurrentDatabase sDBPath, False
'False so as not to open in exclusive mode
' Run Sub procedure.
Select Case Len(sArgs1)
  Case Is = 0
    appAccess.Run sProc
  Case Is > 0
    Select Case Len(sArgs2)
      Case Is = 0
        appAccess.Run sProc, sArgs1
      Case Is > 0
        appAccess.Run sProc, sArgs1, sArgs2
    End Select
End Select
Set appAccess = Nothing
End Function

Open in new window

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.

Jeffrey CoachmanMIS LiasonCommented:
You can run code from another db if you set a reference to that DB in your VBA window
(Click "Browse", and select the database type, ...then navigate to the db)

Then call the function as if it were in your db

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
katerina-pAuthor Commented:
Thank you! Can't believe I didn't know that!
Jeffrey CoachmanMIS LiasonCommented:
I sometimes forget too

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
Microsoft Access

From novice to tech pro — start learning today.