[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


VB6 Record locking examples in VB6

Posted on 2007-04-04
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3

Accepted Solution

prosh0t earned 800 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 800 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:

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

656 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