Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record Locking/SQL (2000 SR1/SQLS 2000)

    When a user opens a record on a form, I want to lock that record so no one else can edit it. In Ac2000, I can use the choices on the Options/Advanced tab, but of course that doesn't work when the back db is SQL Server. Is it simply a matter of changing one of the db's properties on the server (in the Management Console)? I'm 3 hours away so I'd prefer to arrive there with knowledge rather than get stuck, come home, and then ask you guys [img]/forums/images/smilies/smile.gif[/img]

    TIA

    James

  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/SQL (2000 SR1/SQLS 2000)

    I believe it depends on whether you are using ODBC linked tables or ADP. We use the former in most cases, and don't do anything from a locking perspective. As a result you do get an occasional error message out of ODBC/SQL Server. I'm making a WAG at this point as I haven't tested it, but at least in prior versions, ODBC mirrored the settings in the Options/Advanced tab and I don't see any reason why they would have changed that. Further, I don't believe there are any settings at the server or database level that control those kind of things. In SQL Server, I understand that the record locking strategy is set based on the connection that is being made and the access method. You might want to look at "Understanding Locking in SQL Server" in SQL Server Books Online. Is there some specific reason you want to lock a record being read? Usually if we do locking, it's when a record is edited.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    Sorry, I meant edited not just read...

    I asked because the Ac2000 Help file says this:
    >>>
    Note When you edit data in a linked SQL database table by using ODBC, Microsoft Access doesn't lock records; instead, the rules of that SQL database govern locking. In this instance, regardless of the record-locking setting you choose for your database, Microsoft Access always acts as though the No Locks setting has been selected.
    <<<

    Thoughts? Meanwhile I'll cvheck the Books Online you suggested.

    James

  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/SQL (2000 SR1/SQLS 2000)

    Guess I should have looked at Access Help - I've gotten cynical about ever finding things in it I guess! I think the statement is probably correct however - I've been playing with editing of linked ODBC tables this morning in a project, and we get the message that a record has been edited by another user if you attempt to edit the same record twice in succession. But the error message is coming from SQL as an ODBC error message rather than as an Access error message. There's a bunch of masking that goes on with ODBC error messages - there are some articles on TechNet about it and one in particular that has a bunch of complicated code so you can see the actual ODBC error message.

    In any event, SQL will lock your record (or records) for you when you start editing, and other users will get an error if you leave things at the default. Is there a high probability of two users trying to edit the same record?
    Wendell

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    Not a high probability, no. The problem the customer was having was the one you found: unfounded error messages that the record had been edited by someone else (which it hadn't). Result: inability to edit anything. I'm not fussed about an occasional user clash, but I do want to fix these erroneous errors (!). Is it OK if you open a different record then go back?

    James

  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/SQL (2000 SR1/SQLS 2000)

    That seems to be about the only one we see as well - it happens pretty frequently when a user adds or edits a record and then tries to go back almost immediately and correct an error they made. It's as if the change either hasn't been completely written to disk in SQL, or the log hasn't been created or something. I actually suspect it may have something to do with the ODBC driver and the MDAC version you are using as well. We are currently researching the issue, and I post something if I find out more about it. Please do the same if you discover a cure.
    Wendell

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    Many thanks -- I will report back if I find anything. Hope you find a fix!

    James

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    Remember that problem I had with records claiming to have been saved by another user? It is reportedly getting worse - I think my customers are getting annoyed by losing 20 minutes' work when a record gets dumped. Do we have any idea what might be causing this problem? I had been led to believe that there were no problems using an MDB and linked tables to an SQL Server db, but *if* that's the reason that we're getting these errors, that's a big problem in my book. Would these false errors still occur with an ADP? (And no, I really don't want to go down that road if I can help it.)

    Sorry to sound desperate but I thought I was going to get a happy customer by moving from Jet to SQL-S. Instead we have simply moved from one problem to another.

    James

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    James:

    I don't think that it's necessarily a MDB with linked tables to a SQL Server that's rocking the boat, but rather how SQL Server is managing the locks. If you haven't yet, do as Wendell suggested and check out the topic, "Understanding Locking in SQL Server" in SQL Server Books Online as a starting point. The short form is that it doesn't necessarily lock only one record, but may lock a page, a series of 8 pages, i.e. an extent, the whole table, or the whole db!

    So, for instance, you are editing record# 2 and SQL has locked #2 for you. I want to edit record# 3, but it is on the same data page as record# 2. If SQL has locked the whole data page that #2 is on3, then the record I want is locked as well!

    Good luck!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

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

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    Sorry, we haven't had any luck yet in isolating what is going on, and other priorities keep bumping the problem into the background. Will be sure to post if we find anything. BTW, what do you mean about records getting dumped. We don't see that happening - we get an error message, but then you can proceed and the record is saved.
    Wendell

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    By a record getting "dumped", do you mean that they run into a locking conflict with another user and can't save their work? If you're using page locking instead of record locking, that's entirely possible, as Shane has pointed out. However, there's also something seriously wrong with an interface design if it takes 20 minutes to enter a record before it can be saved.

    I've never seen this kind of problem with *either* an Access or a SQL Server back end, so it may be more an interface design issue than a problem with the database back end.
    Charlotte

  12. #12
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    Firstly, thanks to you all for replying. I apologise for my slightly incoherent post yesterday.

    Here's a summary of the situation: my customers have 8 PCs with Ac2000 SR1. Until a couple of months ago, they were sharing an MDB stored on a central PC (not exactly a server, but it wasn't being used for anything else), but repeated data corruption drove us to look at SQL Server as a back end. They now have Small Business Server 2000 installed as a proper server, and all appears to be well. The existing MDB now has linked tables to the tables on SQL Server.

    There is a main menu form - some buttons for reports etc, plus a list box of jobs in hand. Doubleclicking a job opens that record in the data entry form, which is where we have the problem. Often, apparently (yesterday they said 4 or 5 times a day for each user, and that it's getting worse; it didn't do it at all for the first couple of weeks), when they have made some changes and click the Save & Close button, they get a 'Write Conflict' error along the lines that someone else has saved this record while they were editing it. This is entirely false. As a result, they cannot save the changes they have made (I take your point about 20 mins and the interface - perhaps we can come back to that later).

    The form, for some reason, is set up as No Record Locks (on my copy - I may have changed this at the client), but the db default setting is Edited Record.

    I'm sorry but I never did read "Understanding Locking in SQL Server" in SQL Server Books Online. I'm embarrassed to say that I don't know how to find it. Please advise!

    As Wendell has indicated, this is a problem he has seen under similar setups but we are unsure of the cause. I'm wondering whether it might have something to do with the fact that these DAO linked tables use *two* connections to SQL Server per user. I presume that re-engineering this db as an ADP would remove this record locking issue, but would no doubt introduce numerous other gotchas.

    Many thanks for your input. I hope you can provide some further insights.

    James

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    If DAO is all you're familiar with, then don't jump into an ADP yet.

    If you can't find it on your SQL Server CD, you can download SQL Server BOL from the Microsoft site. Be warned however, that it's a huge download (about 35Mb, if I remember correctly).

    I'm not sure what you mean by using two connections per user. Could you explain that, please?
    Charlotte

  14. #14
    Star Lounger
    Join Date
    Apr 2002
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    I have used ADPs but not on a live project, only as a method of seeing data in an SQL Server db. I'd prefer to stay with DAO if I can.

    Re: the SQL Server Books, I only have MSDE, so I guess it's the big download for me. That could prove extremely tricky. Unless it's possible for some kind soul to somehow extract the relevant section for me.

    2 connections per user: Wendell wrote (12-Apr-02) "In addition, every object that is bound to a table will require two connections - no idea why. That makes using ODBC connections using the MSDB engine unusable for more than one or two users."

    I continue to appreciate your help with this. I'm probably going to need a bit of hand-holding to sort out this write conflict problem.

    James

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

    Re: Record Locking/SQL (2000 SR1/SQLS 2000)

    While you wait hours to download the SQL Server Books online, you might want to look at MS Knowledge Base article Q162361 and the articles it links to. It will give you the short version of record locking issues.

    As to the two connections per user, that was based on monitoring the connections for users with one, two or three Access databases (all using ODBC connections), and we would see 2, 4 and 6 connections respectively. I haven't checked this since we began working primarily with SQL Server 2000, but will try to do so today or tomorrow.

    Finally, Charlotte is correct in being concerned if you users are taking 20 minutes from the time they begin to edit a record to the time they finish it. You should probably take a hard look at how the user interface works and see if you can't streamline it, if for no other reason than if a workstation decides to go south a user may well have lost 20 minutes of work. How many concurrent users, and how many total users do you have on your system?
    Wendell

Page 1 of 3 123 LastLast

Posting Permissions

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