Solved

adOpenKeyset, adLockOptimistic. What does it do?

Posted on 2004-09-24
8
31,724 Views
Last Modified: 2011-08-18
Hi,
I'm trying to understand the locking mechanism using adOpenKeyset, adLockOptimistic.
If I've opened an record set using adOpenKeyset, adLockOptimistic, what is the implication to others reading the same record?
Can someone else open the same record with adOpenKeyset, adLockOptimistic and update it?
thanks,
phil
0
Comment
Question by:spoowiz
[X]
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
  • 5
  • 3
8 Comments
 
LVL 13

Expert Comment

by:Michael_D
ID: 12147784
First of all there is two different parameters for those values: CursorType and LockType

CursorType specifies the type of cursor used in a Recordset object.

adOpenDynamic - Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them.

adOpenForwardOnly - Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset.

adOpenKeyset - Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.

adOpenStatic - Uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.

==========================

LockType specifies the type of lock placed on records during editing.

adLockBatchOptimistic - Indicates optimistic batch updates. Required for batch update mode.

adLockOptimistic - Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.

adLockPessimistic - Indicates 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 immediately after editing.

adLockReadOnly - Indicates read-only records. You cannot alter the data.

If you still have questions I'll be happy to help
0
 

Author Comment

by:spoowiz
ID: 12147847
what does optimistic or pessimistic mean?
what does data changes by other users are still visible, mean?
0
 
LVL 13

Expert Comment

by:Michael_D
ID: 12147927
Optimistic means that record will be locked only the updating time
Pessimistic means that record will be locked all the time from first change and til updating

ex.

rs.Open sSQL, Cn, adOpenDynamic,adLockPessimistic
rs.Fields("SomeField")="some value"  ' <= Record locked
rs.Update '<=Record Unlocked

Locked records  are visible to other users but they will get an error if they try to modify locked records
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 13

Expert Comment

by:Michael_D
ID: 12148013
What do you want to do? Maybe I can help you to make decision what type to use?
0
 

Author Comment

by:spoowiz
ID: 12148043
I'm basically using these 2 types:
'adOpenForwardOnly, adLockReadOnly
'adOpenKeyset, adLockOptimistic
Any comments?

So, in using adLockOptimistic, I'm being "optimistic" that 2 users won't be changing the record at the same time, yes?
If user1 opens a recordset with recordx, then user2 opens recordx also, then user1 does update, then user2 does update, does user1 changes get overwritten by user2's update?
0
 
LVL 13

Accepted Solution

by:
Michael_D earned 125 total points
ID: 12148089
yes,if  user2 updating record AFTER user1 FINISHED update then it will be overwritten. If User 2 trying to update record at the time when record is being updated by user1 then user 2 will set error message. But you can be "optimistic" because this is very unusual scenario.:)

Opposit If Lock type pessimestic  
user1 made some changes in recordx and still working on it (not updated record)
if user2 just try to modify this record he will get error message saying that record has been locked
Usualy Optimistic lock is enough for most applications.    
0
 

Author Comment

by:spoowiz
ID: 12148101
THANKS
0
 
LVL 13

Expert Comment

by:Michael_D
ID: 12148113
Did you understand difference between Cursor types? This is more important than lock type.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

726 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