Obtaining an object variable for a running instance of Microsoft Access.
Published on
4,871 Points
Jim Dettman (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
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
If you look at the above code snippet for SwitchToAppMarkedAs(),
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
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
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
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
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.