In a recent post I was asked about running Macros and Code in external databases.

I quickly pointed to my article:

but then realized I never posted my code for running VBA procedures in another Access database.

So let’s correct that oversight!
 

Running VBA Subs & Functions in Another Microsoft Access Database

The process of running a Macro or VBA code in an external database is quite similar, with the main difference being the actual execution line.

Here’s a function that demonstrates how to open another database and run a Sub or Function, even returning the value in case of a Function:

'---------------------------------------------------------------------------------------
' Procedure : ExecuteExternalProcedure
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Run a VBA Procedure (Sub, Function) in an external database
'               Be VERY careful with routines that might trigger dialogs, msgbox, inputbox,...!
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb           : Fully qualified path and file name with extension of the external database
'                 that you want to run a Procedure from
' sProcedureName: Name of the Procedure to run
' sDbPassword   : Password required to open the database (if applicable)
'                   If a password is required and not supplied, the customary Access popup
'                   will appear.
'
' Usage:
' ~~~~~~
' ExecuteExternalProcedure("C:\Users\Dev\Desktop\Test.accdb", "Testing01")
'   Returns -> 4
'
' ExecuteExternalProcedure("C:\Users\Dev\Desktop\Test2.accdb", "Testing02", "Hi")
'   Returns -> 4
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2014-09-16                  Initial Release
' 2         2025-01-23                  Updated Copyright, Refs, Error_Handler for
'                                       blog release
'---------------------------------------------------------------------------------------
Public Function ExecuteExternalProcedure(sDb As String, _
                                         sProcedureName As String, _
                                         Optional sDbPassword As String) As Variant
    Dim oAccess               As Object 'New Access.Application

    On Error GoTo Error_Handler

    Set oAccess = CreateObject("Access.Application")
    If sDbPassword = "" Then
        oAccess.OpenCurrentDatabase sDb
    Else
        oAccess.OpenCurrentDatabase sDb, , sDbPassword
    End If
    ExecuteExternalProcedure = oAccess.Run(sProcedureName)

Error_Handler_Exit:
    On Error Resume Next
    oAccess.CloseCurrentDatabase
    oAccess.Quit
    Set oAccess = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: ExecuteExternalProcedure" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Be sure to ensure that the procedure you call have proper error handling!

Also, if the procedures involve dialogs, msgbox, inputbox, … they will be initiated, but often are not brought to the forefront, so look at you taskbar to find where they are.