Solved

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

Posted on 2004-09-08
11
799 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
Industry Leaders: 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!

 

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

Industry Leaders: 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

Suggested Solutions

Title # Comments Views Activity
VBS file using code from 2nd file (txt or vbs) 4 47
vb6 connector to SQL Server 2 42
Zip Folders Using Chilkat Routines 1 72
Macro problems with Excel file 6 23
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

756 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