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

error: Could not update - currently locked by user Admin on machine Patent_13

I read a few responses that look similar to my problem, however they don't seem to apply to my exact situation.  I am using an MSaccess 2000 database with an application using VB6.0.  There are about 50 users using the application.  The error normally occurs only a couple times a day affecting only the 2 users that seem to be trying to lock the same record (which should never happen).  However, sometimes (maybe once or twice a week) it seems to compound, suddenly affecting many of the users.  At this point the easiest solution is to get all off and have them get back in.  Sometimes this works, other times must compact and repair database!

Thanks!!


The following is the section of the code where I receive the error message.  It occurs on any of the recordset update statements!


Private Sub Form_Load()
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & patent_path
Set cnn = New ADODB.Connection
cnn.Mode = adModeShareDenyNone
cnn.Open strConnect
'open table as recordset
Set rstTimelog = New ADODB.Recordset
Set rstPatent = New ADODB.Recordset
Set rstErrorlog = New ADODB.Recordset
rstTimelog.Open "tbltimelog", cnn, adOpenKeyset, adLockOptimistic
rstPatent.Open "tblpatent", cnn, adOpenKeyset, adLockOptimistic
rstErrorlog.Open "tblerrorlog", cnn, adOpenKeyset, adLockOptimistic
End Sub


Private Sub cmdBrief_Click()
 pstrFLocked = "No"
 cmdBrief.Enabled = False
 Dim pstrprog As String, pintid As Long
 Dim pstrpat As String
 Dim pintres As Integer
 Dim pSTime As Date
 mfol = "P:\PATENT"
 ChDrive mfol
 ChDir mfol
 On Error GoTo handle_error
 pstrprog = """P:\BVB.BAT"""
 pstrpat = txtpatent
 rstPatent.MoveFirst
 rstPatent.Find "patentno = '" & pstrpat & "'"
 If rstPatent.EOF Then
  MsgBox "Invalid Patent #"
 Else
  If Len(Trim(pstrpat)) <> 0 Then
   pSTime = Now()
   pintid = Shell(pstrprog & " " & pstrpat, vbMaximizedFocus)
   frmEditpat.Enabled = False
   Dim lProcessId As Long
   Dim LExitCode As Long
   lProcessId = OpenProcess(PROCESS_QUERY_INFORMATION, False, pintid)
   Do
    Call GetExitCodeProcess(lProcessId, LExitCode)
    DoEvents
   Loop While LExitCode = STATUS_PENDING
   frmEditpat.Enabled = True
   lblMessage.Visible = False
   lblMessage2.Visible = False
   pintres = MsgBox("Editing Complete?", vbYesNo, pstrpat)
   
    Dim pintpages As Integer, pintcpages As Integer, pintpbal As Integer
    pintpages = rstPatent!pages
    If IsNull(rstPatent!ecompages) Then
     pintcpages = 0
    Else
     pintcpages = rstPatent!ecompages
    End If
    pintpbal = pintpages - pintcpages
    pstrFLocked = "Timelog"
    rstTimelog.AddNew
    rstTimelog!patentno = pstrpat
    rstTimelog!stime = pSTime
    rstTimelog!userid = pstrucode
    rstTimelog!opertype = "E"
    rstTimelog!etime = Now()
    rstTimelog!doccnt = pintpbal
    rstTimelog.Update


I can receive the error on the rstpatent.update statement


   pstrFLocked = "Patent"
   If IsNull(rstPatent!editor) Then
    rstPatent!editor = pstrucode
   End If
   If pintres = vbYes Then
    rstPatent!editor = pstrucode
    rstPatent!editcomp = True
    rstPatent!ecompages = pintpbal + pintcpages
   Else
    rstPatent!editcomp = False
   End If
   rstPatent.Update


I can receive the error on the rstpatent.update statement


  Else
   MsgBox "No patent selected"
  End If
 End If
 cmdBrief.Enabled = True
 cmdBrief.SetFocus
 Exit Sub
handle_error:
pReserr = True
handleerrors
If pReserr Then
 Resume
Else
 cmdBrief.Enabled = True
 cmdBrief.SetFocus
 Exit Sub
End If
End Sub
0
havelka
Asked:
havelka
1 Solution
 
SQL_StuCommented:
Have you tried using pessimistic locking instead of optimistic?

0
 
havelkaAuthor Commented:
I had it set to pessimistic but changed to optimistic in an attempt to trap the error before the user actually went into the document (shell command) rather than wait until they get out and then get the error and not be able to update the database.  I use to have updates before and after the shell command, but changed my code in an attempt to elliminate the error - still receive the error.
0
 
arif_eqbalCommented:
The problem is with your DataBase File i.e. the Access .mdb file
It is open by some other user for Exclusive Write, so when you open it it opens the database in ReadOnly mode and when you try to modify/add the records it gives you the Error that the database is locked....

Check your Multi-User Environment who all are opening the file and try opening them in share mode...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
havelkaAuthor Commented:
All users are doing the same thing.  They select a document to edit from a list and then click a command button labeled brief.  Could you show me in the code included, where I need to change what you are talking about?  

Again, there are 40-50 users all day long and yet the error happens only a few times a day affecting only a few users.  Occasionaly, but rarely does it affect the majority op the users.
0
 
havelkaAuthor Commented:
Just recently a keyer received an error number as well as the description:  Error -2147217887:  Could not read; currently locked by user ‘Admin’ on machine xxxxxxx
0
 
havelkaAuthor Commented:
I moved this posting to the DB, Access section since I was not receiving any more responses here?  I am new to expert-exchange and am not sure if that was the right thing to do!!

0
 
jkaiosIT DirectorCommented:
The problem is you're not enforcing the so-called "Record-Level" locking. I believe default record level locking on an Access database using DAO is, by default, "Page-Level" locking. To achieve a true "Per-Record" locking, try the codes below. Perhaps, you might want to create a public sub-routine so that any time you want to use such locking method, you just call it.


---------------------[ Start of Codes ]--------------------------------------------------------------------------
 Dim oCN As ADODB.Connection
 
 '***********************************************************************
 ' The primary purpose of this sub-routine is to establish a true "real-time"                                   *
 ' so called "Record-Level Locking", which all versions of DAO (including the                                  *
 ' latest version 3.6) do not have. Microsoft included this features in Jet 4.0                                  *
 ' and ADO, but not DAO. So the rule-of-thumb is that you must enforce the most                          *
 ' effective record-locking for any of your multiuser applications. ADO has this                               *
 ' capability to use Record-Level Locking; DAO does NOT. The following code is                             *
 ' used to implement such locking in DAO. The most interesting part of this is                                *
 ' IF THE DATABASE IS OPENED FIRST IN RECORD-LEVEL LOCKING IN ADO, THEN ANY                   *
 ' SUBSEQUENT ATTMEPTS TO OPEN IT IN ADO OR DAO WILL USE THE SAME LOCKING MODE.      *
 '***********************************************************************
 
 '=============================================
 ' STEP 1: Set record-level locking using ADO *
 '=============================================
 Set oCN = New ADODB.Connection
 oCN.Provider = "Microsoft.JET.OLEDB.4.0"
 oCN.Properties("Data Source") = "C:\Temp\MyDB.MDB"
 oCN.Properties("Jet OLEDB:Database Locking Mode") = 1
 oCN.Properties("Jet OLEDB:Database Password") = "MyPassword"     <-- THIS IS OPTIONAL
 oCN.CursorLocation = adUseServer
 oCN.Open
 
 '=============================================
 ' STEP 2: Open the same database using DAO
 '=============================================
 Set CurrentDB = OpenDatabase("C:\Temp\MyDB.MDB")                    <-- CurrentDB is a Public object variable for DAO.Database
 
 '=============================================
 ' STEP 3: Close the ADO connection
 '=============================================
 oCN.Close
 Set oCN = Nothing

---------------------[ Start of Codes ]--------------------------------------------------------------------------

Hope this helps,

JK
0
 
havelkaAuthor Commented:
As stated above I moved this question to another area.  None of the suggestions above seemed to resolve the issue.  I received more suggestions (specifically, moving to MySql) in the other area and awarded the points there.  I am not sure what to do with this question but to ask that it be closed/deleted.

Thanks Dan!

Havelka
0
 
moduloCommented:
PAQed with no points refunded (of 500)

modulo
Community Support Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

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