In the end I found someone called TheSmileyCoder asking the same question as me on another forum. With a bit of detective work found him on here called, you've guessed it, the TheSmileyCoder.
I asked him if he'd found a solution to the problem he posed a few years ago and he had and just got around to posting it on his blog,
http://thesmileycoder.com/stop-or-check-multiple-instances-of-access-on-startup/ , less than a week ago!
The code didn't quite work for me but after a quick tweak it did. I got rid of Private Sub Form_Timer(), changed Public Sub CheckMultipleInstances() into a function, put it into a module, and called it (=CheckMultipleInstances()) from autoexec. I also had to change the DoCmd.Quit to Application.Quit. The code that works for me is..
'*************************************
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
'
Public Function CheckMultipleInstances()
Dim appAccess As Access.Application
Set appAccess = GetObject(CurrentProject.FullName)
If appAccess.hWndAccessApp = Application.hWndAccessApp Then
'Same instance. Proceed
Else
MsgBox "You allready have an instance of " & CurrentProject.Name & " running"
'Active the other access app
ActivateAccessApp appAccess.hWndAccessApp
'Clear reference to it
Set appAccess = Nothing
'Close down this app
' DoCmd.Quit
mblnClose = True
Application.Quit
End If
Set appAccess = Nothing
End Function
Public Sub ActivateAccessApp(hWndApp As Long)
If apiIsIconic(hWndApp) Then
apiShowWindowAsync hWndApp, sw_Restore
Else
apiShowWindowAsync hWndApp, sw_Show
End If
End Sub
Thanks TheSmileyCoder!!
