Dan Waters
dwaters at usinternet.com
Fri Mar 6 17:25:50 CST 2009
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