Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Independent consultant specializing in the writing of custom packages for businesses.
Published:
Browse All Articles > Obtaining an object variable for a running instance of Microsoft Access.
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 LoopExit_Procedure:End Sub
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:
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 LoopExit_Procedure:End Sub
Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Independent consultant specializing in the writing of custom packages for businesses.
Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
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
Have a question about something in this article?
You can receive help directly from the article author.
Sign up for a free trial to get started.
Comments (1)
Commented:
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