[AccessD] Page Locking Happening - But Set to Record Locking

To All,

I did set up a test for this problem.  A BE mdb with a single small table,
and two identical FE mdbs, each with an identical form bound to the table
via a table link.  Each mdb is set to Shared, Edited Record, and Open
Databases Using Record-Level Locking.

On my PC, with Access 2003, only a single record is locked when one of the
FE's make a data change.  The other FE can change a different record in the
test table.

On a customer's server with Access 2003 and copied test mdbs - same results.

On another customer's server with Access 2000 and copied test mdbs - I get
what is apparently page locking instead of single record locking.

The test databases were identical - I also checked to see that the record
locking settings were the same.

So, it seems that the problem lies with Access 2000 (or perhaps the
server/settings that my customer is using).

Can anyone else do a test like this and report your results?  I'll send my
test databases off-line if you'd like.

Thanks!
Dan



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
Sent: Saturday, March 07, 2009 11:14 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking

...regular linking.

William

--------------------------------------------------
From: "Dan Waters" <dwaters at usinternet.com>
Sent: Saturday, March 07, 2009 11:03 AM
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking

> Hi William,
>
> I'd like to test this, but a question first...
>
> Are you using FE/BE scenario where the FE uses regular Access table links 
> to
> the BE?  Or is the connection between FE and BE made using an OLEDB
> provider?
>
> Dan
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
> Sent: Friday, March 06, 2009 10:04 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking
>
> "This does not affect the functionality of the 'Open Databases Using
> Record-Level Locking' checkbox on the Advanced tab in Access options.  All
> my databases, FE and BE, have this checked." dan
>
> ...afaik, there has been no change in dao 3.6 to provide such 
> functionality
> on its own ...I'd be more than pleased to find I'm wrong.
>
> if you are using dao 3.6 without first opening the db using ado and 
> setting
> record level locking, the setting on the advanced tab will not provide
> record level locking by itself
> ...create a table with a single byte record so that both records fall 
> within
>
> the 4028 byte page size ...make two entries.
> ...make certain no one else is in the db since first in defines the 
> locking
> for everyone else.
> ...open the recordset with dao ...edit either record, the other is locked
> regardless of the advanced option setting.
> ...open it with an ado connection, set the record level locking option,
> close the connection
> ...open the recordset with dao ...edit either record, the other is not
> locked IF the advanced option setting is record level locking AND the form
> you're working on is set to edited record locking ...less any one of the
> three and you will get page level locking iirc.
> ...there is by the way a granularity setting with ado that will get rid of
> most of the bloat seen with record level locking but there is a 
> performance
> penalty with what is an already slow ado connection compared to dao ...I
> have that stashed in my code files somewhere since I don't use it.
>
> ...and just for the lunacy of such things, note that MS gave us record 
> level
>
> locking by simply expanding the locked record to fill an entire page 
> ...thus
>
> the bloat :(
>
> ...one of the ever growing reasons to move to SQLServer be's :(
>
> William
>
> --------------------------------------------------