Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record locking (front-end/back-end) (A97 SR-2 & SQL 2000)

    A question of design for you to ponder :

    What is the best way of locking records on a two-tier system, in this case an Access front-end (using unbound forms) and SQL Server back-end ? The problem is this

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record locking (front-end/back-end) (A97 SR-2 & SQL 2000)

    Here are my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>. Others may have different ideas. I would let it depend on the usage of the database.

    If the probability that two users will try to edit the same record at the same time is low, I would choose the first option. In many of my databases, most users only look up information without modifying it, and existing information is updated infrequently, so I take the risk of a "collision" for granted.

    If it will frequently occur that two users will try to edit the same record, I would choose the second option, and use a procedure to clear the edit flags from time to time, for example in the middle of the night.

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

    Re: Record locking (front-end/back-end) (A97 SR-2 & SQL 2000)

    In general, Access handles record locking and unless you have extensive overlapping activity on records we always use no-locks. Access will actually lock a record even in that condition while it saves changes. The same thing happens with tables in SQL Server - in fact it is generally more robust than Access in handling locking issues. In your case, if you get a collision, you will get an ODBC error when a conflict is detected. It is generally recommended that you put a timestamp (not a date/time) field in the SQL Server table design, as it does improve the performance, especially when there are triggers on the SQL table, or you DRI implemented. In any event these things all function at the system level, and you as the developer don't need to actually do any programming to deal with them. With an Access front-end, all the actual record locking is done in SQL Server, so it would be a pretty challenging project to attempt to do anything in that area anyhow. Hope this helps - if you have more questions please post back.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record locking (front-end/back-end) (A97 SR-2 & SQL 2000)

    Hans & Wendell

    Thanks for your replies. I take your points about record-locking on the back-end/server, but what I was really looking at was how to handle the user's experience of it. Should it be when they try to edit the record, or when they try to save it that they are told someone else is editing the record ? I think Hans has probably got it when he says that it really depends on the complexity of the system.

    Thanks again

    Bodders

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

    Re: Record locking (front-end/back-end) (A97 SR-2 & SQL 2000)

    Sorry - I didn't make my point very clear. All you really have to do is select a locking strategy for your back-end, and that will handle it in nearly all situations. At least I've never encountered a situation where I couldn't select an appropriate record or page locking strategy. You shouldn't every have to design your own locking mechanism. And there isn't a great deal you can do aside from error handling when a conflict is detected. Error handling is another subject.
    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
  •