Record Level Locking and Editing

[Ronald Dodge]

Given an issue that I had with DAO coding and I'm eventually gonna need to
convert my code to ADO anyhow, I been looking through the help files and
MSDN stuff online. This morning alone, I have made a lot of progress in
learning the ADO coding, some of which is similar to DAO coding, but there
are quite a few differences too. Currently, I'm looking at record level
locking at the appropriate times.

Is there a way to tell the recordset or the DB system behind the scene
rather to lock the current record of the recordset and without having to
close out the recordset and then reopen the recordset with the lock
decision?

Example:

Recordset is:

Set to the item table
Dynamic
Pessimistic (Record Level Locking in place)

User initially enters a valid item number.

Recordset finds the record, and at this point, it should not be locked cause
the user has at this time only inquired on the data.

If user starts to change the data in the form (Note, form is not bound,
cause all of the manipulation is handled via code and the recordset), and
has the authority to modify the data in the item table, the record then gets
locked provided it wasn't already locked by some other user/system and not
yet released. If record was already locked by some other user, then a
message comes back to the user and states as such (this last part would be
done via error handling).

I would hate to have a record locked by one user that is only viewing the
data and has the write authority should they need it while another user who
also has the same permissions is trying to make some sort of a modification
to the record.

NOTES: Main reason why forms/bounds would not be bound, it's not user
friendly for those users that are heavily mouse users, thus would not allow
for strict data validation checks at the appropriate times for those mouse
users.

 

[Steven Parsons [MSFT]]

Hi Ronald -

This is Steven from Microsoft Access Technical Support replying to your
newsgroup post. It sounds like you wish to change a recordset's lock type
"on the fly", as opposed to closing out and re-instating a defined
recordset object. In other words, if I understand you correctly, you wish
to initially instantiate a recordset with a LockType of Optimistic, for
example, and then as soon as a user begins to edit a record (or maybe the
user clicks a button), the LockType of the same recordset session becomes
Pessimistic. This cannot be done without closing out the current rescordset
session and re-instantiating the recordset under a new LockType.

For example, if I globally define ADO objects like the following:

Dim RST As ADODB.Recordset
Dim CON As ADODB.Connection

I can then initialize RST with settings like the following:

RST.CursorLocation = adUseServer
RST.CursorType = adOpenKeyset
RST.LockType = adLockOptimistic 'optimistic locking
RST.ActiveConnection = CON
RST.Open "Select * From Table"

I cannot, from the Click event of some button, for example, simply change
RST from LockType adLockOptimistic to LockType adLockPessimistic without
first executing:

RST.Close

In other words, my Click event would have to first close the RST session,
and then while executing the same Click event, I could:

RST.ActiveConnection = CON
RST.CursorLocation = adUseServer
RST.CursorType = adOpenKeyset
RST.LockType = adLockPessimistic 'pessimistic locking

Please let me know if this answers your question or if you need further
assistance.

Sincerely,
Steven Parsons [MSFT]
Microsoft Access Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! (http://www.microsoft.com/security)

 

[Ronald Dodge]

I certainly understand about the LockType not being able to be changed, but
here's a scenerio that I was thinking about:

Users 1 and 2 have Read and Write permission on the Master Item Table
ADO Recordset is set to Pessimistic LockType with Record Level Locking on
the DB file

User 1 is looking at Record 1234 in the Master Item Table
User 2 goes to Record 1234 in the Master Item Table

User 1 is only viewing the record even though it has the capacity to change
it. User 1 does not want to change any information at this time on the
record. User 2 would like to be able to change data on this record of 1234.
Can User 2 change the data or does User 1 have this record locked since it
was the first user to go to the record with the read/write capacity?

Based on what I have read, I have understood this to be the case where User
2 is locked out of the record for editing purposes for as long as User 1 is
still on the record via the ADO recordset.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Hi Ronald -

This is Steven from Microsoft Access Technical Support replying to your
newsgroup post. It sounds like you wish to change a recordset's lock type
"on the fly", as opposed to closing out and re-instating a defined
recordset object. In other words, if I understand you correctly, you wish
to initially instantiate a recordset with a LockType of Optimistic, for
example, and then as soon as a user begins to edit a record (or maybe the
user clicks a button), the LockType of the same recordset session becomes
Pessimistic. This cannot be done without closing out the current rescordset
session and re-instantiating the recordset under a new LockType.

For example, if I globally define ADO objects like the following:

Dim RST As ADODB.Recordset
Dim CON As ADODB.Connection

I can then initialize RST with settings like the following:

RST.CursorLocation = adUseServer
RST.CursorType = adOpenKeyset
RST.LockType = adLockOptimistic 'optimistic locking
RST.ActiveConnection = CON
RST.Open "Select * From Table"

I cannot, from the Click event of some button, for example, simply change
RST from LockType adLockOptimistic to LockType adLockPessimistic without
first executing:

RST.Close

In other words, my Click event would have to first close the RST session,
and then while executing the same Click event, I could:

RST.ActiveConnection = CON
RST.CursorLocation = adUseServer
RST.CursorType = adOpenKeyset
RST.LockType = adLockPessimistic 'pessimistic locking

Please let me know if this answers your question or if you need further
assistance.

Sincerely,
Steven Parsons [MSFT]
Microsoft Access Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! (http://www.microsoft.com/security)