Solved

Access Locks

Posted on 1998-03-31
13
745 Views
Last Modified: 2008-02-26
Hi all

I'm not sure if this is the correct place for this question, but here goes anyway...

I spend most of my time on Ex-Ex in the Delphi area (it's nothing personal :) ).  CMain left a question in the Delphi section on 29th Mar 1998 entitled "Access Database" which applies to my question too.  (http://www.experts-exchange.com/topics/comp/lang/delphi/Q.10045417)

I left a comment at CMain's question about code I tried in Delphi (using OLE) to lock an Access record.

The code didn't lock the record (2 users could edit the same record) so I tried it in VB using OLE and DAO.  Neither example worked.

Here's the VB code:

Dim db As Database
Dim rs1 As Recordset, rs2 As Recordset

  ' Open database, and 2 recordsets on same table.
  Set db = OpenDatabase("C:\Temp\DbTest\Test.mdb")
 
  Set rs1 = db.openrecordset("tblTest", dbOpenTable)
  Set rs2 = db.openrecordset("tblTest", dbOpenTable)

  ' Set lock edits on BOTH recordsets and put
  ' them BOTH into edit mode (on same record!!)
  rs1.MoveFirst
  rs2.MoveFirst
  rs1.LockEdits = True
  rs2.LockEdits = True
  rs1.Edit
  rs2.Edit  ' Why no error here???

  ' Change the SAME field in both recordsets.
  rs1!TestStr = "From RS1"
  rs2!TestStr = "From RS2"

  ' Update changes.  RecSet1's changes ARE LOST!
  rs1.Update
  rs2.Update

  ' Clean up.
  rs1.Close
  rs2.Close
  db.Close


You can rewrite it with variants too (similar to the Delphi approach I tried) and it still doesn't work:

Dim db As Variant, dbEng As Variant
Dim rs1 As Variant, rs2 As Variant

  ' Open database, and 2 recordsets on same table.
  Set dbEng = CreateObject("DAO.DBEngine.35")
  Set db = dbEng.OpenDatabase("C:\Temp\DbTest\Test.mdb")

Etc...

My question is, therefore, why does LockEdits not do what the help file says, "If you set LockEdits to True and another user already has the page locked, an error occurs when you use the Edit method."

I am using DAO 3.5 (Access 97) but have also tried with DAO 3032 (Access 95).

Regards,
JB
0
Comment
Question by:JimBob091197
  • 6
  • 6
13 Comments
 

Expert Comment

by:duc
ID: 1460051
Is this really what you want to do, or do you want to lock the record from two DIFFERENT users ?
0
 
LVL 1

Expert Comment

by:cmain
ID: 1460052
I need responses to this question, as I have not been able to resolve this issue myself.

0
 
LVL 5

Author Comment

by:JimBob091197
ID: 1460053
Both actually.  It needs to work in a multi-user environment, but I also need to know if the same user opens up 2 instances of the same form in the same app.  Sure there are many ways to get around this, but I am interested in knowing why the LockEdits doesn't seem to work, both for a single app and in a multi-user situation.  (This is just an example, and primarily for interest sake at the moment, although cmain's need is more practical.)

JB
0
 

Expert Comment

by:duc
ID: 1460054
You have to use the optimistic locking and trap the error 3197 with a general error handler, that gives  you the result of  "From RS1".  If you resume the execution at the same line a second update is issued with success and you have the result   "From RS2".
0
 
LVL 5

Author Comment

by:JimBob091197
ID: 1460055
Hi duc

I changed the code to the following, and the error handler NEVER gets called...

--- START OF CODE ---
Dim db As Database
Dim rs1 As Recordset, rs2 As Recordset

On Error GoTo MyErrThing

  Set db = OpenDatabase("C:\Temp\DbTest\Test.mdb")
  Set rs1 = db.OpenRecordset("tblTest", dbOpenTable)
  Set rs2 = db.OpenRecordset("tblTest", dbOpenTable)

  rs1.MoveFirst
  rs2.MoveFirst
  rs1.LockEdits = True
  rs2.LockEdits = True
  rs1.Edit
  rs2.Edit

  rs1!TestStr = "From RS1"
  rs2!TestStr = "From RS2"

  rs1.Update
  rs2.Update

  rs1.Close
  rs2.Close
  db.Close
 
  Exit Sub
 
MyErrThing:
    MsgBox "Error!"
    Resume Next

End Sub

--- END OF CODE ---

The error handler never gets called.  Even if I comment out the LockEdits, the error handler still doesn't get called.

If there was an error (with no error handler) VB's (and Delphi's) default error handler would have thrown an error.  (E.g. try opening a DB that doesn't exist.)

Does this work correctly on your system?  If so, what ver of VB and DAO are you using?  (I'm using VB 5 and DAO 3.5.)

Cheers,
JB
0
 
LVL 5

Author Comment

by:JimBob091197
ID: 1460056
Hi duc,

I'm gonna reject your answer (for now) cos it doesn't answer why LockEdits allows 2 users to edit the same record.  Also, the error handler doesn't seem to work.

Regards,
JB
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Expert Comment

by:duc
ID: 1460057
Hi JB
You have to set both LockEdits = false to get optimistic locking. If you do so the error handler should be called. Let me know about the result.
Regards
duc
0
 

Expert Comment

by:duc
ID: 1460058
Hi duc,

What you say is correct, but VB is not consistent.  The 1st time the code enters the error handler (with LockEdits = False), if I do a "resume 0" the 2nd update occurs anyway, and if I do a "resume next" then the error handler is NOT called if I run the code a 2nd time!!!  Very strange...

However, I want to use pessimistic locking (who invented this optimistic stuff anyway?) because I need the error on the Edit method, NOT the Update method, and so my original question still applies: Why does LockEdits not do what the help file says: "If you set LockEdits to True and another user already has the page locked, an error occurs when you use the Edit method."

JB
0
 
LVL 5

Author Comment

by:JimBob091197
ID: 1460059
Hi JB
I tryed it again. To simulate a multiuser environment on your PC you have to start VB twice. Now try to step into your code stop in the first environment after the first EDIT, than step into your code in the second environment. You will see if you try to EDIT the record it will fail and you can trap the error again. Error message is:

 3260  Couldn't update; currently locked by user 'xxxx' on machine 'yyyyy'.

Regards
duc
0
 

Accepted Solution

by:
duc earned 100 total points
ID: 1460060
Hi duc,
This clearly isn't working.  I did what you said, and I was able to edit the record twice...  The error handlers were ignored, but not consistently.

Please e-mail your sample to me at davekw@iafrica.com, and I can e-mail you my code too.

Thanks,
JB
0
 
LVL 5

Author Comment

by:JimBob091197
ID: 1460061
P.S.  Also, as I said before, I need to know if the SAME user tries to edit the record in the same app.  I can determine this on every other DB that I've tried (in VB and Delphi) except Access, because the LockEdits thing is a farce.
0
 
LVL 5

Author Comment

by:JimBob091197
ID: 1460062
Hi JB
I've sent you a mail
Regards
duc
0
 

Expert Comment

by:duc
ID: 1460063
Hi duc

Thanks for the sample you sent me.  I had a look, and you are right - it enters the error handler on the Edit method in the 2nd instance of the app, but not if you Edit twice in the same app.  I assume this is the behaviour you got too.

I guess that with Access I will have to live with this. I.e. Edit does NOT cause an error if inside the same app.  (I wonder what would happen if I created 2 workspaces within the same app??  Maybe that would work...)

Anyway, thanks for you help & patience.

Regards,
JB
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Check Disk (CHKDSK) on all volumes and fix if needed. 8 178
LINQ return type for nested group query 6 71
Use Multiple Forms 4 49
Saving history changes to sub form 4 36
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
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…

910 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

24 Experts available now in Live!

Get 1:1 Help Now