Prevent users from opening multiple instances of access
Quite often we want to only allow the user to open the frontend file one and ONLY one time. It could be that some functionality simple doesn’t work if the frontend is opened multiple times on the same PC. Access can sometimes handle it, and sometimes not. Therefore the recommended approach is to have each user have his own copy of the frontend. But that doesn’t help if the user is opening it multiple times!
This problem has been around for ages, and Graham Mandeno came up with a solution which is posted on the Access MVP site: Check Multiple Instances of Access, but sadly that doesn’t work in Access 2007 or newer, because of some changes to the way the Access windows are structured. So I came up with the code you can see below:
Private Declare Function apiIsIconic Lib "user32" Alias "IsIconic" (ByVal hwnd As Long) As Long
Private Declare Function apiShowWindowAsync Lib "user32" Alias "ShowWindowAsync" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Boolean
Private Const sw_Restore As Long = 9
Private Const sw_Show As Long = 5
Private Sub Form_Timer()
DoCmd.Close acForm, Me.Name
Public Sub CheckMultipleInstances()
Dim appAccess As Access.Application
Set appAccess = GetObject(CurrentProject.FullName)
If appAccess.hWndAccessApp = Me.Application.hWndAccessApp Then
'Same instance. Proceed
MsgBox "You allready have an instance of " & CurrentProject.Name & " running"
'Active the other access app
'Clear reference to it
Set appAccess = Nothing
'Close down this app
Set appAccess = Nothing
Public Sub ActivateAccessApp(hWndApp As Long)
If apiIsIconic(hWndApp) Then
apiShowWindowAsync hWndApp, sw_Restore
apiShowWindowAsync hWndApp, sw_Show
How to use it
Now while I was testing this I noticed a few things. First of all, it seems that as long as the frontend file is a accdb, that it cannot be opened more than once. If I try to open it again, access automatically set focus to the all ready opened accdb file. Atleast that is the behavior I am seeing in Access 2010 on my machine. So in that case, the code isn’t really required. But when I compile the frontend to a accde, its perfectly possible to open the frontend many times, in seperate accesss windows. But with the code above, we can now detect if thats the case, set focus to the all ready open/active application and quit the latest instance being opened.
There is a small thing to notice. The code seems to only work if the database is “fully open”. To overcome this, I placed a splash screen which opens first, added a timer to the splash screen, and at the end of the timer interval, I run the code, and if no other apps are open, I proceed to close the splash screen, and open my login form.
I’ve added both an accdb file (Which should be 2007 compatible, but I haven’t tested) and a demo accde file, which is only 2010/2013 compatible.