William Hindman
wdhindman at dejpolsystems.com
Sat Mar 7 11:14:00 CST 2009
...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 > > -------------------------------------------------- > 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 >