dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: preventing multiple users from updating recordset

  1. #1
    Caroline Guest

    preventing multiple users from updating recordset


    I am trying to come up with a method of 'locking' an update so only one user
    can perform the task. I have a form with a flexgrid as the source of an updateable
    recordset. Users can select any or all records to update and
    the defaults are automatically filled in ... dates are calculated and a unique
    number assigned. That unique # is incremented from a field stored in a one-record
    system defaults table and it also needs to be updated.

    For example, if the system field is 400 and the user chooses to update 5
    records, they'll be updated with the #'s 401-405 and the # field in the system
    table needs to be incremented to 405. I don't want to lock the system table,
    because it contains several fields that serve the same purpose for other
    updates throughout the application. I've tried several approaches, but can't
    seem to settle on which, if any, is best:

    - an additional Yes/no field in the system table for each # field that will
    be updated. When the user starts updating, that field is updated to true
    to indicate that some user is already updating so others can't.

    - an additional table for each # field in the system table. When a user
    starts updating, that table is locked ...

    - read the system table twice, once when the user loads the form and just
    before the update. If the system # has changed, someone else has already
    updated.

    - no method, but handle it after the update if there were concurrency problems.

    To make it more complicated, all users need to open the form but all don't
    have update rights. Any of them could keep the form open all day, so the
    update can't be locked when the form loads. Once I figure out when to lock,
    then I have to deal with when to unlock it!

    This is driving me crazy ... any advice would be greatly appreciated. I'm
    using VB6, ADO and Access 97 with stored queries.

    Thanks,
    Caroline


  2. #2
    Rick Guest

    Re: preventing multiple users from updating recordset



    Caroline -

    You are trying to solve database management issues from the code. Not only
    will it drive you crazy, but you are unlikely to succeed.

    The database, and your connection to it, must be configured to manage the
    necessary locking in a multi user environment.

    I would rethink your approach, starting with the capabilities of your database
    and network/access platform.

    Once you have the database doing its job, your coding will be very straight
    forward.

    Best of Luck
    Rick


    "Caroline" <cjust@io.com> wrote:
    >
    >I am trying to come up with a method of 'locking' an update so only one

    user
    >can perform the task. I have a form with a flexgrid as the source of an

    updateable
    >recordset. Users can select any or all records to update and
    >the defaults are automatically filled in ... dates are calculated and a

    unique
    >number assigned. That unique # is incremented from a field stored in a one-record
    >system defaults table and it also needs to be updated.
    >
    >For example, if the system field is 400 and the user chooses to update 5
    >records, they'll be updated with the #'s 401-405 and the # field in the

    system
    >table needs to be incremented to 405. I don't want to lock the system table,
    >because it contains several fields that serve the same purpose for other
    >updates throughout the application. I've tried several approaches, but can't
    >seem to settle on which, if any, is best:
    >
    >- an additional Yes/no field in the system table for each # field that will
    >be updated. When the user starts updating, that field is updated to true
    >to indicate that some user is already updating so others can't.
    >
    >- an additional table for each # field in the system table. When a user
    >starts updating, that table is locked ...
    >
    >- read the system table twice, once when the user loads the form and just
    >before the update. If the system # has changed, someone else has already
    >updated.
    >
    >- no method, but handle it after the update if there were concurrency problems.
    >
    >To make it more complicated, all users need to open the form but all don't
    >have update rights. Any of them could keep the form open all day, so the
    >update can't be locked when the form loads. Once I figure out when to lock,
    >then I have to deal with when to unlock it!
    >
    >This is driving me crazy ... any advice would be greatly appreciated. I'm
    >using VB6, ADO and Access 97 with stored queries.
    >
    >Thanks,
    >Caroline
    >



  3. #3
    Jason Guest

    Re: preventing multiple users from updating recordset


    Caroline,

    It sounds like you are trying to perform a form of optimistic locking. If
    you simply want to prevent other users from accidentally stepping on each
    other (concurrency). The Microsoft KB article Q190727 might help you.

    The general idea it talks about is how the WHERE clause is generated when
    an update to the database is performed. You can skip the system field incrementation
    stuff by changing the "Update Criteria" dynamic property on your recordset
    object.

    When you call Update on a recordset normally it would generate the following
    SQL (assume I'm updating the CompanyName column, and CustomerId is the primary
    key):

    UPDATE Customer SET CompanyName = "New Value" WHERE CustomerId = 123 And
    Companyname = "Old Value";

    rsCustomers.CursorLocation = adUseClient
    rsCustomers.Properties("Update Criteria").Value = adCriteriaAllCols
    rsCustomers.Open "SELECT * FROM Customers", cnNWind, _
    adOpenStatic, adLockOptimistic, adCmdText
    rsCustomers.Fields("CompanyName").Value = "Acme"
    rsCustomers.Update

    The above code will generate the following SQL:
    UPDATE Customer SET CompanyName = "New Value" WHERE CustomerId = 123 And
    Companyname = "Old Value" And PhoneNb = "4075551212" And FirstName = "Jason"
    And ...

    If the record has been changed by someone else, ADO will kick up an error
    and you can then inform the user, requery the database and try the update
    again, or whatever else you might want to do.

    Depending on your DBMS there should be no performance impact (the plan generated
    by the DBMS engine should be able to figure out if an appropriate index is
    available and then it scans the tables that haven't changed).

    I've used this procedure before on numerous occasions to enforce optimistic
    locking with optimistic concurrency (however, I rarely use the Recordset
    object to do it). This should also work with both client-side and server-side
    cursors.

    Hope this helps!


    "Caroline" <cjust@io.com> wrote:
    >
    >I am trying to come up with a method of 'locking' an update so only one

    user
    >can perform the task. I have a form with a flexgrid as the source of an

    updateable
    >recordset. Users can select any or all records to update and
    >the defaults are automatically filled in ... dates are calculated and a

    unique
    >number assigned. That unique # is incremented from a field stored in a one-record
    >system defaults table and it also needs to be updated.
    >
    >For example, if the system field is 400 and the user chooses to update 5
    >records, they'll be updated with the #'s 401-405 and the # field in the

    system
    >table needs to be incremented to 405. I don't want to lock the system table,
    >because it contains several fields that serve the same purpose for other
    >updates throughout the application. I've tried several approaches, but can't
    >seem to settle on which, if any, is best:
    >
    >- an additional Yes/no field in the system table for each # field that will
    >be updated. When the user starts updating, that field is updated to true
    >to indicate that some user is already updating so others can't.
    >
    >- an additional table for each # field in the system table. When a user
    >starts updating, that table is locked ...
    >
    >- read the system table twice, once when the user loads the form and just
    >before the update. If the system # has changed, someone else has already
    >updated.
    >
    >- no method, but handle it after the update if there were concurrency problems.
    >
    >To make it more complicated, all users need to open the form but all don't
    >have update rights. Any of them could keep the form open all day, so the
    >update can't be locked when the form loads. Once I figure out when to lock,
    >then I have to deal with when to unlock it!
    >
    >This is driving me crazy ... any advice would be greatly appreciated. I'm
    >using VB6, ADO and Access 97 with stored queries.
    >
    >Thanks,
    >Caroline
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center