Prevent database open if already open by a user

[Stephen sjw_ost]

I have a stand alone database that is not split and will not be split because
only 1 person should be in the DB at any given time. Currently, multiple
users can open the DB but I want to stop this from happening so that when,
lets say, I have the DB open and someone else tries to open it, I want them
to get a Msgbox popup that says something like
"This database is already open by "user login here". Please try again later
or contact "user login here"

When they click on OK, the DB window closes.

I have tried to use this code;

If Dir(CurrentProject.Path & "TextMsgs.ldb") <> "" _
Then
MsgBox "This database is in use by " & fOSUserName()
DoCmd.Quit
End If

but it does not work.

How can I prevent a DB from being opened if it is already open?

Thanks for any help.

 

[tina]

well, you're not giving us much detail to work with. where and when is the
code running? and what happens when it "does not work" - are you getting an
error message? or does the db simply open and function normally? have you
tried stepping through the code to verify that the correct path is being
returned, and see just what is happening as each line of code executes?

hth

 

[Stephen sjw_ost]

Sorry about that.
I have the code I presented in the "On Open" event of the start up form. I
do not get any error msgs when opening from a single PC or if the DB is
opened a second time from another PC and the DB does open normally.
By "does not work" I mean that the code is not preventing the DB from being
opened a second time.
I did step thru the code and it is getting the correct path.
When it verifies the path, I see the correct path but the ldb file name is
not included with the path. Should it be?

Not sure of any other details you may need, please let me know
Thank you for your response.

 

[John W. Vinson]

I have a stand alone database that is not split and will not be split because
only 1 person should be in the DB at any given time. Currently, multiple
users can open the DB but I want to stop this from happening so that when,
lets say, I have the DB open and someone else tries to open it, I want them
to get a Msgbox popup that says something like
"This database is already open by "user login here". Please try again later
or contact "user login here"

You can ensure that only one user can open the database at a time by setting
Tools... Options... Advanced... and setting the Default Open Mode to
Exclusive.

 

[tina]

John's post looks like an easy, hassle-free solution, Stephen. if you need
anything more, post back.

hth

 

[Tom Wickerath]

Hi Stephen,

I did step thru the code and it is getting the correct path.

I don't think so. Try issuing this command in the Immediate Window:

?(CurrentProject.Path & "TextMsgs.ldb")

Aren't you missing a backslash between the folder name and the name of your
locking database file? Also, your code would never execute the IF branch,
because it would simply never return a zero lenght string. Try the following
in the Immediate Window:

?Dir(CurrentProject.Path & "TextMsgs.ldb") <> ""

You should get a result of False returned. If you insert the missing
backslash, then the result will evaluate to True:

?Dir(CurrentProject.Path & "\TextMsgs.ldb") <> ""

Opening exclusive, as John suggests, is probably the best solution. However,
I have to ask why do you have such a business rule that only one person
should be in the database at any given time?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Stephen sjw_ost]

This doe not correct the issue. I can still open and operate the DB from
multiple PCs. The Exclusive option only prohibits change to the code and
design views of the DB, it does not prevent the DB from opening or operating.

So, if opening Exclusive does not prevent a second occurance of the DB from
opening and being used and the code I tried to use does not prevent a second
occurance of the DB from opening and being used, we are back to my original
question.

How can I prevent a database from being opened and/or used if the database
is already opened by a user?

FYI to answer Tom's question; This ability is needed for the project I am
developing because this particular database needs to be set up for one user
at a time only. Business needs.

 

[Tom Wickerath]

Hi Stephen,

I suppose you could try using Connection Control to lock other users out, as
soon as the first user opens the database. You would likely initiate this
action by calling a function from an Autoexec macro, or via code in a startup
form:

HOW TO: Use Connection Control to Prevent User Log On
at Run Time in Access 2000
http://support.microsoft.com/kb/198756

Disregard the "Access 2000" in the title of this KB article, if you are
using a later version of Access. You are going to have to run code to undo
this lock during the close operation, when the user exits the database. If a
user crashes out of your application (ie. does not close gracefully), you
might find the database locked for all users. So, you'd want to be aware of
this possibility--that all users might be locked out until you returned to
the office to undo the Connection Control.

FYI to answer Tom's question; This ability is needed for the project I am
developing because this particular database needs to be set up for one user
at a time only. Business needs.

But........why???

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Stephen sjw_ost]

Ok, so I have read thru the article and have followed the instructions to
implement the code. I am not understanding how to activate and de-activate
the passive shutdown. I know the activate request will need to be in the
startup form's on open event and the de-activate request will need to be in
the on close event but what part do I call for each event? Please help.

To answer your question more fully Tom, the database needs to be set up so
that only 1 user can have it open at any given time because i have built the
database to perform multiple functions at once. I have found that when
multiple users have the DB open, none of the code works properly. This is a
database that's single function is to provide a comma-delimited file, after
processing a downloaded table, which will be used to upload to a vender
approved site to append the information needed for business needs. Without
giving out proprietary information, this is all I can provide in way of
explaination to my request.

Thank you for your reply and help.

 

[Stephen sjw_ost]

Hi Tom,

I have found my solution.
I currently have a "collect user ID" process that records the username &
login time when the DB is opened and it records the logout time when the DB
is closed. I added 1 field to the receiving table and called it "Lock". This
field is a Yes/No filed. I then created a query that identifies the Lock
field and I then built a form based on the query. Now on the startup form, I
have the Lock form opening in a hidden mode and wrote the following code;

Private Sub Form_Load()
DoCmd.Maximize
DoCmd.OpenForm "frm_Lock", , , , , acHidden
If Form_frm_Lock.Avail_Lock.Value = "Lock" Then
MsgBox "This database is currently in use by " & fOSUserName() & vbCrLf
& _
"If you need to use this database, please contact" & vbCrLf & _
fOSUserName() & " to request they close it down for use." &
vbCrLf & _
"This database will now close" & vbCrLf & vbCrLf & _
"Thank you", vbInformation Or vbOKOnly, "DATABASE IN USE"
DoCmd.Quit
Else
CurrentDb.Execute "q_UserID_Del>=30days"
CurrentDb.Execute "q_UserID_IN"
'
Me.lblstdt.Caption = Date
Me.lblCurntUser.Caption = fOSUserName()
'
MoveWindow Access.Application.hWndAccessApp, 400, 200, 540, 375, 1
'
'Right,Down,Width,Height,Repaint
'
CommandBars.ActiveMenuBar.Enabled = False
Call Buttons(False)
End If
End Sub

Now when anyone has the DB open, if someone else tries to open the DB, the
second user will receive the msgbox and their call of the DB will be closed
with the quit command once they click OK on the msgbox.

Thank you for all of your help.

 

[Stephen sjw_ost]

I forgot to mention in my previous post that the filed Lock is updated to YES
when the startup form is open and then is updated to NO when the DB is
closed. I do this by way of the "collect user ID" queries.

Thank you

 

[Tom Wickerath]

Hi Stephen,

To answer your question more fully Tom, the database needs to be set up so
that only 1 user can have it open at any given time because i have built the
database to perform multiple functions at once. I have found that when
multiple users have the DB open, none of the code works properly.

Have you split your database into a FE (Front-end) and BE (Back-end) .mdb
files? Is each user running their own copy of the FE .mdb file from their
local hard drive, so that no two users can possibly share the same FE .mdb
file?

If you are doing stuff with temporary tables, have the temporary tables
created on-the-fly, on each users local hard drive and linked directly to the
FE .mdb file, so that no two users can cause conflicting results in a shared
temp. table. Here is a sample database that demonstrates the idea of a
temporary work database:

http://www.accessmvp.com/TWickerath/downloads/tmpwrkdb.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[John Spencer MVP]

One problem with this approach - if a user abnormally quits the database the
flag will remain set. Abnormally quit = computer crashes. You may need a
method that allows you to reset the the lock flag in certain cases.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Stephen sjw_ost]

John,

That is a good point but I have left myself a backdoor to be able to view
the design view of the file and force the Lock field to be blank which will
free up the DB and allow normal use again. Typically in a situation like
that, at least in my limited experience, the ldb file is also able to be
deleted which will help to free up the DB for use. In any case, I'll be able
to free up the DB.

Thank you for the reply