VB6 Record locking examples in VB6

Can anyone provide a few concrete examples in VB6 of record locking in ADO, with error detection and retry.  (I read some MSDN and experts-exchange articles, but couldn't find what I need)

1. Example of optimistic lock, and what to do if I want to retry:
Sub Opt1()
Open recordset Rs1
' change it:
Rs1! Name = "Jane"
Rs1!Type = 14
if (record could not be updated because someone else updated it before we got to it)
  How Can I try the update again?
  How can I get the new "current value of Rs1" (that another user updated; the Resync?), do my "change it" again, and try again?
End If

2. Example with pessimistic lock
Sub Opt2()
Rs1.Open ' is it at this point the lock is obtained on the record?
Rs1!Name = "Jane"  ' or  is it at this point that the lock is obtained?, the first time you try to change it
' is there an explicit way of saying "I would like to lock this record now"
Rs1!Type = 14
Rs1.Update ' do I need to trap for any errors here?

3. Any other examples or suggestions, especially with parameters to look for and error trapping

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What you'll want to do is use error handling and 'sleep's' in order to wait and try your update again.  Keep in mind, pessimistic locking is safer overall.

Here's a generic function which will open (at least try to open) a recordset up to 15 times sleeping 2 seconds in-between each call.  (this same method can be used to update recordsets and sleep upon an error to try again).

You can change this to suit your needs:

'point to sleep function in windows dll.. put this at the top of your module
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function OpenRec(cn As ADODB.Connection, strSQL As String, rsRec As ADODB.Recordset) As String
'designed to open recordsets without crashing
' return error message or blank on success
Dim intErrors As Integer
Dim strError As String
Dim strtemp As String

OpenRec = ""
intErrors = 0
On Error GoTo TryAgain
Set rsRec = New ADODB.Recordset
rsRec.Open strSQL, cn, adOpenStatic, adLockPessimistic, adCmdText

Exit Function

  intErrors = intErrors + 1
  If intErrors > 15 Then 'gonna sleep 2 seconds each time; if it fails for 30 seconds straight then return error
    OpenRec = "something is seriously wrong!!"
    Exit Function
  End If

  Sleep 2000
Resume 'resume will go back to where the error ocurred and start again

End Function

good luck

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amp834Author Commented:
Hi prosh0t, and thanks for your reply.  

1. Does pessmistic locking mean that at RsRec.Open time, the record is locked, or that it is locked when I try to do an edit to RsRec?

2. Can the error checking be more specific, i.e. not any error, but if it's a lockout error?

And, just in case,
3. Are there any settings on "cn" (ADODB.Connection) I should be aware of for the locking to work?
1.  pessimistic means a record is locked as long as the cursor is at that position. No updates can happen to this field then.  Optimistic locking means it only locks that record if and when the recordset attempts to update that data at that cursor location.  Then when it is done updating, it is unlocked again.

2.  yes, you can find out which type of error it is simply by checking Err.Description, or Err.Number :
Select Case True
    Case Ucase(Err.Message) Like "*LOCK*"
        'do something
    Case Else
       'do something else
End Select

3.  The thing to worry about here is where the recordset cursor resides.  For pessimistic locking to work, it obviously has to reside at the server rather than the client, so you'd set the recordset's 'cursorlocation' property to 'adUseServer' (if it is not already by default).  Check this out:

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
Just a word of caution depending on your provider, you may not even have record locking, but rather page locking.  That mean that when you use adLockPessimistic you could in fact be locking more than one row.

This is the reason, most developers do not use any type of locking, but rather depend on other mechanisms.
amp834Author Commented:
prosh0t, one more clarification

So if I have just one record in the recordset, I will continue to have the lock until I close the recordset.  And if I have multiple records, then moving the cursor to another record will release the lock on the record I just moved away from.

Is this correct?
Anthony PerkinsCommented:
>>Is this correct?<<
Not necessarily.  See my previous comment.

acperkins is right... what kind of provider and database are you using?  For example, if you're using Jet 4.0 with Access (very common combo), record-locking (rather than page) is on by default (see http://msdn2.microsoft.com/en-us/library/aa189633(office.10).aspx) and everything will work as you described unless you change it.  But for other db's and providers I'm not sure.

Anthony PerkinsCommented:
And even then it is not always the case:

However, any SQL Data Manipulation Language (DML) queries — that is, queries that add, delete, or modify records — that are run from ADO (when you use the Microsoft Jet 4.0 OLE DB Provider), DAO, or the Access query user interface will use page-level locking. Page-level locking is used for SQL DML statements to improve performance when you are working with many records. However, even when record-level locking is turned on, it is not used for updates to values in memo fields and values in fields that are indexed — they still require page-level locking.
amp834Author Commented:
Thank you both for your help.  I also found the Resync doesn't always work (there are some strange cache problems as well), though Requery or (Rs.Close and Rs.Open) will work ok if necessary, when I find that someone else has updated before I got a chance to do so.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.