Solved

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

Posted on 2004-09-08
11
806 Views
Last Modified: 2013-12-25
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
Comment
Question by:havelka
[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
11 Comments
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 12008024
Have you tried using pessimistic locking instead of optimistic?

0
 

Author Comment

by:havelka
ID: 12008259
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
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 12015059
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:havelka
ID: 12015333
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
 

Author Comment

by:havelka
ID: 12054115
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
 

Author Comment

by:havelka
ID: 12108391
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
 
LVL 12

Expert Comment

by:jkaios
ID: 12197669
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
 

Author Comment

by:havelka
ID: 12973210
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12996070
PAQed with no points refunded (of 500)

modulo
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month5 days, 17 hours left to enroll

627 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