<

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

Published on
4,566 Points
966 Views
1 Endorsement
Last Modified:
Jim Dettman (Microsoft MVP/ EE MVE)
Independent consultant specializing in the writing of custom packages for businesses.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.

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

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

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

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

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
 
1
Comment
1 Comment
LVL 50

Expert Comment

by:Dale Fye
Jim,

Great article.  I use a similar process in a procedure I use for reading from or writing to Excel.  The problem is that occassionally, the user might want to read from or write to a workbook which is already open, and if the user has more than one instances of Excel open it is vertually impossible to select the correct version.  With a method very similar to this, I am able to identify all available instances of Excel and allow the user to select the appropriate instance based on the workbook(s) opened in those instances.

Dale
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Join & Write a Comment

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month