• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

DBException : Could not update; currently locked by user Admin on machine

My multithreaded, multi-user application is using Access through ODBC & MFC.  I'm receiving periodic catches of DBException (using the CRecordset::Update member function) with reason: "Could not update; currently locked by user Admin on machine ....".

Each of my users receives his own connection to the database, although they are often serially reused.  I have implemented critical section locking around each call  to any of the underlying CRecordset member functions.

There are between 75 and 150 users on my application when this starts happening.  There are probably 15 or 20 database connections at any one time.
0
sniles
Asked:
sniles
1 Solution
 
Brendt HessSenior DBACommented:
Access handles locking issues poorly, I'm afraid.  The best thing you can do is to implement a generic Update routine, with a retry, something like this (an abbreviated version of the code we use here):

Sub RSMUpdateTbl(tbl As Object, ErrorMsg As String)
    Dim Retry%, Pause%, Msg$, Cursor%
    Dim FirstErr$, FirstErrCode%, FoundError%

    On Error Resume Next

    Cursor = Screen.MousePointer
    FirstErrCode = 0

    Do
        Err = 0
        tbl.Update
        freelocks
        If Err = 0 Then Exit Do
        If Retry < 100 Then
            If FirstErrCode = 0 Then
                FirstErr = Error$
                FirstErrCode = Err
            End If
            Screen.MousePointer = 9 - (Retry Mod 4)
            Retry = Retry + 1
            For Pause = 1 To 2000: DoEvents: Next Pause
       Else
            ' Handle too many retries here
       End If
   Loop
End Sub
0
 
matarcallarseCommented:
From the looks of it, a user is attempting to update a field that is being modified (and locked) by another user.  Unless your users are logging in using a workgroup (.mdw) file, they are logged into Access as the default Admin user.
0
 
LaphroaigCommented:
Was your multi-user application developed using VB6. If this is the case then the problem you are encountering could well be best addressed by using VB routines which provide more flexibility in multi-user data handling than VBA routines in Access.

I'm also unclear as to how mat's comment on running Access from a group file, would change the way that Access handles record locking in a multi-user environment. I assume that you are aware of the limited choices of 'pessimistic' or 'optimistic' record locking options that Access provides. Although limited, these choices can be optimised to suit most environments.
0
 
snilesAuthor Commented:
It looks like bhess1's answer is about the best that can be done in this case.

To address Laphroaig's comment, my application is in VC++ 5.0, using MFC, so the VB6 scenario you described doesn't apply.  I am aware of pessimistic and optimistic options, and am experimenting with both.

Thanks.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now