[AccessD] Redesign the problem to fit the solution

jwcolby jwcolby at colbyconsulting.com
Thu May 29 15:53:32 CDT 2008


Charlotte,

I have never used NoLocks but I was under the impression 
that it did a SILENT write back.  If you had a collision you 
had a collision, no warnings, no "do you want to continue" 
etc.  I thought that it was literally a "last write wins" 
kind of thing.

I think it would be rare to ask the user if they wanted to 
overwrite another user's changes.  I don't trust any user to 
have a clue whether they really want to do that.

If I were to do something of this nature I would want some 
means of controlling the writeback such that another user's 
edits were not overwritten.  If necessary show the user the 
places where the collisions occur, SOMETHING.  NEVER just 
silently write one user's changes over the top of another 
user's changes.

And finally does NoLocks correctly handle where User1 edited 
FieldA and and User2 edited FieldB?  In this case there is 
no real collision and with some slight programming BOTH 
edits can be saved without ever bothering the users.  How 
does Access handle that?  I have never tested this stuff, 
but I just assumed (yea, yea) that User2's ENTIRE RECORD was 
written back even if there was no field collision.

I am trying (unsuccessfully) to use the experience of others 
who have done this stuff before.

John W. Colby
www.ColbyConsulting.com


Charlotte Foust wrote:
> John, in a multiuser environment, you'll always have collisions.  All
> the locking scheme does is determine WHEN you have them.  If you don't
> hold the recordset open, it works the same as NoLocks, you don't have a
> collision until you try to update the record.
> 
> 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:23 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Redesign the problem to fit the solution
> 
> 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
>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 



More information about the AccessD mailing list