Programmatically switch back and forth between two access databases


I have a new scenario where I need to have ready access to one database from another. It's easy enough to use the shell command to launch the other database, but the problem is that if you keep clicking the button the launch the other database, you'll get multiple copies running at once. I've attached a pair of sample databases that demonstrate this.

What needs to happen on clicking the button is:

* Check for existing instances of the app to launch
* If it exists, bring it to the front
* If it doesn't, launch it.

I've got a rough idea of how windows are referred to using the API commands, which I've setup in the example databases, but when I haven't yet been able to put together a stable and consistent method of doing what I need to do.

Can anybody please help to make this work?

Thanks in advance!
Jon JaquesInformation TechnologistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use a couple of API calls to do this:

FindWindow - this returns the Windows Handle (a Long value) if it finds a "window" that matches the arguments you pass in

BringWindowToTop - this sets focus to that window and brings it to the top of the other windows.


Here's the modified code for Database A:

Option Compare Database
' Module    : Form_Form1
' Author    : Jon
' Date      : 9/10/2015 11:28
' Purpose   :
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function BringWindowToTop Lib "user32" _
 (ByVal lngHWnd As Long) As Long
 Function apicFindWindow(strClassName As String, strWindowName As String) As Long
  'Get window handle.
  Dim lngWnd As Long
  apicFindWindow = FindWindow(strClassName, strWindowName)
End Function

' Procedure : cmdLaunch_Click
' Author    : Jon
' Date      : 9/10/2015 11:28
' Purpose   :
Private Sub cmdLaunch_Click()
    On Error GoTo cmdLaunch_Click_Error

    Dim hwnd As Long

    hwnd = apicFindWindow("OMain", "Database B")

    If hwnd <> 0 Then
        BringWindowToTop hwnd
        Dim strPath As String, strTool As String
        Dim intTaskID As Double
        strPath = CurrentProject.Path & "\dbb.accdb"
        strTool = "MSAccess.exe """ & strPath & """"
        intTaskID = Shell(strTool, vbNormalFocus)
    End If
    On Error GoTo 0
    Exit Sub


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdLaunch_Click of VBA Document Form_Form1"
End Sub

Open in new window

In the above code, the call to FindWindow returns a non-zero value if it finds the window with OMain as the "class" (which is always the same for Access), and with a title of "Database B". Note the second argument must EXACTLY match the window name - so if you pass in "Database - B", the call would fail.

The code to return focus to DatabaseA would be similar, except you would change the call to apicFindWindow to refer to the name of that application:

hwnd = apicFindWindow("OMain", "Database A - Launcher")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jon JaquesInformation TechnologistAuthor Commented:
Awesome, thank you very much, nice and clean!!! I guess my efforts weren't too far off, but I had a few things wrong that prevented it from working, and you got them right.

Much appreciated!
Jon JaquesInformation TechnologistAuthor Commented:
Just in case anybody would like to see the completed solution, I'm attaching it here.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.