Solved

Access Locks

Posted on 1998-03-31
13
741 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…

757 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

23 Experts available now in Live!

Get 1:1 Help Now