Locking a record in Access from vb.net

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I need to lock a record while a user edits that record, so other users see a
"Record is locked" message when try to access that specific record. I know
there is a lockrecord property in Access, but I'm not sure how to lock this
record from my vb.net form.
Any help would be highly appreciated.

Roy
 
Roy,

That is something expressely not implemented in the non connected
environment as AdoNet is.

To get the idea, think for that how you would do that in a PDA which has no
mobile connection. For that is ADONET as well.

AdoNet is mainly build arround optimistic concurrency. The thought behind
the last is that proven is that seldom a conncurrency error exist and that
those can better than be corrected afterwards by telling the user that he
has to do things he did again. Or whatever method you want to use for that.

I hope this helps,

Cor
 
Thanks Cor for your response, but I need to find a way to do this. Do you
have any suggestions?
Roy
 
Roy,

You might try using a transaction.

But really, ADO.Net is not the tool to use if you need pessimistic locking.

If I absolutely needed this kind of functionality I would use classic ADO.

Kerry Moorman
 
¤ Thanks Cor for your response, but I need to find a way to do this. Do you
¤ have any suggestions?
¤ Roy
¤

What you could do is use a semaphore or update counter column in your table for each row to indicate
when a row is being updated.

Just make certain that you understand that a user can potentially lock a row indefinitely. Your
application may have to track how long a row is in edit mode if you want to avoid this situation.

Alternatives for pessimistic locking, if you are looking for a built-in method when working with an
Access database, would require using a connected data access mechanism such as DAO or ADO.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thanks Kerry,

Would you explain how to use classic ADO? I tried rs.Open and defined adLock
Pessimistic as lock type, but it cannot lock the record and another user can
update that record.

Roy
 
Roy,

I haven't used classic ADO in a while. But if I recall correctly, you first
must set some recordset properties to appropriate values:

CursorType = adOpenKeyset
LockType = adLockPessimistic
CursorLocation = adUseServer

Then, when the record is placed into Edit mode, the lock is applied. It
seems to me that to place the record into Edit mode you need to assign a new
value to one of its fields.

So if you want to place the record into Edit mode as soon as you have
retrieved it, you need to assign some new value to one of its fields
immedieately. There may be an explicit Edit mode that you can place the
record into, but I can't remember it.

Kerry Moorman
 
¤ Thanks Kerry,
¤
¤ Would you explain how to use classic ADO? I tried rs.Open and defined adLock
¤ Pessimistic as lock type, but it cannot lock the record and another user can
¤ update that record.
¤
¤ Roy
¤

You need to keep in mind that there is a difference between page level and row level locking in
Access. See the following:

http://msdn.microsoft.com/library/d...deovrpagelevellockingvsrecordlevellocking.asp


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top