[AccessD] Run a routine in another mdb

Tony,

    It seems this thread, initiated vide your post of 26-Jun-2010, has been 
awaiting further progress.

    You wish to run a subroutine belonging to an external db which is 
already in open state. It is seen that if this task is attempted via office 
automation, there is a tendency towards landing into a hung state, if 
external db happens to be already in open state. On the other hand, the 
desired objective can be realized by setting up a temporary library 
reference to the external db on the fly.

    Sample subroutine named P_RunProcInExternalDb_A(), located in general 
module of local db, as given below, establishes a temporary reference to 
external db, runs the sample subroutine P_TestMsg_A() belonging to external 
db (as given below) and thereafter removes the temporary reference. For sake 
of illustration, four arguments of different data types (text, number, date, 
boolean) are passed to the external proc.

    For a similar task, sample subroutine named P_RunProcInExternalDb_B() in 
local db as given below, demonstrates a more generic approach. In this case, 
the name of target subroutine in external db is passed as the first 
argument, while the arguments (for external proc) if any, are passed as the 
second argument (optional one), in the form of a semicolon separated string. 
Correspondingly, the subroutine in external db has to extract individual 
elements from the string listing the arguments, as shown in sample 
subroutine named P_TestMsg_B() given below. Typical call for using this 
approach would be as follows (for illustration, elements of argument list 
represent four different data types i.e. text, number, date, boolean):

' Sample calling code in form's module
'===================================
Private Sub Cmd_B_Click()
    P_RunProcInExternalDb_B _
        "P_TestMsg_B", "Survey;50;" & _
        Format(Date + 90, "dd-mmm-yyyy") & ";True"
End Sub
'===================================

    Note:
    Application.Run method has been used as it allows the target procedure 
name to be placed in a string, thereby avoiding compile error that would 
otherwise be encountered on account of non-recognition of library reference 
qualifier for external db (as it is not a permanent reference).

Best wishes,
A.D. Tejpal
------------

' Sample code in general module of LOCAL db
' (For purpose of this sample, file External.mdb
' is located adjacent local db in the same folder)
'================================
' Declarations section
Public Const ExternalDbName As String = "External"
Public Const ExternalDbExtn As String = ".mdb"
'------------------------------------------------

Sub P_RunProcInExternalDb_A()
    On Error Resume Next
    Dim FilePath As String

    FilePath = CurrentProject.Path & _
                    "\" & ExternalDbName & _
                    ExternalDbExtn

    ' Remove any existing library reference
    ' bearing this name
    Application.References.Remove _
        Application.References(ExternalDbName)

    ' Set up fresh library reference as per
    ' specified file path
    Application.References.AddFromFile FilePath

    ' Run the desired procedure belonging to
    ' external db
    Application.Run ExternalDbName & _
            ".P_TestMsg_A", "Survey", "50", _
            Format(Date + 90, "dd-mmm-yyyy"), _
            "True"

    ' Remove the above library reference
    Application.References.Remove _
        Application.References(ExternalDbName)

    On Error GoTo 0
End Sub
'------------------------------------------------

Sub P_RunProcInExternalDb_B( _
                ExternalProcName As String, _
                Optional ArgumentList As String)
    ' ArgumentList is a semicolon (;) separated
    ' list of arguments for external procedure.
    On Error Resume Next
    Dim FilePath As String

    FilePath = CurrentProject.Path & _
                    "\" & ExternalDbName & _
                    ExternalDbExtn

    ' Remove any existing library reference
    ' bearing this name
    Application.References.Remove _
        Application.References(ExternalDbName)

    ' Set up fresh library reference as per
    ' specified file path
    Application.References.AddFromFile FilePath

    ' Run the desired procedure belonging to
    ' external db
    Application.Run ExternalDbName & _
            "." & ExternalProcName, ArgumentList

    ' Remove the above library reference
    Application.References.Remove _
        Application.References(ExternalDbName)

    On Error GoTo 0
End Sub
'===================================


' Sample code in general module of EXTERNAL db
'===================================
Sub P_TestMsg_A(Optional Task As String = "", _
            Optional Points As Long = 0, _
            Optional TargetDt As Variant, _
            Optional HasPriority As Boolean = False)
    Dim Msg As String

    Msg = "Today: " & Format(Date, "dd-mmm-yyyy")

    If Len(Task) > 0 Then
        Msg = Msg & vbCrLf & "Task: " & Task
    End If

    If Points > 0 Then
        Msg = Msg & vbCrLf & "Points: " & Points
    End If

    If Not IsMissing(TargetDt) Then
        Msg = Msg & vbCrLf & "Target Dt: " & TargetDt
    End If

    If HasPriority = True Then
        Msg = Msg & vbCrLf & "This Task Has Priority"
    End If

    MsgBox Msg
End Sub
'-------------------------------------------------------

Sub P_TestMsg_B( _
                Optional ArgumentList As String = "")
    ' ArgumenList is a semicolon (;) separated
    ' list of arguments
    Dim Msg As String, Rtv As Variant

    Msg = "Today: " & Format(Date, "dd-mmm-yyyy")

    If Len(ArgumentList) > 0 Then
        Rtv = Split(ArgumentList, ";")

        If Len(Trim(Rtv(0))) > 0 Then
            Msg = Msg & vbCrLf & "Task: " & Trim(Rtv(0))
        End If

        If UBound(Rtv) > 0 Then
            If Val(Trim(Rtv(1))) > 0 Then
                Msg = Msg & vbCrLf & "Points: " & Trim(Rtv(1))
            End If
        End If

        If UBound(Rtv) > 1 Then
            If IsDate(Trim(Rtv(2))) Then
                Msg = Msg & vbCrLf & "Target Dt: " & _
                                                        CDate(Trim(Rtv(2)))
            End If
        End If

        If UBound(Rtv) > 2 Then
            If Eval(Trim(Rtv(3))) <> 0 Then
                Msg = Msg & vbCrLf & _
                                    "This Task Has Priority"
            End If
        End If
    End If

    MsgBox Msg
End Sub

'=================================

----- Original Message ----- 
From: Tony Septav
To: Access Developers discussion and problem solving
Sent: Saturday, June 26, 2010 20:43
Subject: [AccessD] Run a routine in another mdb

Hey All

Is it possible to run a routine in another mdb that is already open???
Can't seem to find the correct syntax.

Thanks