Solved

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

Posted on 2004-09-08
11
792 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
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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 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

13 Experts available now in Live!

Get 1:1 Help Now