ACCESS 2003 record locking vs page frame locking

[Guest]

which is the better locking option to use?
are there occassions when you would use one in preference over the other?
do both locking methods work equally well?

 

[Allen Browne]

Advantage of Record-level locking
- Useful where many users are editing at once.

Disadvantage
- Slower performance, and probably less tested.

The only serious problem I have experienced was with a JET 4 front end
connected to an Access 97 back end. If record-level locking was enabled,
some fairly involved action queries executing inside a transaction failed to
run to completion. Disabling record-level locking in the front end solved
the problem. Since Access 97 did not have record-level locking, the front
ends should have just ignored the setting. We experienced this in Access
2000 and in 2002.

In general, therefore I suggest you use page-level locking unless you have a
need for record-level.

 

[Guest]

Thanks Allen. My only issue with Page Frame locking is that it can lock many
other records that you don't want to lock, depending on record size.

 

[Albert D.Kallal]

which is the better locking option to use?
are there occassions when you would use one in preference over the other?
do both locking methods work equally well?

If you have a choice?, then you should AVOID record locking, but use page
locking.

The reason is that of file bloat. Record locking works by padding the
records to fill up a frame/page. So, in effect, it is a "fake" way of
achieving record locking, and the penalty is considerably MORE file bloat.

Also, remember that usually only the MAIN table needs locking. So, for
example, if I have a customer table, and a table of invoices, you likely
have that classing setup with a customer form, and a sub-form for details.
You do NOT need any locking on the details table since you can ONLY get to
that data through the main parent table/form. So, use caution with locking
anyway.

So, if your application can function fine with page locking, then that
should be your choice. Use record/row locking with caution, as it is source
of bloat in a application.

however, the feature is there for you use. If you need it, then use it. We
have to deal with file bloat, and compacting the file on a regular bases is
a requirement for any application.

Just keep in mind there is a penalty for using row locking...