Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    2

    Unanswered: Records locks don't release - error 3260/3187

    I've written a call tracking application for the tech center of a major
    automotive after-market company.

    Everybody is running the same application from a networked server. The
    workstatons run NT. The network apears to be Netware. I have never seen
    the server that the program is being stored on - and they have a lot of
    servers at this place.

    The techs enter a small amount of information concerning the just finished
    tech call into an unbound form. When they are done they click a button to
    "log a call". This event uses VB code to write this information to a new
    data record in the table - checking for lock errors. This is the only time
    the table is open. It is closed again after the update.

    Some times they get a 3260 (Couldn't update; currently locked by user '|2'
    on machine '|1') or 3187 (Couldn't read; currently locked by user '|2' on
    machine '|1') errors which either don't go away or take a very long time to
    clear out.

    When we go to the machine that the message says is holding the lock, the
    form is sitting waiting for data entry. It is not in the adding record
    routine and so (according to the code) doesn't even have the table in
    question open.

    If we exit the program on the machine that the message says has the lock,
    the other users can immediately write their records.

    Does anybody know why the lock is persisting after the recordset has been
    closed and the subprocedure exited?

    I've just read about the "dbEngine.Idle (Refresh Cache)" method and wondered
    if that should be included in my code after I close the recordset?

    Would changing the application into a front end / back end application clear
    up this type of lock persistents (they liked having everybody use the same
    copy of the database for ease of maintainenance.)

    Any thoughts would be appreciated.

    A summary of the codes is included below. Thank you.

    ----------------------------------

    Private Sub LogCall_Click()
    '***
    '*** Write the record to the Table TechCall
    '*** Minimize the form if the user default (vMinimizeOnLog) is set to
    minimze.
    '***
    Dim rstTechCall As Recordset
    Dim fld As Field, iLockCount As Integer, strmsg As String

    On Error GoTo LogCall_Error
    'Make sure all required fields have been populated
    If required fields not populated
    Appropriate message and action
    'Otherwise write the record
    Else
    'open table
    Set rstTechCall = dbs.OpenRecordset("TechCall")
    'and set locking to optimistic - page will be available until
    update
    '(pessimistic always occurs if during addnew the record will
    span a page)
    rstTechCall.LockEdits = False

    'turn on the hourglass and
    DoCmd.Hourglass True
    'start adding new record
    On Error GoTo Lock_Error
    rstTechCall.AddNew
    On Error GoTo LogCall_Error

    For Each fld In rstTechCall.Fields
    'the Form's control names are the same as the Table
    TechCall's field names
    fld.Value = Me(fld.name).Value
    Next fld

    'finish adding new record,
    On Error GoTo Lock_Error
    rstTechCall.Update
    On Error GoTo LogCall_Error
    'and close the connection to the table
    rstTechCall.Close
    ' Call the Idle method to release unneeded locks, force
    ' pending writes, and refresh the memory with the current
    ' data in the .mdb file.
    DBEngine.Idle dbRefreshCache
    'turn off the hourglass
    DoCmd.Hourglass False

    're-initialize the form (zero it out)
    Call ResetForm
    'Minimizes the form?
    If vMinimizeOnLog Then
    DoCmd.RunCommand acCmdAppMinimize
    End If
    End If
    Exit_LogCall:
    Exit Sub

    LogCall_Error:
    MsgBox Err & ": " & Err.Description
    DoCmd.Quit

    Lock_Error:
    'See if Error is a 'Table Lock' or 'Open Exclusive' error
    If InStr(Err.Description, "lock") Or InStr(Err.Description, "exclusiv")
    Then
    'increment how many times we've gotten here
    iLockCount = iLockCount + 1
    'if it's been more than 5 times ask if they want to continue
    waiting
    If iLockCount > 5 Then
    DoCmd.Hourglass False
    strmsg = appropriate message
    'yes - continue waiting for the recordset to unlock
    If MsgBox(strmsg, vbYesNo + vbQuestion, "Retry Logging
    Call?") = vbYes Then
    DoCmd.Hourglass True
    iLockCount = 1
    'no - close the connection to the table and return to the
    unaltered form
    Else
    rstTechCall.Close
    Resume Exit_LogCall
    End If
    End If
    'pause for 20 - 200 milliseconds
    sSleep (20 + Int(Rnd * 180 + 0.5))
    'and try again
    Resume
    'all other error
    Else
    MsgBox Err & ": " & Err.Description
    DoCmd.Quit
    End If
    End Sub

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Try this:

    Set rstTechCall = dbs.OpenRecordset("Select * From TechCall Where UserID=0;",dbOpenDynaset)
    rstTechCall.LockEdits = False

    'turn on the hourglass and
    DoCmd.Hourglass True
    'start adding new record
    On Error GoTo Lock_Error
    rstTechCall.AddNew
    On Error GoTo LogCall_Error

    For Each fld In rstTechCall.Fields
    'the Form's control names are the same as the Table
    TechCall's field names
    fld.Value = Me(fld.name).Value
    Next fld

    'finish adding new record,
    On Error GoTo Lock_Error
    rstTechCall.Update
    On Error GoTo LogCall_Error
    'and close the connection to the table
    rstTechCall.Close
    set rstTechCall=Nothing

    ' your code here

  3. #3
    Join Date
    Feb 2002
    Posts
    2
    Thanks for the reply.

    What is the advantage to opening an empty Dynaset as oposed to a Table? Does Access perform locking differently on each when the AddNew method is called?

    Set rstTechCall = dbs.OpenRecordset("Select * From TechCall Where UserID=0;",dbOpenDynaset)

    Could the above also be written as

    Set rstTechCall = dbs.OpenRecordset("Select * From TechCall Where False;",dbOpenDynaset)
    -------

    The problem seems to be that the locks are not being dropped after the recordset has been closed.

    Thanks again

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    If you could sent me your application and instruction how to duplicate the problem, I would look into it at home.

    HTH

Posting Permissions

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