Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007

    Unanswered: Updating records with ado - lock type question

    I'm working through converting dao code to ado for an access 2003 adp that's been upsized to sql server 2000.

    One piece of code I'm working on has 2 recordsets open, rst & rst2. Depending on the values of rst the code may update the same row in rst2 more than once. I've opened rst1 with the following code:

    rst2.Open "tblTimeSheet", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    At the end of each loop there's and update: rst2.Update

    But on the 2nd update I'm getting the error message:

    "row cannot be located for updating. some values may have been changed since it was last read"

    adLockoptimistic is only supposed to lock the record when the update command is running but it appears the record is still locked.

    Any help would be much appreciated,


  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    Moved to Microsoft Access topic.
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Adelaide, South Australia
    My first thought is to only run the Update method once all of the changes have been made.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    Here's some code I have which you may or may not find helpful.

    Dim rs as adodb.recordset 'first table recordset
    Dim rx as adodb.recordset '2nd table recordset
    Dim strSQL as string
    Dim TID as integer 'autonumber ID (CallerID) in table 1
    strSQL = "Select * from SomeTable"
    Set rs = new adodb.recordset strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rs!SomeField = "Something"
    TID = rs!CallerID 'autonumber field - save newly created ID to TID before closing recordset
    rs.close 'Note - if looping through something, I may not close recordset
    set rs = nothing
    'Note - I could have re-used rs below but used rx for demonstration purposes in this scenario
    set rx = new adodb.recordset
    strSQL = "Select * from SomeTable2"
    or...strSQL = "Select * from SomeTable2 where CallerID = " & TID & "" strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rx!CallerID = TID
    rx!SomeField = "Something"
    set rx = nothing

    I usually open the recordset with one of the following syntax (keep in mind that I use SQL Server linked tables): strSQL, currentproject.connection, adopendynamic, adlockoptimistic strSQL, currentproject.connection, adopenkeyset, adlockreadonly

    I know there's other combinations but I haven't really had problems using one of the two syntax's above.
    Last edited by pkstormy; 01-09-08 at 16:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2007

    Thumbs up

    Thanks for the feedback. It's a complicated piece of code with loops within loops and I wanted to avoid passing the results to variables before updating as I just couldn't get my head around how to do it.

    I did a bit of reading up about locktypes etc and changed my code to read:

    rst2.Open "tblTimeSheet", cnn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
    rst2.Properties("Update Criteria") = adCriteriaKey

    This has done the trick. There's not likely to be more than 1 user accessing the tables in question.

    Thanks again.


Posting Permissions

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