Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Record Not Locking (97 SR-2)

    I have a client with the database mdb file stored on their NT file server. It was being used by only one person at a time, with Open Shared, No Locks. They have recently added another user, so I set the locking to Edited Record on each PC. I then ran a test and opened the same record on both PCs. I was able to change a field to a different value on each PC! The behavior was as if No Locking was in use.
    I suspect that they do not have the Jet Engine 3.51 update. Is that relevant?

    The lone user also has had an occasional problem with the Find tool. Every so often an Access error box comes up saying that the same search is being tried by another user, even when there is no other user. If you wait 15-20 seconds, the search does complete successfully.

    Any suggestions?
    Howard

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

    Re: Record Not Locking (97 SR-2)

    Locking can fool you - in order to really test the locking mechanism, you have to start an edit on one PC but not save the record, then go to the second and start an edit, go back to the first and save your change, and then try to save the change on the second PC. Then you should get an error with No Locks. If you don't do it that way, Access will refresh the data on the second PC every second or so (depending on settings), and pick up the change made to the first record before you get there to start the edit.

    As to the Jet Engine update, your client should definitely have that, although I don't believe there were any updates that affected the locking functions.

    The Find error is one I've never seen - we don't usually encourage users to work with the Find, as filters are much quicker. Perhaps someone else has see this kind of behavior.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Record Not Locking (97 SR-2)

    Thanks, Wendell. You described exactly what I did, running down the hall to the other computer. However, I was a little confused by the sentence "Then you should get an error with No Locks." I thought I should get the error with Edited Record locking.
    In any event, I'll try the test again when I next visit the site.
    Howard

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

    Re: Record Not Locking (97 SR-2)

    I should have clarified - No Locks is actually Optimistic Locking - in other words the record isn't actually locked until just before the update event occurs. Hope this makes it clearer.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Record Not Locking (97 SR-2)

    Sorry, it doesn't. Here is what Access Help has to say:

    No Locks: Microsoft Access does not lock the record you're editing. When you try to save changes to a record that another person has also changed, Microsoft Access displays a message giving you the options of overwriting the other user's changes to the record, copying your version of the record to the Clipboard, or discarding your changes. This strategy ensures that records can always be edited, but it can create editing conflicts between users.

    Edited Records: Microsoft Access locks the record you're editing, so no other user can change it. It might also lock other records that are stored nearby on your disk. If another user tries to edit a record that you've locked, Microsoft Access displays the locked record indicator in the other user's datasheet. This strategy ensures that you can always finish making changes that you start. It is a good choice if you don't have editing conflicts often.

    Could you clarify, in light of these definitions. I looked up Optimistic locking and it talked about DAO and VBA. This a simple form, with no user code.

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

    Re: Record Not Locking (97 SR-2)

    No Locks and Optimistic Locking refer to the same thing - a record isn't locked in reality, but the Jet database has explicit code to prevent it from letting two people write to a record at exactly the same time, and the description you listed is correct. In nearly all of the databases we design, we use that strategy, and bet that two people aren't going to edit the same record at the same time. We usually force changes to be save fairly promptly to prevent long delays, and live with the occasional conflict message. In very critical situations we will use the Edited Record locking, but what they don't tell you is that once an edit is started, another user cannot view the record until you finish. Furthermore with Access97 only supporting page locking, and if your records are small, locking a page may lock many records. (Access 2000/2002 support true record locking as an option.) If you are really wanting to delve into this, let me suggest you download the jetutil file referencted here .
    Wendell

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Record Not Locking (97 SR-2)

    Thanks for the lucid explanation and the jet database link. Now I'm really locked in on the correct method.

Posting Permissions

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