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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Willard Baker - 29 Mar 2005 19:57 GMT

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?

Reply to this Message

Jörg Ackermann - 29 Mar 2005 22:14 GMT

> I have a frontend/backend system at a non-profit. Users entering
> intake interview info are not always computer savvy. I frequently
[quoted text clipped - 4 lines]
> to close their other systems when they open mine. Is there a way
> vbwise to see if a particular .mdb is already open?

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
-----------------------------------------

start this form hidden in autoexec-macro

HTH

Signature

Jörg Ackermann Microsoft MVP Access, Germany
German Access FAQ: www.donkarl.com

Reply to this Message

Willard Baker - 30 Mar 2005 05:21 GMT

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.

Reply to this Message

Jörg Ackermann - 30 Mar 2005 08:37 GMT

> The problem is
> that the application name is 'Microsoft Access'. It's too broad to
> capture a unique db.

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
[quoted text clipped - 3 lines]
> him/her to have to close the addresses db in order to open the
> inventory db.

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

Try it!

HTH

Signature

Jörg Ackermann Microsoft MVP Access, Germany
German Access FAQ: www.donkarl.com

Reply to this Message

Willard Baker - 30 Mar 2005 17:09 GMT

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.

Reply to this Message