Obtaining an object variable for a running instance of Microsoft Access.

The problem:

Recently while answering a question on Experts Exchange, I had suggested that the asker split their Access app into two parts.    It was a calendaring application and had a calendar/scheduling form that was very resource intensive.

Because of this and that the calendar form was almost always open, users would often receive "resources exceeded" error messages at random when using the app (Access has a few internal constraints that for the most part are never a problem, but on occasion they are).    With no work around for this and given the functionality within the app, it was very easy to split it into two apps.   One contained the calendar form and its procedures, and the other contained everything else. While this solved the resource error messages, it presented some other problems.

First, the asker wanted these two parts to function seamlessly from the users viewpoint.  So it was set up that a user could start Part A or Part B first.   The other part was fired up in code when needed.  When this was done, it was easy to grab an object reference because it was the code creating the instance of Access.  This is what that code looked like:
 

Private Sub Command1_Click()

    Call SwitchToAppMarkedAs("App2", "C:\Temp\myApp2.accdb")
    oAccessRemoteApp.Run "DisplayMessage", "This is app 2 from app 1.  Hello there"
    
End Sub

Select all Open in new window

Public Sub SwitchToAppMarkedAs(strAppMarkedAs As String, strAppPathAndFileName As String)

  Dim bolFound As Boolean
  Dim lngHWND As Long
  Dim iid As GUID
  Dim obj As Object
  Dim lngReturn As Long

  ' Minimize the current window
  ShowWindow Application.hWndAccessApp, SW_SHOWMINIMIZED
  
  ' Find if the app is loaded, and if not, start it
  If AppIsLoaded(strAppMarkedAs) = False Then
    ' Start the application - we can use shell or automation.  I'll use automation
    Set oAccessRemoteApp = CreateObject("Access.Application")
    oAccessRemoteApp.UserControl = True
    oAccessRemoteApp.OpenCurrentDatabase (strAppPathAndFileName)

    ' Optionally run a macro or do something else here.
    ' oAccessRemoteApp.Run "<macro name>"
  End If

  ' Now switch to the app.
  ' Find the existing instance(it may have already been running) and switch to it.
  bolFound = False

  Do While bolFound = False

    lngHWND = GetWindow(GetDesktopWindow(), GW_HWNDChild)

    Do While lngHWND > 0
      If GetProp(lngHWND, strAppMarkedAs) = 1 Then

        BringWindowToTop (lngHWND)
        lngReturn = ShowWindow(lngHWND, 3)
        
        bolFound = True
        lngHWND = 0
      Else
        lngHWND = GetWindow(lngHWND, GW_HWNDNEXT)
      End If
    Loop

    DoEvents

  Loop

Exit_Procedure:

End Sub

Select all Open in new window

If you look at the above code snippet for SwitchToAppMarkedAs(), you'll see in the first part we determine if the app is already running.   We do that by looking for a window with a specific property (see https://www.experts-exchange.com/articles/2104/Avoiding-running-multiple-instances-of-an-application.html for more detail on how this is done).

If not, then we create an instance of Access and end up with the object reference of oAccessRemoteApp.    We then switch to the correct window by enumerating through all the open windows, locating the correct one, and switching to it.  
 

---

Sidebar:
Some of you may be wondering why the procedure is structured the way it is given that when creating the instance, we could get the windows handle easily and then switch to it immediately without enumerating through all the open windows.  There are two reasons for this; the first being that this also had to deal with cases where the second part is already running and would not be creating an instance.  The second reason is that I'm re-creating this example from what the finial code is, so it doesn't make total sense at the moment. Still however, even the final result is inefficient structured the way it is.  But it allowed the code to be a little more concise in the end while covering both possibilities (second part running or not) and being a little more robust in case your user does something unexpected.  

---

This allowed him to run code in the second part from the first (see second line of the OnClick of the command button), BUT he also wanted to make calls from the second part back into the first.   In other words if Part A was started first and Part B second, he wanted to make code calls from Part B in A.

Problem was, Part A was already running.   So the question was; is it possible to get an object variable for a running instance of Access?

Digging around:

First, I had looked around to see if anyone had done this previously and discovered that no one had.  When an instance of anything is running and you locate it via Windows API's, the main piece of information your left with is a window handle.

I hadn't worked with COM at a low level previous to this, but it seemed like there should be some method of finding the IDispatch interface within a process if it had one.   So I dug some more and came up with a Windows API called AccessibleObjectFromWindow.   It sounded like what I was looking for and after reading through the docs:

https://msdn.microsoft.com/en-us/library/windows/desktop/dd317978(v=vs.85).aspx

this seemed to be it!  But after playing with it for a bit, I was more than a little lost on exactly how to use it.   

So round two and back to the net.   After digging for a bit longer, I un-earthed a post that showed how this was done with Excel, but there was nothing related to Access.   Figuring it was going to take some work to get there, I got a fresh cup of coffee and got ready to dig in.   But with the Excel example in hand, it really wasn't too hard to get there and in a short while, it was a done deal.

 

The Result:

So how do you get the object reference?   Turns out, all you need are two calls:
 

         ' Make sure we have an object reference to it.
        If oAccessRemoteApp Is Nothing Then
          Call IIDFromString(StrPtr(IID_IDispatch), iid)
          If AccessibleObjectFromWindow(lngHWND, OBJID_NATIVEOM, iid, obj) = S_OK Then
            Set oAccessRemoteApp = obj
          End If
        End If

Select all Open in new window

It did take some digging however to find the CLSID's for the interfaces (IDispatch and IUnknown) in Access.  But outside of that, it was very straight forward.   The finial code then ends up looking like this:
 

Public Sub SwitchToAppMarkedAs(strAppMarkedAs As String, strAppPathAndFileName As String)

  Dim bolFound As Boolean
  Dim lngHWND As Long
  Dim iid As GUID
  Dim obj As Object
  Dim lngReturn As Long

  ' Minimize the current window
  ShowWindow Application.hWndAccessApp, SW_SHOWMINIMIZED
  
  ' Find if the app is loaded, and if not, start it
  If AppIsLoaded(strAppMarkedAs) = False Then
    ' Start the application - we can use shell or automation.  I'll use automation
    Set oAccessRemoteApp = CreateObject("Access.Application")
    oAccessRemoteApp.UserControl = True
    oAccessRemoteApp.OpenCurrentDatabase (strAppPathAndFileName)

    ' Optionally run a macro or do something else here.
    ' oAccessRemoteApp.Run "<macro name>"
  End If

  ' Now switch to the app.
  ' Find the existing instance(it may have already been running), make sure
  ' we have a reference to it, and switch to it.
  bolFound = False

  Do While bolFound = False

    lngHWND = GetWindow(GetDesktopWindow(), GW_HWNDChild)

    Do While lngHWND > 0
      If GetProp(lngHWND, strAppMarkedAs) = 1 Then
         ' Make sure we have an object reference to it.
        If oAccessRemoteApp Is Nothing Then
          Call IIDFromString(StrPtr(IID_IDispatch), iid)
          If AccessibleObjectFromWindow(lngHWND, OBJID_NATIVEOM, iid, obj) = S_OK Then
            Set oAccessRemoteApp = obj
          End If
        End If
    
        BringWindowToTop (lngHWND)
        lngReturn = ShowWindow(lngHWND, 3)
        
        bolFound = True
        lngHWND = 0
      Else
        lngHWND = GetWindow(lngHWND, GW_HWNDNEXT)
      End If
    Loop

    DoEvents

  Loop

Exit_Procedure:

End Sub

Select all Open in new window

 Summary:

I still always find it amazing that with VBA you can delve into the Windows API and pull of what you would think at first is impossible.   

But with the above, you now have the option to breakup an app into multiple parts, each being able to call procedures in the other.

A simple sample application is attached demonstrating how this works between two parts.

Enjoy!

Jim Dettman

SwitchApps2.zip