William Hindman
wdhindman at dejpolsystems.com
Sat Mar 7 11:30:34 CST 2009
Jim ....turn off compact on close ...open a db with an ado connection and set record level locking ...close the connection and open a dao record set from a form set for locking edited record only ...the iterate through a table opening and closing each record until you've done 250k of them ...close the mdb and look at the size ...compact it and do the same again without using the ado connection ...then look at the size. William -------------------------------------------------- From: "Jim Dettman" <jimdettman at verizon.net> Sent: Saturday, March 07, 2009 1:48 AM To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking > William, > > <<...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 :(>> > > That cannot be the case. You can go back and forth between locking modes > without the database size changing. > > But that remains a valid work around for if you really need record > locking. Just pad the record so that it occupies more then half a page > and > volia; record level locking! > > Jim. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman > Sent: Friday, March 06, 2009 11: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 > > -------------------------------------------------- > From: "Dan Waters" <dwaters at usinternet.com> > Sent: Friday, March 06, 2009 6:25 PM > To: "'Access Developers discussion and problem solving'" > <accessd at databaseadvisors.com> > Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking > >> Hi Jim, >> >> I read through this KB and also the referenced >> http://msdn.microsoft.com/en-us/library/aa189633.aspx article. >> >>>From these, it looks pretty certain that record locking can't be changed >> programmatically during run-time by DAO. Only ADO can do this, and only >> for >> a connection object. >> >> 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. >> >> So, I suspect that what I was seeing was that someone had edited a memo >> field, and had not yet saved the changes, since those are handled via >> page >> locks regardless of the setting. If this becomes an issue, perhaps I can >> switch to SQL Server with table links. >> >> Thanks! >> Dan >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman >> Sent: Friday, March 06, 2009 3:23 PM >> To: 'Access Developers discussion and problem solving' >> Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking >> >> Dan, >> >> Here's the link to the article (http://support.microsoft.com/kb/306435) >> and William is 100% correct that it is not a bug in the traditional >> sense, >> but rather "by design" >> >> I always refer to it as a bug though because I consider it to be broken. >> When you modify something as fundamental as locking in a database >> product, > >> I >> think it's unconscionable when you don't have it working right out of the >> gate and you know about it! Especially given that DAO was still the best >> object lib for working with JET databases. >> >> Jim. >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters >> Sent: Thursday, March 05, 2009 9:35 PM >> To: 'Access Developers discussion and problem solving' >> Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking >> >> Wow! That's a head-turner! >> >> I do have to say that I haven't seen this behavior in Access 2002 or >> 2003. >> So perhaps they did fix it in those versions. But this client uses >> Access >> 2000. >> >> And yes - if you can give me a lead to the KB article I will be in your >> debt! I don't know how but it's a good sentiment! :-) >> >> Thanks! >> Dan >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman >> Sent: Thursday, March 05, 2009 6:58 PM >> To: 'Access Developers discussion and problem solving' >> Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking >> >> Dan, >> >> Even though you are set for "record level" locking, not all operations >> are >> done at that level. Index updates, memo pages, DML operations, etc. are >> always done at page level. >> >> Also the first user in must be set for record level locking otherwise >> everyone defaults to page level regardless of setting. >> >> There is also a bug where DAO in code will not record level lock unless >> you first open a recordset in ADO. There is a MSKB article on that which >> I >> can dig out if you want. >> >> Jim. >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters >> Sent: Thursday, March 05, 2009 6:11 PM >> To: 'Access Developers discussion and problem solving' >> Subject: [AccessD] Page Locking Happening - But Set to Record Locking >> >> Twice now I've seen what appears to be page locking in the BE mdb when >> that >> file is set to record locking. About 8 contiguous records show locking - >> reminds me of Access 97, but they are using Access 2000. >> >> Has anyone seen this before or know of a fix? >> >> Thanks! >> Dan >> >> >> >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >