• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1256
  • Last Modified:

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 http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28245671.html#a39512929). 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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now