Solved

adOpenKeyset, adLockOptimistic. What does it do?

Posted on 2004-09-24
8
29,260 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
  • 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

705 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

19 Experts available now in Live!

Get 1:1 Help Now