General discussion

Locked

Locking Access recordsets VB6 ADO

By Soundbomber ·
I have written an application that reads, writes, and deletes to an Access 2000 shared database using VB6 and ADODB recordsets. In Access the record locking level is set to Edited record and in VB I have set the ADODB.Recordset lock type to adLockReadOnly. My problem is that when I come to test the locks, they don't appear to be in effect. I have set a break point in the code just after the recordset is opened, I then go into the database through Access and attempt to delete the record and it lets me!
I am using the Microsoft.Jet.OLEDB.4.0 provider.
Any help would be greatly appreciated

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Don Christner In reply to Locking Access recordsets ...

Since you are using adLockReadOnly, you are only looking at the data, not locking anything. I think that you probably want to use adLockPessimistic. The following is from VB6 help when looking under 'lock type property':

adLockReadOnly - Default. Read-only?you cannot alter the data.
adLockPessimistic - Pessimistic locking, record by record?the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.
adLockOptimistic - Optimistic locking, record by record?the provider uses optimistic locking, locking records only when you call the Update method.
adLockBatchOptimistic - Optimistic batch updates?required for batch update mode as opposed to immediate update mode.


Don

Collapse -

by Soundbomber In reply to

Don,
Thanks for the reply. I had assumed that ReadOnly was the highest or most pessimistic type of lock available! I tried using pessimistic and optimistic and both locked out 'Access' from the recordset. My problem now is when I run two instances of the application simultaneously. In the first instance, I want to read information from the recordset only, so I run the app. up to the break point so that the recordset is open. In the second instance I want to delete or alter the contents of the same recordset that is open in the first instance (from say another machine on the network) but for obvious reasons I don't want to allow this to happen until the recordset in the first instance has been closed. It seems that no combination of lock type will prevent the second instance from deleting the recordset whilst it is still open in the first instance. (I hope I have explained this clearly enough!?)
Regards Justin Davenport

Collapse -

by Don Christner In reply to Locking Access recordsets ...

Bad form, I answered your question correctly but you rejected it and then ask another?

In your new question, pessimistic will work. If the first one could be opened as read-only do that. If it can't be opened read-only, the second one will be allowed to make changes when you call for an update, the first computer will be told that changes happened if it tries to change the same data.

What are the chances that two people will be attempting to change the same data on the same record?

Don

Collapse -

by Soundbomber In reply to

Apologies - didn't quite understand the rating system
The question is the same but accessing through code rather than through Access.
The length of time the program spends with the recordset open is minimal but with enough users connected it could happen.
I will repost the question

Collapse -

by Soundbomber In reply to Locking Access recordsets ...

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums