Locking a file when another user has it open

[Guest]

Hi everyone,

I have an Access database that will be accessed by multiple people over a
network. When someone opens the file it creates a lock file (lbd). What I
thought this should do is then either prevent others from opening the file or
just allow them to open it Read Only (can’t remember which but I would prefer
the Read Only option). The problem at the moment is that more than one person
can open the access file and edit the records. This doesn’t seem to change no
matter what I select under “Default Open Mode†or “Default Record Lockingâ€
under Tools/Options/Advanced Tab. What’s strange is that we have a mailing
database which seems to be set up in pretty much the same fashion (as far as
I can tell or the person who created it) that use top give a “In Use Errorâ€
when it was opened by someone else. Now that seems to allow multiple users.
I’m not sure if this has any impact but we just changed over two of the three
computers that access the database to XP were as before only one computer was
using XP and the other two were using 2000. Any thoughts or suggestions would
be great and highly appreciated.

Mfranz

 

[Joseph Meehan]

Hi everyone,

I have an Access database that will be accessed by multiple people
over a network. When someone opens the file it creates a lock file
(lbd). What I thought this should do is then either prevent others
from opening the file or just allow them to open it Read Only (can't
remember which but I would prefer the Read Only option). The problem
at the moment is that more than one person can open the access file
and edit the records. This doesn't seem to change no matter what I
select under "Default Open Mode" or "Default Record Locking" under
Tools/Options/Advanced Tab.

There are a couple of locking settings, but I believe what you are
seeing is that Access is allowing the first change, but if someone else
tries to change data on the same "page" and they accessed the data before
the first user saved the changes, it will notify the second user of the
possible problem when they go to save their change.

< What's strange is that we have a mailing

database which seems to be set up in pretty much the same fashion (as
far as I can tell or the person who created it) that use top give a
"In Use Error" when it was opened by someone else. Now that seems to
allow multiple users. I'm not sure if this has any impact but we just
changed over two of the three computers that access the database to
XP were as before only one computer was using XP and the other two
were using 2000. Any thoughts or suggestions would be great and
highly appreciated.

I suspect access right to the director where the access file is located
has changed with the change in systems

BTW are you using a "split" system; that is a copy of a front end
Access file on each user's computer with a back end Access file located on
the server. The back end should have only data in tables; all the forms
reports queries etc should be on the front ends.

 

[Guest]

A locking database file does not prevent other users from opening the
database. It's intent is to prevent two users from editing the same record at
the same time. I'm not sure why you would even want to do this in a
multi-user application. The idea, generally, is that all users should be able
to access the database simultaneously. Here is a KB article where you can
read more about the role of the .ldb file:

Introduction to .ldb Files
http://support.microsoft.com/?id=299373


You can change the default open mode to Exclusive, or you can create a
shortcut that includes the /excl switch in the target, but I'm really not
sure why you would want to restrict write capability to one user at a time.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Joseph,

Thanks for your help. I do get the notification that you talk about, which
would be fine but it’s not smart enough to keep both sets of info (assuming
there in separate fields). How do I set the access database up so that if
someone is editing a record that one record cannot be edited by others or if
someone is editing the database everyone else can only view it Read Only? I’m
not running a split system. Just now is the first time I’m hearing about it.
Do I need to run something like this in order to do what I want to do? It
seems like the problem might have something to do with XP since we just had
someone running 2000 try and open the file and they were only able to view it
in Read Only mode because I already had it running. Thanks in advance for
your help.

MFranz

 

[Guest]

Hi Tom,

Thanks for your email and the link. If I could get the locked database to
prevent other users from editing the same record that would perfectly,
unfortunately that does not seem to happen. All that seems to happen is that
one person makes a change and moves on, then when the other person tries to
apply there change they get a message saying that the record had already been
edited and they could make their change, discard their change or copy and
view the other persons change. This would be fine but the program doesn’t
seem smart enough to preserve both individuals changes (as long as they are
in two separate fields). Is there a way to prevent any editing to a record
that is already being edited by someone else? It almost seems like the
problem might be with XP as well, since we just had someone running 2000 try
and open the file and they were only able to view it in Read Only mode
because I already had it running. I’m running Access 2000 if that information
helps. Thanks in advance for your help.

Mfranz

 

[Joseph Meehan]

Hi Joseph,

Thanks for your help. I do get the notification that you talk about,
which would be fine but it's not smart enough to keep both sets of
info (assuming there in separate fields).

I believe it is intentional. Often you would make different
modifications of a record if you knew someone had already made changes. For
example, I you were editing John Smith's date of birth, it would not be good
for Access to allow you to save that information without you knowing that
someone had changed the name to Sam Smith.

How do I set the access
database up so that if someone is editing a record that one record
cannot be edited by others or if someone is editing the database
everyone else can only view it Read Only?

I will leave that to someone else. I have never required that.

I'm not running a split
system. Just now is the first time I'm hearing about it. Do I need to
run something like this in order to do what I want to do?

No, splitting the database will not help out with this, at least not
directly, but it will greatly reduce the chance of corrupting your database
and will allow for much better performance. It is easy to do and there is a
wizard to get you started. I suggest you work with a copy of your database
first and read up on the procedure before starting.

 

[Guest]

This would be fine but the program doesn’t seem smart enough to preserve

both individuals changes (as long as they are in two separate fields).

Access 2000 and later versions support row-level locking. It does not,
however, support field-level locking. Joseph provided a good example for you
of why this would not be a good thing: "For example, (if) you were editing
John Smith's date of birth, it would not be good for Access to allow you to
save that information without you knowing that someone had changed the name
to Sam Smith."

You could accomplish what you want with unbound (stateless) forms. However,
then the second person's changes would completely overwrite any changes saved
by the first person.

Is there a way to prevent any editing to a record
that is already being edited by someone else?

Not that I'm immediatly aware of. I can't say that this issue comes up with
any frequency in the databases that I'm involved with. I suppose if your
database had only a very limited number of records, the chances of collisions
would be increased. It would probably be possible to write code that
accomplished this, but it's just not been a problem for me, so I've never
looked into it.

To prevent the Read only mode, when different versions of Access are
involved, you *must* split the database into a front-end (FE) and a back-end
(BE) file. A copy of the FE is installed on each user's local hard drive. The
BE, which contains only the tables with the shared data, is placed on the
file server.

I believe the issue that you are running into is one of references. If you
open Access 2000 and view the checked library references, you will see that
the first two listed are:
Visual Basic for Applications and
Microsoft Access 9.0 Object Library

If you open the same database in Access 2002, the second reference will read:
Microsoft Access 10.0 Object Library

and, in Access 2003, this reference will read:
Microsoft Access 11.0 Object Library

When a user opens your unsplit database with one version of Access, the
reference to the Access Object Library is automajically set to the correct
version. Along comes user # 2 with a different version of Access. Oops, can't
automajically reset this reference, cause the file is already open by another
user and that user requires a different version of the object library. You
get a read only copy.

When each user has their own copy of the FE on their own local hard drive,
these collisions do not occur. Each copy is totally independent of other
user's copies.

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Joseph Meehan]

..
When a user opens your unsplit database with one version of Access,
the reference to the Access Object Library is automajically set to
the correct version. Along comes user # 2 with a different version of
Access. Oops, can't automajically reset this reference, cause the
file is already open by another user and that user requires a
different version of the object library. You get a read only copy.

When each user has their own copy of the FE on their own local hard
drive, these collisions do not occur. Each copy is totally
independent of other user's copies.

That was interesting. Makes sense. It also points out how much more
you know about Access than I.

 

[Guest]

Hi Tom,

You say that Access 2000 and later support row level locking, does that
mean if someone is editing a record on a form then that one record should be
uneditable to anyone else? The problem is that Access doesn’t seem to realize
that I have the locking environment set to Edited Records only. The message I
get is the one that says you can overwrite the other user's changes to the
record, copying your version of the record to the Clipboard, or discarding
your changes, which seems to mean that Access considers the locking
environment to be set to No Locks as stated in the Access help menu. Is there
something that needs to be done in order for the database to register my
choice in Options/Advanced/Default Record Locking? Thank you again for all
your help on this.

MFranz

 

[Guest]

Hi Joseph,

Any ideas on the below question I sent to Tom. Thanks again for your help on
this.

MFranz

Question:
You say that Access 2000 and later support row level locking, does that
mean if someone is editing a record on a form then that one record should be
uneditable to anyone else? The problem is that Access doesn’t seem to realize
that I have the locking environment set to Edited Records only. The message I
get is the one that says you can overwrite the other user's changes to the
record, copying your version of the record to the Clipboard, or discarding
your changes, which seems to mean that Access considers the locking
environment to be set to No Locks as stated in the Access help menu. Is there
something that needs to be done in order for the database to register my
choice in Options/Advanced/Default Record Locking? Thank you again for all
your help on this.

 

[Guest]

You say that Access 2000 and later support row level locking, does that

mean if someone is editing a record on a form then that one record should be
uneditable to anyone else?

Page or Row level locking does *not* prevent other users from attempting to
edit a record that has been dirtied by user # 1 (a record is dirtied if you
see the pencil symbol, as long as you have the Record Selectors property set
to Yes). However, the locking will cause the notification message that you
see to be displayed.

The difference between Page level locking and Row level locking is that row
level locking only locks a single row, as long as the request to use row
level locking was honored. Please refer to this link for more information
about row level locking:
http://www.microsoft.com/office/com...cess&mid=8a357217-b1b8-491a-875c-8f0d30d0da69

Page level locking (Access 97 and earlier) locks a page of records when a
given record is edited. The number of records that fits into a page depends
on the version of JET, and the size of the records.

The page size was doubled for JET 4 (4 KB = 4096 bytes), which is what
Access 2000, 2002 and 2003 use, when compared to the page size of JET 3.5x (2
KB = 2048 bytes), which is what Access 97 used. This change was made due to
the adding Unicode functionality to JET 4, which requires two bytes instead
of one byte for each character.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom,

Thanks again for all your help on this. I think I finally figured out why
my row level locking wasn’t working. It looks like I just needed to make sure
the row level locking check box was checked in each of the table’s properties
windows. Once I did that everything started to work fine. I figured it would
be something as simple as that, it was just a matter of finding it especially
since the help menu really doesn’t mention it (at least the parts I looked
at). Anyways thank you again for all the help you provided, I actually
stumbled across when I was looking for something I had read in one of the
links you had sent.

MFranz

 

[Guest]

Hi Joseph,

Finally figured out what was going on. The row level check box was not
checked off in the properties window of each of my tables. Once I did that
everything started to work as I though it should. Thanks again for your help
in this.

MFranz

 

[Guest]

Once I did that everything started to work fine.

Very curious result. The reason I say this is that the option you described
is "Row Level Tracking" (in Access 2002). If you make this the active
checkbox (by clicking it in the properties dialog), and then pressing the F1
key, you should open context sensitive Help for this topic. The topic deals
only with replicated databases. Row level tracking is not the same as Row
level locking.

I actually stumbled across when I was looking for something I had read
in one of the links you had sent.

Can you provide a reference, so that I can take a look for myself?


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom,

Sorry about that, I meant to say "Row Level Tracking". Well for whatever
reason the database finally started to recognize when someone is editing a
record verse before when it would allow both users to make changes and then
pop up that helpful but not very useful warning window (keep changes, discard
changes, view changes).

I’m not sure exactly how I found the item, I just remember I was checking
out the following sites when for some reason I decided to right click over a
database and check it’s properties. I originally went to
http://www.microsoft.com/office/com...cess&mid=8a357217-b1b8-491a-875c-8f0d30d0da69
then from there I went to
http://support.microsoft.com/?id=306435
then I went to
http://msdn.microsoft.com/library/d...deovrpagelevellockingvsrecordlevellocking.asp
where I then went to The Locking Information File under System Components
and Multiuser Settings in the table of contents. From there I did a search
for Understanding Microsoft Jet Locking," by Kevin Collins. I did find an
interesting article at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/repltypes_9obr.asp
about Row Level Tracking and Column Level Tracking. Sorry I can’t more
precisely define what lead me to Row Level Tracking. If you come across any
additional information I would level to her about it or if you have any other
thoughts on why clicking that check box caused the Row Level Locking to start
working.

MFranz