Solved

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

Posted on 2000-05-05
4
452 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
4 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 100 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now