Solved

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

736 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