Can't break exclusive access

[Leif]

My understanding of Access locking is that the first user
to connect Access is given exclusive access (if they have
that permission). Once other users attempt to connect the
exclusive lock is changed to a shared lock (assuming they
are not doing anything that requires exclusive access).

The problem I have is that when the system admin. person
logs in first, other users may not log in. In other
words, the exclusive lock is not be changed to a shared
lock. The message the second user sees is "The database
has been placed in a state by user 'username' on
machine 'machinename' that prevents it from being opened
or locked." where username and machine will be specific
to the system admin. person.

The system admin. person is not doing any activity that
would require the exclusive lock to be maintained (such as
a compress/repair, or design work).

Does anyone know the reason this happens and how it can be
solved? Thanks.

Regards,
Leif

 

[Joan Wild]

My understanding of Access locking is that the first user
to connect Access is given exclusive access (if they have
that permission). Once other users attempt to connect the
exclusive lock is changed to a shared lock (assuming they
are not doing anything that requires exclusive access).

No that isn't correct. If the database is set to shared, and the user isn't
opening it exclusively via a shortcut switch, or choosing File, Open,
Exclusive, then the first person in does not open it exclusively.

The problem I have is that when the system admin. person
logs in first, other users may not log in. In other
words, the exclusive lock is not be changed to a shared
lock. The message the second user sees is "The database
has been placed in a state by user 'username' on
machine 'machinename' that prevents it from being opened
or locked." where username and machine will be specific
to the system admin. person.

This will happen if the first person in doesn't have full permission on the
folder where the mdb is. All users need full permission in order to create
the associated ldb file. Without it, then the ldb can't be created, and
they *will* open it exclusively.

 

[Leif]

Joan,

Thanks for your reply.

What I've discovered is that the problem is the result of
changing a menu entry. On login I have all users execute
the line:

CommandBars("TBSMenu").Controls("Tools").Controls
(2).Caption = "Redirect ON"

Apparently this change is considered a design mode change,
so an exclusive lock is placed on the database. It is not
removed until the user exits. The interesting thing is
that when the above command is used with multiple users in
the database, it still accepts the command, even though it
cannot change to exclusive mode!

Regards,
Leif

 

[Joan Wild]

The exclusive lock is set (or not) first thing. It won't change as a result
of executing code.

Why are you running that code? Why not just set the caption of the menu
item?

 

[Leif]

My experience is different from your comment. It does
appear to me that an exclusive lock is set through code
(not directly through some function, but by doing a menu
caption update). If a user, with exclusive access rights,
enters the database first, the database will get an
exclusive lock. If, on the other hand, a person without
exclusive lock permissions enters the database first, an
exclusive lock is not set. I've verified this by
commenting out the CommandBars statement in which case I
don't experience the problem.

I use that particular menu entry as a toggle. If my admin
user clicks the menu entry it toggles between Redirect ON
and Redirect OFF. During certain activities my code reads
the menu caption to check the setting, and execute code
accordingly. Prior to adding the code I got a complaint
that if the user toggles to OFF, and then exits, the menu
comes up as OFF on the next session. So, throught VB
code, I'm trying to force the setting to ON at the
beginning of the session.

My solution was to move the code to a hidden form that
executes the code prior to the user exiting Access. That
seems to be working. However, I've warned my user, that
if the she toggles the menu entry, and she is the only
user in the database, that she will acquire an exclusive
lock.

On your second comment, you say "Why not just set the
caption of the menu item". I thought that was what I was
doing with the CommandBars statement. Can you tell me how
to just set the caption (through code)? Perhaps that will
avoid the problem entirely.

Regards,
Leif

 

[Joan Wild]

My solution was to move the code to a hidden form that
executes the code prior to the user exiting Access. That
seems to be working. However, I've warned my user, that
if the she toggles the menu entry, and she is the only
user in the database, that she will acquire an exclusive
lock.

On your second comment, you say "Why not just set the
caption of the menu item". I thought that was what I was
doing with the CommandBars statement. Can you tell me how
to just set the caption (through code)? Perhaps that will
avoid the problem entirely.

You are setting it in code; I was suggesting that you customize the TBSMenu
and set the caption. But you in fact are toggling the caption, so that
wouldn't work.

Since you have it working via a hidden form, then go with it.

You should consider splitting the database and giving each user their own
copy of the frontend.

 

[Leif]

I've tried splitting the database, however, I noticed a
very significant performance penalty. I even tried some
suggestions I've read, such as opening and keeping open a
connection to the BE, but performance was still poor.
Perhaps you can point me to other ideas to deal with the
performance hit.

Regards,
Leif

 

[Joan Wild]

Here's a good site regarding performance:
http://www.granite.ab.ca/access/performancefaq.htm