Row-level locking

[Ron Hinds]

I seem to recall seeing somewhere that Access 2003 implements row-level
locking. Is this true, or was I just dreaming? If it is true, how do I
implement it? TIA!

 

[Albert D.Kallal]

tools->options->advanced tab..

There is a check box to use
[ ] Open Databases using record level locking.

Note that you still have to set each form as to how you want locking to
occur.

In the form's design mode, data tab properties

record locks - set it to edited record.

Note that for any sub-form, you really don't need the locks to be set on,
since you FIRST have to load the main form (and table) record before you can
use the child records anyway.

My suggestion above is due to the fact that you DO NOT want to use this
feature without some consideration (ie: ONLY use it if you need). The reason
for this caution is that the feature does cause a marked increase in file
bloat. Access normally uses page locking, and they trick the data engine by
padding the records to fit into a page, so in effect you get record
locking...but bloat with it also.....

So, the feature is available, and works well, but it does cause a increase
in the rate of file bloat..and is often quite noticeable..

 

[Ron Hinds]

I see! Thanks! That was very useful! I recall in the Litwin and Getz book
for A97 they suggested using a similar method to achieve row locking, i.e.,
by designing one's tables so that a single row would take up a page. I
thought that was a somewhat kludgy solution and was hoping that MS had come
up with something more elegant by now.

tools->options->advanced tab..

There is a check box to use
[ ] Open Databases using record level locking.

Note that you still have to set each form as to how you want locking to
occur.

In the form's design mode, data tab properties

record locks - set it to edited record.

Note that for any sub-form, you really don't need the locks to be set on,
since you FIRST have to load the main form (and table) record before you can
use the child records anyway.

My suggestion above is due to the fact that you DO NOT want to use this
feature without some consideration (ie: ONLY use it if you need). The reason
for this caution is that the feature does cause a marked increase in file
bloat. Access normally uses page locking, and they trick the data engine by
padding the records to fit into a page, so in effect you get record
locking...but bloat with it also.....

So, the feature is available, and works well, but it does cause a increase
in the rate of file bloat..and is often quite noticeable..

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

I seem to recall seeing somewhere that Access 2003 implements row-level
locking. Is this true, or was I just dreaming? If it is true, how do I
implement it? TIA!