Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Location
    Gloucestershire, England
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record Locking (Access 2000)

    We have a routine that is used by multiple users at once, so needs to rely on record locking. The procedure is designed to read the first record in a file that has no entry in a specific field and then to stamp the users name in this field (thus reserving the record for them). Because more than one user can be executing this routine at once, it is important that two people could not be writing their name against the same blank field at the same time.

    The code looks like this:

    Dim db As Database, rs As Recordset, ok2go as integer
    ok2go=false
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM [MasterFile] where isnull([Processedby])=true and isnull([processedon])=true and [followupon] <= now() ORDER BY [MasterFile].UniqueRef", dbOpenDynaset, dbSeeChanges)
    If rs.EOF = False Then
    rs.MoveFirst
    Do While ok2go = False
    If IsNull(rs![ProcessedBy]) = True Or rs![ProcessedBy] = CurrentUser() Then
    rs.Edit
    rs![ProcessedBy] = [Forms]![splash]![TheUser]
    rs.Update
    If rs![ProcessedBy] = [Forms]![splash]![TheUser] Then
    ok2go = True
    End If
    Else
    rs.MoveNext
    End If
    Loop
    End If

    Having read articles in the lounge on locking in SQL and using .lockedits =true, I can get the code to report 3197 "The Microsoft Jet Database Engine stopped the process because you and another user are attempting to change the same data at the same time", which is part of the way there (at least two users don't end up with the same record), but does anyone know of a way to check if the record is locked and if it is move to the next record in the set rather than just wave a flag of defeat?

    Perhaps there is a better way that I am trying to use to get the same end result?

    Any assistance gratefully received!

  2. #2
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Locking (Access 2000)

    I didn't see any error trapping code, but [warning -- inelegant solution about to be suggested] in your error handler check to see if Err.Number 3197 is thrown. If it is resume at a label "TryAgain:" which I would put right after the Do While statement.

    Slightly revised code

    Public Sub DoWonderfulThings()
    On Error goto Err_DoWonderfulThings

    Dim db As Database, rs As Recordset, ok2go as integer
    ok2go=false
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM [MasterFile] where isnull([Processedby])=true and isnull([processedon])=true and [followupon] <= now() ORDER BY [MasterFile].UniqueRef", dbOpenDynaset, dbSeeChanges)

    If rs.EOF = False Then
    rs.MoveFirst
    Do While ok2go = False
    TryAgain:

    If IsNull(rs![ProcessedBy]) = True Or rs![ProcessedBy] = CurrentUser() Then
    rs.Edit
    rs![ProcessedBy] = [Forms]![splash]![TheUser]
    rs.Update
    If rs![ProcessedBy] = [Forms]![splash]![TheUser] Then ok2go = True
    Else
    rs.MoveNext
    End If
    Loop
    End If


    Exit_DoWonderfulThings:
    Exit Sub

    Err_DoWonderfulThings:
    If Err.Number = 3197 Then 'Record locked try again
    Resume TryAgain
    Else
    MsgBox "Error: " & Err.Number & VBA.vbNewLine & Err.Description
    Resume Exit_DoWonderfulThings
    End if

    End Sub

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

    Re: Record Locking (Access 2000)

    What do you mean by this:
    <hr>The procedure is designed to read the first record in a file that has no entry in a specific field and then to stamp the users name in this field (thus reserving the record for them).<hr>
    Are you talking about a linked file or a table, which are two very different things, indeed? Assuming from the SQL that you're talking about a table, what creates those records in the first place so you can stuff in a user name?

    One of the problems you're having is the direct result of using CurrentDb, which points to the instance of the Access database loaded into memory on that machine, not to the backend on the server, so the two are slightly out of sync. That means that you may be able to avoid the problem by using DBEngine(0)(0) instead of CurrentDb to instantiate your database object. However, until you save the newly changed record other users will not see the "locked" record, and you will need to force a refresh on the database object to get that into the CurrentDb as well.

    Perhaps if you explain more about what you're trying to accomplish with this table, we can be of more help.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Mar 2004
    Location
    Gloucestershire, England
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Locking (Access 2000)

    Thanks FlyerMike, I've never been too hot on the error trapping thing, but I understood what you suggested and it worked fine!

Posting Permissions

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