VB6 Record locking examples in VB6

Posted on 2007-04-04
Last Modified: 2013-12-25
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

Question by:amp834
  • 3
  • 3
  • 3

Accepted Solution

prosh0t earned 200 total points
ID: 18854608
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

Author Comment

ID: 18859218
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?

Assisted Solution

prosh0t earned 200 total points
ID: 18860820
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:

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 18865319
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.

Author Comment

ID: 18866476
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?
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18867812
>>Is this correct?<<
Not necessarily.  See my previous comment.


Expert Comment

ID: 18868300
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 and everything will work as you described unless you change it.  But for other db's and providers I'm not sure.

LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 18868335
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.

Author Comment

ID: 18877510
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.


Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
clicking a shape in a frame array vb6 3 45
I need help using System.Web.HttpUtility.HtmlEncode in my VB.Net application 3 76
VB6 ListBox Question 4 42
AWS RDS Backups? 3 40
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question