I have written an application that reads, writes, and deletes to an Access 2000 shared database using VB6 and ADODB recordsets. I am using the Microsoft.Jet.OLEDB.4.0 provider. Because multiple users will be using the application, I need to lock the recordsets. I had assumed that ReadOnly was the highest or most pessimistic type of lock available, but have tried using pessimistic and optimistic. My problem is when I run two instances of the application simultaneously to test the locks. In the first instance, I want to read information from the recordset only, so I run the application 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.
The length of time the program spends with the recordsets open is minimal, but with enough users connected, a clash could occur, resulting in an inadvertant loss of data and a program crash. (I hope I have explained this clearly enough!?)
Any assistance will be greatly appreciated.
Regards Justin Davenport
