Jim Dettman
jimdettman at verizon.net
Sat Mar 7 00:48:42 CST 2009
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