And i wonder... What if ? (VB 6 + SQL)

[FlipOver]

Hi there,

First of all i'd like to appologise not describing this post in its subject, because i really don't know how to do it in few words.
I've finished an app that uses ADODB recordsets and connections using an SQL database.. the thing is:
I started to wonder what if someone starts to edit a record from the DB and another user delets it? It's shure to go something wrong.. Is there any way for me to use somekind of parameter in the recorsets to avoid another user deleting/modifying the same record i'm using(editing)?

I'de really apreciate some feedback on this one.

<§> FlipOver <§>
_____________________

&quot;If something can go wrong.. IT WILL !!&quot;

 

[perplexd]

I think setting lock types to pessimistic on the queries and tables will prevent this:
ADO Data Environment:
RightClick on tbl/qry > Properties > Advanced > Lock Type

However if someone goes into the DB without going through VB front-end they could still make changes.

Within Access there are also a number of similar options as to how you would like to open the database, query, table etc. You can find them in the menu bar: tools>options>advanced.

 

[FlipOver]

If i set the lock type to pessimistic isn't it true the other users would not be able to even see te data from the hole table? (this is another doubt that i have)
The hole idea is to enable the viewing of the data that of a table that another user is editing, but preventing others to edit or even delete it..
I'de like to know if i can Lock the recordset's cursor to the record i'm editing, not to all the table's content..

 

[BobWman]

When the user doing the Edit tries to save the change, they will recieve an ADO error - they will not be able to save. This occurs whether another user deletes or edits the same record. If the record has been changed by another user, they will need to refresh their recordset before they can edit the record.

This will happen if you are using Optimistic locking. Pessimistic locking may eliminate or cut down on the possibility of the problem occuring.

 

[FlipOver]

BobWman thankx for your reply,
but i didn't understand quite well the downside of one and the other.. (in the app i'm using standerdly the pessimistic lock, but i'de really aprecite you to point out the real diferences between de optimistic and pessimistic locks..)

 

[BobWman]

Pessimistic locking will lock the record as soon as a user accesses it - other users can't even read the data. For this reason, I don't use it. I would rather that several users would have the capability to view the record at the same time. I use optimistic locking, and let ADO generate an error if 2 users try to change the same record. Optimistic locking only locks the record during the time an Update command is executed.

 

[FlipOver]

But that brings us to my inicial question.. if optimistic locking just locks the current record during the Update time, if someone deletes the record before the Update command is executed.....
That's what i've been trying to figure out, a way to lock the record for editing/deleting and enable the record to be read by others.. it's something like this: you can read but you can't change nor delete it until i finish all changes.
Is this possible?

 

[BobWman]

I believe I was wrong about not being able to view a record that is locked by another person. The first person that retrieves the record is the only one that can change it. Pessimistic locking is only supported with client side cursors, according to latest help file.

I still recommend optimistic locking - it locks the record very briefly. I use this code to display a plain English message to a user involved in this situation(strMsg holds the complete error description, including errors from the ADO errors collection).

If lngErrNum = 3640 Then
If InStr(strMsg, &quot;Row cannot be located for updating&quot > 0 Then
If InStr(strMsg, &quot;Some values may have been changed since&quot > 0 Then
strMsg1 = &quot;Another user has updated this record since you retrieved it!&quot; & vbCrLf
strMsg1 = strMsg1 & &quot;Please cancel, and retrieve the updated record!&quot; & vbCrLf
gstrErrMsg = gstrErrMsg & vbCrLf & strMsg1
End If
End If
End If

 

[BobWman]

From VB help:

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 as soon as you start editing records. No other users can read or edit the data until you either save changes with the Update method or cancel them with the CancelUpdate method.

So, if user 1 starts to edit a record, then leaves their system before updating, everybody else is locked out from viewing the record.

I hope this gives a complete and accurate picture. Sorry, if an previous comments were misleading.

 

[BobWman]

I just went through this stuff again and noticed your statement:

The hole idea is to enable the viewing of the data that of a table that another user is editing, but preventing others to edit or even delete it..
I'de like to know if i can Lock the recordset's cursor to the record i'm editing, not to all the table's content..

The locking we have been discussing should apply at the record level, not table level.

 

[FlipOver]

Thank you BobWman,

I guess this is it..
You made my weekend..
Thanks again.