General discussion

Locked

Locking Acces 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. 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

This conversation is currently closed to new comments.

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

All Comments

Collapse -

by Soundbomber In reply to Locking Acces recordsets ...

To clarify, I placed a break point in the code just after the recordset is opened in the first instance of the application, and then run the second instance and delete the same record.

Collapse -

by ananthap In reply to Locking Acces recordsets ...

I think it is not sufficient to run the app till the record set is open. This is because the meaning of pessimistic is that the record gets locked (only) when data in the underlying recordset is changed. Could you confirm that you have done this?

Optimistic is more what (Optimistic !?). It locks it only when you give the update statement. M$ help you if the record has been deleted between your fetch and update,.

For your information, I read this in some old MSDN CD.

The solution is to use PASS THRU' SQL statements of some sort. OR make a dummy update.


End

Collapse -

by Soundbomber In reply to

Thanks anyway

Collapse -

by MadestroITSolutions In reply to Locking Acces recordsets ...

My dear friend,
The Microsoft Jet database engine does not support dynamic cursors. Therefore, the OLE DB Provider for Microsoft Jet does not support the adLockDynamic cursor type. When a dynamic cursor is requested, the provider will return a keyset cursor and reset the CursorType property to indicate the type of Recordset returned. Further, if an updatable Recordset is requested (LockType is adLockOptimistic, adLockBatchOptimistic, or adLockPessimistic) the provider will also return a keyset cursor and reset the CursorType property.

Run the program, and after you have opened the recordset, check the CursorType property and you will see it has changed.

The only thing I can suggest is that you add a lastmodified field with Date/Time info, and check this value again before you save the changes. That way you will know if the version you have is the latest one or if someone has changed it, in which event you will have to present a message (which you are probably familiar with) like :
Record has just been updated by another user. All changes will be reverted" and get the latest version.

I know, It sucks.... which is why I stopped using Access and switch to developing applications in SQL Server >:)

Don't forget to rate my answer, and if you have any questions, DO NOT REJECT MY ANSWER, first drop me a line at: juanr@richfx.com Good Luck!

Collapse -

by Soundbomber In reply to

Poster rated this answer.

Collapse -

by Soundbomber In reply to Locking Acces recordsets ...

This question was closed by the author

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

Related Discussions

Related Forums