Solved

VB6 Record locking examples in VB6

Posted on 2007-04-04
9
2,200 Views
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
Rs1.Update
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

Thanks
0
Comment
Question by:amp834
  • 3
  • 3
  • 3
9 Comments
 
LVL 7

Accepted Solution

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

TryAgain:
  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
0
 

Author Comment

by:amp834
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?
0
 
LVL 7

Assisted Solution

by:prosh0t
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 :
TryAgain:
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:
http://www.developer.com/tech/article.php/721911

0
 
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:amp834
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?
0
 
LVL 75

Expert Comment

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

0
 
LVL 7

Expert Comment

by:prosh0t
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.
1





0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 18868335
And even then it is not always the case:

<quote>
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.
</quote>
0
 

Author Comment

by:amp834
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.

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now