Arthur Fuller
artful at rogers.com
Thu Mar 31 14:29:07 CST 2005
One thing you didn't mention: forms have a Data-Entry flag. Have you tried setting that to True? ----------------- What I would suggest, to give you time to really sort out the problem, is creation of a copy of the table(s) for each user, and then using a static function or a class open those tables in your form. The contention will instantly go away. You can then add some code to the Save button that appends the master and detail rows to the "real" tables. Not an elegant solution, but it will work until you figure out what's wrong. John W. Colby wrote: >WinXP, OfficeXP. All SPs applied. This is a simple bound subform, no >recordset objects or the like. > >We are going to try optimistic locking to see if the issues go away. Given >how simple this db is and how they all just sit in this one form entering >new records all night, it seems that any locking is probably not needed. > >John W. Colby >www.ColbyConsulting.com > >Contribute your unused CPU cycles to a good cause: >http://folding.stanford.edu/ > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust >Sent: Thursday, March 31, 2005 1:55 PM >To: Access Developers discussion and problem solving >Subject: RE: [AccessD] May need an Unbound form > > >I vaguely remember something about recordlocking switching to page locking >under specific circumstances, but I'll be darned if I can remember any >details. Did it have something to do with a server backend?? Problems with >extended pagelocking due to NOS "intelligent" caching?? Transactions?? >Darn!! The memory loss that comes with old age is biting me today. > >Are you using DAO to open the recordset? I believe it ignores the settings >unless you specify them in the lockedits argument of the OpenRecordset. >Also, it ignores recordlocking if you open the database from a shortcut. >And optimistic locking (NoLocks) overrides record-level locking, so could >that be the problem? The only other thing I could think of would be if you >were creating your own custom ID for each of these records rather than using >an autonumber. BTW, what version of Access are you dealing with? > >Charlotte Foust > > >-----Original Message----- >From: John W. Colby [mailto:jwcolby at colbyconsulting.com] >Sent: Thursday, March 31, 2005 9:15 AM >To: 'Access Developers discussion and problem solving' >Subject: RE: [AccessD] May need an Unbound form > > >Charlotte, > >Nope, this is a bound subform, uses the LinkChild / Master properties to >"filter" the subform records, the subform just uses SELECT * from tblXXXX as >the query etc. This is a clean implementation of a bound form, nothing >fancy needed or used. The lock is occurring in the child form / table, the >communications subform. The users literally open the main form, filtered to >a small subset of people (in a set of zip codes) and just start at the first >person, making phone calls. If they don't get an answer or just leave a >message on an answering machine, then they create a new comms record and >select from a "status" and move on. If they actually talk to the person, >they select a status and then may fill in a memo re the conversation. > >Really nothing fancy here, a very simple child table with about 5 fields >including the PEID foreign key, and a couple of other FKs for employee >making the call, contract being called about and status, plus the memo. >These are ALL new comm records, no editing a record someone else might be in >etc. > >Why is there a lock at all? The FE is set up for Record locking. The memo >field is actually just a text(255) field, not even a real memo. I removed >ALL indexes (including the relationship between people and >comms) on the theory that this might be a lock on the index pages. I >finally added code to save the record In AfterUpdate of each and every >control so that as they tabbed out of each control the record was saved (to >release any locks) and this does help... But there are still locks >happening. > >Bizarre. > >John W. Colby >www.ColbyConsulting.com > >Contribute your unused CPU cycles to a good cause: >http://folding.stanford.edu/ > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust >Sent: Thursday, March 31, 2005 11:57 AM >To: Access Developers discussion and problem solving >Subject: RE: [AccessD] May need an Unbound form > > >John, > >It sounds like you have a normal one-to-many relationship between the parent >form and subform. Where is the lock occuring, in the parent table or the >child table? Is the parent table included in the subform source? Is the >subform table included in the parent form source? When I've seen similar >locking issues, they've always revolved around those two questions. > >Charlotte Foust > > >-----Original Message----- >From: John W. Colby [mailto:jwcolby at colbyconsulting.com] >Sent: Wednesday, March 30, 2005 10:26 PM >To: 'Access Developers discussion and problem solving' >Subject: [AccessD] May need an Unbound form > > >OK Guys, > >WinXP, OfficeXP, all service packs applied for Windows as well as office. > >I have a situation where a group of 2 to 6 or so people open a form of >people info, with contact subform, making phone calls all night. These >people call therapists trying to fill employment contracts. They may make >changes to the person info (address changes etc. but mostly they just enter >new contact records which consist of a simple date and employee (prefilled), >call status (left message, not interested etc,) and a memo field. All night >long just mostly entering records in this contact subform. > >The problem is that they are having HUGE locking issues. I got rid of all >indexes, but they were still getting locks. An employee will start an edit >(select a status, then fill in the memo etc.) but they may hold the record >in the edit state for a minute or two. If any other employee tries to start >a new edit they get a lock, WITHOUT the lock symbol in the record selector. >It is reproducible, it always happens and it SUCKS. These people are not >editing each others records, they are always building NEW records, but they >are getting these locks and it just killed their productivity. > >I made a change such that as soon as they enter any data (after update of >any control), the record is saved. That causes the new records to save >immediately as well as the edits during the course of business to save ASAP. >This helps but they are still seeing the locks occasionally, however now >they are seeing the lock symbol in the record selector. > >I checked all the usual suspects, SPs, edit modes, compact / repair etc. I >am just not finding the problem. As far as I know this shouldn't be >happening, but it is. Has anyone seen this behavior? Solutions? > >This leads me to consider (for the first time in my career) using an unbound >form for collecting the new data record so that the save just does a single >write of the entire record, one time, as the contact record is finished and >ready to store. Needless to say this is not my forte and I the form is not >allowing new data to be entered. The form itself is unbound, all the >controls are unbound, the modes are: > >AllowEdits False >AllowDeletes False >AllowAdds False >EditMode True > >This should allow all the controls to accept data but no control can accept >data. > >I tried AllowEdits true and Editmode false, no help. The form is not >locked, the controls are not locked. The form was opened originally from a >form which was passing in a filter if that means anything. I was going to >grab the existing record data and then unbind the form but I am now just >looking at a plain Jane new data entry form. > >Any suggestions gratefully accepted. > >Thanks, > >John W. Colby >www.ColbyConsulting.com > >Contribute your unused CPU cycles to a good cause: >http://folding.stanford.edu/ > > > > -- Internal Virus Database is out-of-date. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 3/18/2005