?
Solved

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

Posted on 2000-05-05
4
Medium Priority
?
468 Views
Last Modified: 2012-06-27
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
Comment
Question by:sniles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 400 total points
ID: 2781845
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
 

Expert Comment

by:matarcallarse
ID: 2781875
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
 
LVL 2

Expert Comment

by:Laphroaig
ID: 2784969
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
 

Author Comment

by:sniles
ID: 2784990
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question