Solved

adOpenKeyset, adLockOptimistic. What does it do?

Posted on 2004-09-24
8
30,516 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…

789 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