jwcolby
jwcolby at colbyconsulting.com
Thu May 29 15:22:57 CDT 2008
Yes, but I don't see NoLocks being a useful tool in multi-user environments. You are asking for edit collisions. John W. Colby www.ColbyConsulting.com Charlotte Foust wrote: > Actually IIRC, unless things have changed, NoLocks means that write > locks are not applied until the *update* starts. > > Charlotte Foust > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Thursday, May 29, 2008 1:07 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Redesign the problem to fit the solution > > 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 >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >