Solved

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

Posted on 2000-05-05
4
458 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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 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…

785 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