Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 791
  • Last Modified:

Access Locks

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
JimBob091197
Asked:
JimBob091197
  • 6
  • 6
1 Solution
 
ducCommented:
Is this really what you want to do, or do you want to lock the record from two DIFFERENT users ?
0
 
cmainCommented:
I need responses to this question, as I have not been able to resolve this issue myself.

0
 
JimBob091197Author Commented:
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
Technology Partners: 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!

 
ducCommented:
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
 
JimBob091197Author Commented:
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
 
JimBob091197Author Commented:
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
 
ducCommented:
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
 
ducCommented:
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
 
JimBob091197Author Commented:
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
 
ducCommented:
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
 
JimBob091197Author Commented:
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
 
JimBob091197Author Commented:
Hi JB
I've sent you a mail
Regards
duc
0
 
ducCommented:
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

Technology Partners: 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!

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