jwcolby
jwcolby at colbyconsulting.com
Thu May 29 15:07:03 CDT 2008
Max, In fact it is not reinventing the wheel. Bound forms hold recordsets open. Open recordsets cause write locks as soon as the edit begins. What I am discussing is a system that does not hold recordsets open. Did you know that "bound" combos and lists also hold recordsets open? each and every combo which uses a table or query opens a connection to the BE and holds it open. I just finished creating a generic callback that eliminates that by caching the data in the table. This has limitations in that it does not work well with frequently changing data but if you have data that you are willing to cache, it eliminates that connection to the BE. I do not have any solid data yet but I do know that in most cases the second and subsequent times that you open a combo that uses my callback, the combo loads MUCH faster than if it has to get the data from the BE. This is of course off topic, but I am starting to examine some issues that in certain instances can significantly speed up operation of the FE, or solve other specific problems. That is not why I am looking at the unbound form, but an unbound form implemented as discussed below will eliminate the "modify" lock that occurs when a user starts to modify a record and then goes outside to smoke a cigarette. These locks can be quite troublesome in an MDE BE under specific circumstances. This unbound form is just another tool, but it is MUCH less useful if it has to be custom engineered for each place you want an unbound form. This read / modify / inspect / write stuff is a CLASS of problem. It needs a solution for the problem CLASS. I am a bound form user, I like them and if they do not cause issues I intend to use them as long as I use Access. That said I do not have a usable unbound form tool in my toolbox, for those cases where it can solve specific problems. And yes, I have selected "edited record" in the properties. If you have read up on this you will know that Jet's implementation is less than stellar, AND it completely ignores the page lock issue. John W. Colby www.ColbyConsulting.com Max Wanadoo wrote: > John, > Not wishing to add fuel to any embers that may be smoldering, but all of > this is just re-inventing the wheel. Access does all of this and lots more > in a Bound Form. As I understood it you just had a problem with locking > spanned unconnected records. I think that between what Gustav and others > have said you have a solution - keep the bound form with all its *features* > - extract the memo field to a separate table. Perform a pseudo lock on that > if it does not lock to your satisfaction. > > BTW, I am assuming in all this that you have selected "Edited Record" as the > "Default record option" in the database options window under the "Advanced" > Tab. > > Max > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Thursday, May 29, 2008 8:24 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Redesign the problem to fit the solution > > Charlotte, > > If and when I get around to this kind of solution I would: > > 1) Create a data store for the data coming from the record to manipulate. > Call this Original Read. > 2) Create a system for automatically matching field to control. In my mind, > probably a control naming convention such that the field name is embedded in > the control name? > Something like that. > 3) Read the data from the data store into unbound controls, leaving the > original record untouched. > 4) Allow the user to edit away. > 5) Create some method to allow the user to signal "edit complete. > 6) Compare original data to control data. IF any changes were made then... > 7) Pull the same data record from the table into a NEW data store. Call > this Compare Read. > 8) Compare Original Read data to Compare Read data to discover if any data > was edited. IF NOT then LOCK the record in the table at this point. If NO > field collisions between Compare data and Modified Data (form) then LOCK the > record in the table at this point/ > 9) Create a third data record. Call this Write Data. > 10) Copy Compare Read to Write Data. > 11) Update fields with modified data from the controls > 12) Write the Write Data back to the table, releasing the lock > > If there were edits between Original Read and Compare Read AND the modified > fields collide with Compare read fields THEN error handle. The error > handler would need further thought. Notify the user and allow overwrite? > Notify the user and trash changes? Notify the user and store in some temp > location for conflict resolution? > > For a generic solution there would have to be system specific rules, i.e. in > this system we want the users to be told and made to reenter the data (trash > changes). > > John W. Colby > www.ColbyConsulting.com > > > Charlotte Foust wrote: >> Then I have to point out AGAIN that the demo I suggested does exactly >> what you wanted, but only for a single "table" and with a predesigned >> field layout in the UI. Allows you to edit/add/delete records in an >> unbound form. It does NOT allow you to do it for any record in any >> table, that is an exercise left for the student. ;-> You would >> probably want to use a grid if you wanted to handle "any table" and >> define the columns on the fly based on an ado recordset. >> >> Charlotte Foust > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >