ADO, DAO and record locking

I am using VB6, ADO and Access 97

I am having a problem with getting ADO to lock records

I have setup the cursor location as UseServer and
opened a recordset with pessimistic locking, but
when I access the same record from another workstation
it opens the record no problem, I would have expected some
form of error.

In short can I make ADO lock an Access 97 record so no other user can access it until the lock is released by
the original user.

I have read in another post the ADO and OLE DB don't currently support recordset locking and you must use DAO
if you require recordset locking. Is this true?

I would be very gratful for any help

[Clunietp]

ADO supports locking up to the record level, while DAO supports locking up to the page level. In any case, ADO can lock.

Do you issue a EDIT command on your ADO recordset before you attempt to edit it on the other machine? Remember, the lock does not go into effect until you edit the record, and you will not get an error on the 2nd machine until that user attempts to edit the record

HTH

Tom

[lychew]

Clunietp is right. The lock pessimistic will not work unless you issue an update, insert or delete command first.
For example:
if the sequence is as below:
begintrans
select
update
select
committrans

when two machines process the first select, both will return a record. However when it comes to the update, one machine will process till the committrans and the other will only process the update after the first machine committrans. Which means the record has been locked.

Many thanks for your help. This has been causing me no
end of headaches.

Its not exactly clear in the VB docs (not to me anyway :-) ) that you have to use transactions in this situation.

Thanks again.

[Clunietp]

You usually will not need a transaction unless you are editing/deleting/inserting more than 1 record, and they all depend on each other to exist to maintain data integrity.

If you are just inserting 1 record at a time, don't use transactions, as they only slow everything down.

Tom

Clunietp thanks for your reply.

I just cant seem to get the lock on unless I use transactions. See the simplified code sample below

With rstUsers
cmd.CommandText = "Select * From Users Where Number = " SelectedUser & ""
.Open cmd, , adOpenKeyset, adLockPessimistic
Adodc.BeginTrans
!UserName = txtUserName.Text
.Update
Adodc.CommitTrans
.Close
End With

This gives me a lock, if I remove the begintrans and committrans I don't get a lock.
I'm using ADO so I can't use LockEdits

[Edited by SeanR on 05-03-2000 at 06:51 PM]

[Clunietp]

Well, do what you gotta do, I suppose :)