Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unlocking Records (Access 2K)

    I am working on a DB App that has a centrally located data MDB and an app that runs on the clients' machines. The forms turn on record level locking. Two requests have been made that I'm not sure are possible to implement. First, if a record is locked during an update, is it possible to report to other users who locked it? Second, if a person begins updating a record, locking it for other users and then leaves for some reason, is it possible to a privileged user to unlock it, abandoning changes made by the missing user? This also brings up another question in my mind: what happens if a user has a record locked and the client machine or the server crashes? Does the record remain locked in either case?

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Unlocking Records (Access 2K)

    Hi Bill,
    A couple of things to check - are you actually using record-level locking or is it page locking? There's an Advanced option at the database level that should be checked in order to use record-level locking. Also, what locking option has been selected on the form? We nearly always use No Locks (also referred to as optimistic locking), but there are two other options, Edited Record and All Records. The first locks a record the minute some starts an edit, rather than waiting until it tries to save the record, while the second locks the record when it is being viewed. Either of those options tends to give you more locking conflicts than we like to see.

    As to reporting who locked a record, if another user attempts to edit a record while someone has it locked, they get a message that says the user name and the computer name. If you have security active it works fairly well. Unfortunately,l if some one walks off and leaves their workstation in the middle of an edit, I don't believe you can do anything other than go to that workstation and deal with it first hand. However crashes do seem to be dealt with in a reasonable fashion - at least the worst situation we normally see outside of corruption from crashes is a need to delete the .ldb file to allow a user to take exclusive access to a database so it can be compacted and repaired. You might want to take a look at <!mskb=208778>Microsoft Knowledge Base Article 208778<!/mskb> which lays out some of the details about the .ldb file.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unlocking Records (Access 2K)

    Wendell, thanks for the response, it helps quite a bit.

    I checked my work and I have "Open databases using record-level locking" checked and the "Default record locking" set to "No locks". I'm assuming that each client machine would need to have these settings, right?

    The forms are currently set to "No Locks". Now, the requirements from the customer state that if a user is editing a record, no other user should be allowed to edit the same record. From this I'm assuming that I'd want to set the forms to "Edited Record", right? But you stated that these settings produce "more locking conflicts than we like to see". Can you recommend another approach that would satisfy the requirement, but work more smoothly? When a user saves their edits to a record (moves to another record, for example) does Access unlock it?

    Again thanks for the info.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Unlocking Records (Access 2K)

    <hr>Can you recommend another approach that would satisfy the requirement, but work more smoothly?<hr>
    If the customer insists that a record must be locked from the instant that editing begins on a record, then you will probably have to use "Edited Record" and explain that they will get an error message each time someone tries to do that sort of thing. On the other hand, if a user starts an edit and then walks away for an hour, there may be issues with that situation too. We prefer the scenario where User A starts an edit, walks away, and User B changes the record in the mean time. When User A gets back to finish their edit, they get a message that the record has been changed and their changes cannot be saved.

    With Edited Record locking, you could have some sort of timer running on your form, and have it check to see how long a record has been in an edit mode (check the dirtly property of the form), and after a certain period, cancel the update, but that involves a fair bit of overhead if you have lots of forms or they are complex. If you do have to switch to Edited Record, are you going to prevent users from working in tables? If not, you probably want to change the default locking as well, since that's how edits at the table level would be handled.
    <hr>When a user saves their edits to a record (moves to another record, for example) does Access unlock it?<hr>
    Yes - it does release the lock if you cancel the update, or when you save the record, either by moving to another record, or by closing the form.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •