Any way to run an Access VBA Subroutine/Function/Method from within Excel VBA

Any way to run an Access VBA Subroutine/Function/Method from within Excel VBA

Is there any way to call an Access VBA subroutine, function or Class Method from within an Excel VBA routine?

In Access, by using Tools, References, I can add-in subs, functions, classes from other  databases, but when in Excel, Outlook, etc. VBA, I'm unable to jump across applications to invoke references to Access databases.

Many thanks in advance if anyone knows a way I can run a program in an Access database from within Excel?

As written, Excel and Access need to be in the same directory.
Access need not be open.

In Excel:-

Option Explicit


Private Sub cmdCallAccessSub_Click()
    Dim strDBName  As String
    Dim strMessage As String
   
    strDBName = ThisWorkbook.Path & "\" & "TestAccess_A2003.mdb"
   
    With CreateObject("Access.Application")
        .OpenCurrentDatabase strDBName
        .Run "Invoke", "CalledFromExcel", strMessage
        .Quit
    End With
   
    MsgBox strMessage

End Sub


In Access:-

Option Explicit
Option Compare Text


Public Sub Invoke(ByVal strSubName As String, _
                  ByRef strReturnMessage As String)
   
    Dim strReturn As String
   
    On Error Resume Next
   
    Application.Run strSubName, strReturn
   
    If (Err.Number) Then
        strReturnMessage = Err.Description
        Err.Clear
    Else
        strReturnMessage = strReturn
    End If
   
End Sub


Public Sub CalledFromExcel(ByRef strReturn As String)
   
    On Error Resume Next

    strReturn = "Returned message from Access."
   
End Sub

Copyright © 2007-2012 www.chuibin.com Chuibin Copyright