Record locking - i'm REALLY confused!

[MarkWaddington]

Hi,

I have a multiuser database set-up using Access 2K. I have split the database so the data resides on a network drive, and the application is on each user's computer.

I have tried to implement record-level locking but I cannot get it to work.

The main form in my database which will be accessed regularly is the "client" form, many users may be using this at one time.

I have checked/enabled the following options:

Tools/Options/Default record locking: Edited Record
Tools/Options: "Open Databases using record level locking"

I have also set the "record locks" property on my client form to edited record.

The scenario which I want to prevent is as follows:

User A logs on to the database, searches for a client record, opens the record and begins to edit it.

User B logs on, goes to the exact same form, opens the same record edits it (quickly) and saves the changes.

User A attempts to save his/her record and gets told of the fact somebody else has been to that record and changed it before him/her.

I don't want User B to be able to edit a record that is already open by another user. Is this possible?

Thanks in advance.

Mark Waddington.

 

[nberryman]

I will start by asking the obvious, have you set up record locking on the Back End database or the Front Ends?

It's the Back End that need this doing.

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[MarkWaddington]

Hi,

It's done on the backend.

I found that if I make a new record, then close it and open it again, that does lock the record, but only if you start making changes to the data.

I would like it so a second user would never see a record that is open by another user.

Do you know if this is possible?

Thanks.

M. Waddington.

 

[nberryman]

I can't think how to do it but that doesn't mean it can't be done through VB.

Try posting a question on the Access VBA Modules Forum some thing like How do I hide a record being edited.

Their brains are far superior to mine

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[VeeFour]

I also have this identical problem and am also really confused because I have followed the same procedures.

The main problem is one user overwriting another users data on the same record, I have also tried implementing the security features by allocating usernames/passwords but now I am unable to repair and compact the database also.

Can anyone point me in the right direction?

 

[VeeFour]

I also have this identical problem and am also really confused because I have followed the same procedures.

The main problem is one user overwriting another users data on the same record, I have also tried implementing the security features by allocating usernames/passwords but now I am unable to repair and compact the database also.

Can anyone point me in the right direction?

 

[allanon]

We also had a similar problem and we got around that in a fairly easy if not obvious way. We have a system where we have dozens of related tables and we did not want information updated in any of these tables for a client where a different user was already editing that users data.

We created a manual lock table. When a user enters the edit screen for a client we ensure:
(a) if there are any exsisting lock records for that user they are deleted.
(b) if there is an exsisting lock record for that client the user is not allowed to enter the edit screen and edit that client.

Anyway, I am not saying that this is the best idea. It is just an idea that worked for us.

If no lock record is found then we write one out and then the user can be in it as long as they like with no fear of someone else changing it.

There are a couple of concerns doing it this way. Mostly, how do you get rid of a lock record when a user leaves. If they leave the edit screen by pressing the close button then no problem, just delete there record. If the user presses 'X' to get out or locks the app and uses cntl-alt-del then we do not delete the record. You do run a batch job each night that clears this lock table.

 

[BillPower]

Hi allanon,

I use an almost identical locking sysyem to yours.

If you move the procedure you've got behind the Close button to the Form's unload event, unless there is a crash of some kind, the lock record will always be deleted, which ever way the user closes your app.

To allow for a system or local crash, I have a Date/Time field which records when the record is first locked. When a user attempts to edit a record, the procedure, firstly checks to see if it's locked, if it's not, ok lock it, let the user edit the record. If it is locked, the user can't edit the record, a message is displayed, giving the name of the user currently locking the record and how long they've been locking it. It becomes apparent that there's a problem, if a record that on average might say, take 5 minutes to edit has been locked for an hour. The user can then take appropriate action, notify the other user or administrator, etc.

Hopefully, this might give you an idea or two.

All the best.

Bill

 

[cavery]

Can this be used in a Unbound form? I have similar issues, but resulted in using a timer that would detect idle users. I like this idea better because it actually prevents users from editing the same record. Any idea of how to implement with Unbound forms would help...

Thanks

Clark

 

[KenReay]

Hi

See:

http://msdn.microsoft.com/library/d.../en-us/odeopg/html/deovrmultiusersettings.asp

it might help Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK