How can I know if a particular Access db is already open

  • Thread starter Thread starter Willard Baker via AccessMonster.com
  • Start date Start date
W

Willard Baker via AccessMonster.com

I have a frontend/backend system at a non-profit. Users entering intake
interview info are not always computer savvy. I frequently find that they
have opened the frontend more than once....even 4 times once. I'd like to
be able to prevent this. I've found some logic that will prevent the
opening of any other Access db, but some users use more than one Access
system and I do not want them to have to close their other systems when
they open mine. Is there a way vbwise to see if a particular .mdb is
already open?
 
Willard said:
I have a frontend/backend system at a non-profit. Users entering
intake interview info are not always computer savvy. I frequently
find that they have opened the frontend more than once....even 4
times once. I'd like to be able to prevent this. I've found some
logic that will prevent the opening of any other Access db, but some
users use more than one Access system and I do not want them to have
to close their other systems when they open mine. Is there a way
vbwise to see if a particular .mdb is already open?
Click to expand...

Best practice for this is to create a mutex using the according
Windows APIs CreateMutex, OpenMutex and ReleaseMutex.

In a standard module:
-----------------------------------------
Option Explicit

'Put your own unique application-name here
Public Const APPLICATION_NAME As String = "TestAppMutexTest"

Private Const READ_CONTROL = &H20000

Private Type SECURITY_ATTRIBUTES
nLength As Long
lpSecurityDescriptor As Long
bInheritHandle As Long
End Type

Private Declare Function CreateMutex Lib "kernel32" Alias "CreateMutexA" ( _
lpMutexAttributes As SECURITY_ATTRIBUTES, _
ByVal bInitialOwner As Long, _
ByVal lpName As String) As Long

Private Declare Function OpenMutex Lib "kernel32" Alias "OpenMutexA" ( _
ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal lpName As String) As Long

Private Declare Function ReleaseMutex Lib "kernel32" ( _
ByVal hMutex As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hHandle As Long) As Long

Public ApplicationMutex As Long

Public Function MutexCreate() As Boolean

Dim lhandle As Long
Dim SecAttrib As SECURITY_ATTRIBUTES
'-------------------------
With SecAttrib
.nLength = Len(SecAttrib)
.lpSecurityDescriptor = 0&
.bInheritHandle = 0&
End With

lhandle = OpenMutex(READ_CONTROL, 0&, APPLICATION_NAME)

If lhandle > 0 Then
CloseHandle lhandle
MutexCreate = False
Else
ApplicationMutex = CreateMutex(SecAttrib, 1&, APPLICATION_NAME)
MutexCreate = (ApplicationMutex > 0)
End If

End Function

Public Sub MutexRelease()

If ApplicationMutex > 0 Then
ReleaseMutex ApplicationMutex
CloseHandle ApplicationMutex
ApplicationMutex = 0
End If

End Sub
-----------------------------------------

in a start form:

-----------------------------------------
Option Explicit

Private Sub Form_Open(Cancel As Integer)

If MutexCreate() = False Then
MsgBox "Sorry, the Applikation '" & APPLICATION_NAME & "' is already
running.", _
vbExclamation, APPLICATION_NAME
DoCmd.Quit acQuitSaveAll
End If

End Sub

Private Sub Form_Close()

MutexRelease

End Sub
 
This is very similar to the logic I already have. The problem is that the
application name is 'Microsoft Access'. It's too broad to capture a unique
db. For example, say that one user uses two Access db's.....one for
inventory and another for addresses. If I do not want him/her to open the
inventory db a second time and insert your logic into that db, it means
that he/she will not only have to close the open inventory db, but the
addresses db as well. I do not want him/her to have to close the addresses
db in order to open the inventory db.
 
Willard said:
The problem is
that the application name is 'Microsoft Access'. It's too broad to
capture a unique db.
Click to expand...

Why is it 'Microsoft Access'?
It's too broad to write something like
Public Const APPLICATION_NAME As String = "MyInventoryDB"
in the one and
Public Const APPLICATION_NAME As String = "MyAdressDB"
in the other?

For example, say that one user uses two Access
db's.....one for inventory and another for addresses. If I do not
want him/her to open the inventory db a second time and insert your
logic into that db, it means that he/she will not only have to close
the open inventory db, but the addresses db as well. I do not want
him/her to have to close the addresses db in order to open the
inventory db.
Click to expand...

If the constant APPLICATION_NAME is not the same,
the user will not have to close the other db.

Try it!

HTH
 
Sorry....I confused APPLICATION_NAME with application.name.

This works great with my mde db's. In development, of course, I use an
mdb. I think the reason it works with mde's is that before I create the
mde (in Access 2000), I do Tools/Startup and uncheck Display Database
Window, Allow Built-in Toolbars and Allow Toolbar/Menu Changes.


However, it doesn't work with the mdb with the above items checked. After
I've opened the mdb and, then, try to open it again hoping to get a message
saying it was already open, the autoexec doesn't even get performed in a
new opening (I put a msgbox as the first step to make certain) and the db
opens at the database window of the already open mdb. And I cannot exit
from it unless I SetWarnings True.
 
Back
Top