Hi Emilio,
See the article titled "Avoid Multiple Instances of an Access Application" in the November, 2003
issue of Access-VB-SQL Advisor Magazine. If you send me a private e-mail message, with a valid
e-mail address, I will send you a copy of a database that I created using the code sample from
this article. Please do not post your e-mail address in a reply to this newsgroup (unless you
want to start receiving loads of spam).
I have not looked at the sample available at MVPS, but an outline for how my sample works is
shown below.
Tom
1.) Use the word "Startup" (without the quotes) as the application title
(Tools > Startup...)
2.) Call either OpenOnlyOneCopyVersion1 or OpenOnlyOneCopyVersion2 via an Autoexec macro or using
a startup form's load event procedure to call either function. For example, a form designated as
the startup form could include the following code:
Private Sub Form_Load()
OpenOnlyOneCopyVersion1
' or
OpenOnlyOneCopyVersion2
End Sub
3.) Create a new module and paste the code shown below. Watch for possible line wrap from the
newsreader.
Option Compare Database
Option Explicit
' Public Declare Function apiFindWindow Lib "User32" _
Alias "FindWindowA" ( _
ByVal strClass As String, ByVal lpWindow As Any)
'
' Note: There appears to be an error in the declaration shown above
' in the article. It is missing the return value "As Long", which
' results in Run-Time error '49' / Bad DLL calling convention. I
' used the shown declaration below, as indicated in KB 210115
' (
http://support.microsoft.com/?id=210115)
'
Public Declare Function apiFindWindow Lib "User32" Alias "FindWindowA" _
(ByVal lpclassname As Any, ByVal lpCaption As Any) As Long
Public Declare Function apiSetWindowText Lib "User32" Alias "SetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String) As Long
' *****************************************
' The following declaration, including the two constants, is called only by the function
' "OpenOnlyOneCopyVersion2", as suggested by Magdy Kallini (see below)
Public Declare Function apiShowWindow Lib "User32" Alias "ShowWindow" _
(ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Const SW_SHOWNORMAL = 1
Const SW_MAXIMIZE = 3
' *****************************************
'
Public Function FindWindow(strCaption As String) As Long
Dim lReturn As Long
lReturn = apiFindWindow(vbNullString, strCaption)
FindWindow = lReturn
'FindWindow = CBool(lReturn > 0)
End Function
Public Function SetWindowText(hwnd As Long, strCaption As String) As Boolean
Dim lReturn As Long
lReturn = apiSetWindowText(hwnd, strCaption)
SetWindowText = CBool(lReturn = 0)
End Function
Public Function OpenOnlyOneCopyVersion1()
' Check for a running instance. Note: Specify a temporary application title, under Tools >
Startup...
' For this sample database, I have used "Startup" as the application title. This title is
changed using an
' API call to "Running".
Dim lngRunningHWnd As Long
Dim lngCurrentHwnd As Long
lngRunningHWnd = FindWindow("Running")
If lngRunningHWnd = 0 Then ' There is no other instance of this database running, so change
the current
' instance's title to the word "Running" (or whatever
you want--you should pick
' a title that is likely to be unique among a user's
collection of databases).
' The following assumes you have used "Startup" as the hard-coded application title
lngCurrentHwnd = FindWindow("Startup")
'Call SetWindowText(0, "Running")
Call SetWindowText(lngCurrentHwnd, "Running")
Else
' Another instance of Access is running! Notify the user and quit.
MsgBox "Application already running", vbCritical, "This Copy Will Be Closed..."
Access.Application.Quit
End If
End Function
Public Function OpenOnlyOneCopyVersion2()
' This is the version submitted by Magdy Kallini, of Chicago, which replaces the Msgbox in the
above
' version with an API call that sets focus to the first copy automatically.
Dim lngRunningHWnd As Long
Dim lngCurrentHwnd As Long
Dim lngTemp As Long
lngRunningHWnd = FindWindow("Running")
If lngRunningHWnd = 0 Then ' There is no other instance of this database running, so change
the current
' instance's title to the word "Running" (or whatever
you want--you should pick
' a title that is likely to be unique among a user's
collection of databases).
' The following assumes you have used "Startup" as the hard-coded application title
lngCurrentHwnd = FindWindow("Startup")
'Call SetWindowText(0, "Running")
Call SetWindowText(lngCurrentHwnd, "Running")
Else
' Set focus to first instance and quit this instance.
lngTemp = apiShowWindow(lngRunningHWnd, SW_MAXIMIZE)
Access.Application.Quit
End If
End Function
The code was written for earlier versions of Access, so it may not work
correctly with A2003.
Graham Mandeno would have tested this code before he posted it but obviously
not with Access 2003. If it doesn't work as described, you will need to
understand the code / experiment / trace it and see where it doesn't work
with A2003 (unless Graham jumps in here).
--
HTH
Van T. Dinh
MVP (Access)
