Record Locking in tables dao/ado

[marex]

Greetings to All,

This is a call from belgium, could you help me?

My problem is very simple, hope that the solution will be
the same :-).
My database is stored on a NT server with 3 workstations.
All the users have access to the database.
Oke, thats no problem, but how can I lock the record
when a user accessed it?
In DAO the locking works but the whole table has been locked instead of that specific record.
I changed the project in ADO with adLockPessimistic and
it's not working, everybody can change the same record.
Is there someone familiar with multi-user programming and
could tell me how it works?
Perhaps a small piece of test code should help me a lot.
I'm using MS Access 2000, Visual Basic 6 Pro, no datacontrols on the form and running MDAC 2.5.
I'm using SQL to access the tables.

Thank you for your time.

[666539]

I'm not sure you can do perform record locking with an access database. I Think to use pessemistic locking (which you should only use when absolutly necisary) you have to be using server side cursors which aren't available while using an access database.

This is a common mistake. You don't get an error if you tell your access database to use server side cursors with pessemistic locking. If those options aren't available at run time your application will just down grade you to what ever cursor location and locking method works. It is kind of misleading.

[paulw]

Locking is supported for Access DAO. Access offers two strategies, Pessimistic and Optimistic. The difference between Pessimistic and Optimistic is that Optimistic attempts to lock a page (2K) of data when the record is updated. Pessimistic locks the data as soon as it is edited.

There are additional lock types that refer to ODBCDirect workspaces only, along with dbReadOnly which is of no use to you. These methods are available with code derived recordsets (see below).

The following is taken from the Access help file (check it out):

For Connection and Database objects:
Set recordset = object.OpenRecordset (source, type, options, lockedits)

For QueryDef, Recordset, and TableDef objects:
Set recordset = object.OpenRecordset (type, options, lockedits)

You can use the following constants for the lockedits argument.

dbPessimistic Uses pessimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record you're editing is locked as soon as you use the Edit method (default for Microsoft Jet workspaces).
dbOptimistic Uses optimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record is not locked until the Update method is executed.

There are also locking methods available with Forms that will lock the underlying recordset vaia the RecordLocks property. As soon as a record is edited the page of current data from the underlying table (or query etc.) is fully locked, when the RecordLocks property is set to EditedRecord. This is effectively pessimistic. RecordLocks can be set to NoLocks, which is effectively optimistic, all users can edit the record, but the last to save will receive notification that the record has been modified by somebody else.

Cheers,

Paul.

[marex]

To 666539

Thanks for the reply.

I'm sure there is a possibility to lock records.
I know in DAO that the locking is with pages of 2k.
But ADO is rather new for me so i'm looking further.

Bye

[marex]

To paulw


The coding i used is similar to your coding.

The tip on locking methods with forms is what i need.
I will try to create a testproject to solve my problem.


Thanks for the reply

Cheers,

Ray

[paulw]

No probs.:D

Paul.

[Krass]

Maybe someone could help me out....

The RecordLocks property of my form is set to "Edited Record".

A user creates a new record and starts filling info in the form. This works good.

Meanwhile, another user goes to that record and tries to update data, but end up with an error because the record is locked. This works good.

My concern:
I'd like the 2nd user to be warned that he won't be able to edit that record. In some case, I will even completely DENY access to that record. How could I see if the current record is locked? If I'd knew how to do this, I would then be able to DISABLE some buttons on the form to avoid the 2nd user adding sub-records (invoices, notes, product order details etc)

Thanks