Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    record-locking with SQL SERVER back-end (Access 2002, SQL Server 2000)

    I have an Access 2002 front-end .mdb that operates with a SQL Server 2000 back-end. I'm having trouble with the record-locking. Currently, more than one user can open the same record concurrently, and one of them will inevitably lose any changes that he/she made to the record when the other one closes it. I want the records to be locked so that as soon as a user opens a record for viewing through the Access front-end, it will be locked to all other users until it's closed. I'm really having trouble figuring out how to set this up since SQL Server couldn't care less about the locking that you set up in Access's Tools-Options menus. Can anyone point me in the right direction?

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

    Re: record-locking with SQL SERVER back-end (Access 2002, SQL Server 2000)

    Well, I had some time to look at the books online, and also to consult a couple of SQL Server books - one of my favorites is Professional SQL Server 2000 Programming - a WROX book that I'm not sure is still available. In any event, record locking in SQL Server is much more complex than that in Access - you have several different kinds of locks that can be applied to a given set of records. In general they are controlled in the syntax of the query (or view) being used, and are determined by the ODBC driver that you are presumably using to connect from the .MDB to SQL Server. You can create what are called ODBC direct queries and in that case I believe you can control the locking strategy, at least to some extent. Another strategy is to use pass-through queries to execute SQL Server stored procedures. In that case you write the specific actions to be performed, including transactions and the like, and simply invoke the procedure from Access. Each has it's advantages and drawbacks.

    The fundamental question here is whether you really want to lock a specific record simply because someone is reading that record. In general that's the advantage of a database - it is multiuser so that people can read the same data at the same time. The challenge comes when one of them wants to update the data and how you deal with other users who want to read, or also update, that same record. To get at the nitty gritty, search the SQL Server Books Online for "Lock Modes" and for the various types such as Shared, Update, Intent, and Exclusive. Hope you gets you started down the trail.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: record-locking with SQL SERVER back-end (Access 2002, SQL Server 2000)

    Thanks, Wendell. Actually, I had looked at record-locking on SQL Server Books online, and was rather befuddled. I don't want to write the queries to perform the sql work that Access performs for me. That seems to be the only way that I can control the record-locking. But, I have a real problem when two users view the same record, and one ends up losing all of his/her changes. Does this happen in your Access - SQL Server applications? If so, how do you deal with it? If not, how do you prevent it?

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

    Re: record-locking with SQL SERVER back-end (Access 2002, SQL Server 2000)

    Well, for starters, viewing the record isn't the same thing as editing it, so what you have if records are being clobbered, is two people editing the record during the same time interval. In that case an edited record lock should be all that you want. I'm not sure about this, but you should be able to test it pretty easily - use the locking properties on a form and set it to edited record and see if the ODBC driver doesn't pick that up and prevent another user from starting to edit. The other option is to create a view (which we often do), specify the record locking properties in the view, and then base your form on a view. You can connect to views just like you do a table - the only issue is that you will normally need to tell Access what the primary (unique) key is when you attach to it.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: record-locking with SQL SERVER back-end (Access 2002, SQL Server 2000)

    I'll test the edited record locking as you suggested, but I"m pessimistic since even MS admits that Sql Server doesn't look at the record-locking options set in Access, and each user has his/her own front-end. I don't think that I can use a view as the data source of the form because I need the changes that are made to the form to be saved to the table. I'd prefer to let access do this as opposed to writing the ODBC Insert and Update queries myself.

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

    Re: record-locking with SQL SERVER back-end (Access 2002, SQL Server 2000)

    We normally use optimistic locking on tables, be they Access or SQL Server, and I believe that's what ODBC normally does. We occasionally see cases where ODBC returns an error message saying that another user has temporarily locked a record so that your change can't be saved, and suggests you try again, which normally works. So something is going on with ODBC and locking. It may not be sufficient for your needs however.

    I would not discard the idea of using a view - views are attached just like a table, and as long as you tell Access what the primary key is, they can be updated. We have developed a number of systems using views and they work pretty well. A more advanced approach is to create an indexed view, and that attaches just like a normal indexed table - there are some issues in creating indexed views however, and you should understand them before you proceed too far down that path. Hope this clarifies things a bit - sorry I can't be more specific about what ODBC actually does.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: record-locking with SQL SERVER back-end (Access 2002, SQL Server 2000)

    Thanks, Wendell. Just a question - when you use a view as a datasource, is the view updated or the table behind it updated when the user makes changes on the form?

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

    Re: record-locking with SQL SERVER back-end (Access 2002, SQL Server 2000)

    The tables behind the view are updated, just as they would be if you used SQL Enterprise Manager or Analyzer to view the data and then edit it.
    Wendell

  9. #9
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: record-locking with SQL SERVER back-end (Access 2002, SQL Server 2000)

    Now I understand - thanks Wendell! I'll try using a view instead of my table as the form's data source. Hopefully, as you say, this will help with the locking problem. Thanks for all of your help and insight.

Posting Permissions

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