[AccessD] Unbound Form Check For Changes

This multiglom i worked at had an Oracle based product coded in Java to
handle logistics. People swore that values in fields were changing without
their doing anything. Apparently multiple users could view and edit the
same shipment record.

So you put in a date and someone else saved their record but did not
include the date. Yours disappeared How this happened I don't recall but
YOUR date would disappear when you saved. Really. Actually now that I
recall it had something to do with the system autosaving records in
progress. It was a messy implementation and rollout. Serious design flaw
and inadequate logging.

I was not involved with the design and don't recall the main cause so
"wasn't me"...  your system sounds mo' bettah. Multiglom's was built
offshore with low - bidder .
On Mar 24, 2014 10:15 PM, "John W Colby" <jwcolby at gmail.com> wrote:

> >so if you feel that a set of unbound classes could ever be created, for
> the product, I would be very interested.
>
> I created one so I do think it is possible.  Understand that it was under
> contract for IBM so I cannot start with their code, however I learned a
> great deal doing that.
>
> The center of my framework system was a clsCRUD which implemented ADO
> methods for connecting to SQL Server, a recordset for loading the data, and
> methods for writing the data back to SQL Server WITH collision checking.
>
> One key to working with SQL Server is to use a special data field / type
> called a timestamp.  It is not a timestamp, it is really a random number
> which is automatically created when a record is created, and updated by the
> SQL Server engine whenever any change is made to the record.  The idea is
> that when you create or read a record, you immediately capture the value of
> that field for the record that will be modified.  When it is time to
> perform the update, you:
>
> 1) Lock the record.
> 2) Compare the timestamp that you retained from created / read the record
> to the value in that field now.
> 3) If the timestamp in the record is the same as your stored value then
> the record was not updated between when you read the record and now.  You
> can safely write your record changes.
> 4) If the timestamp has changed, then something was modified. Since you do
> not know what, you have to perform a compare of ONLY the fields that you
> have modified.
>
> 5) When you read the record initially (to perform a modification), you
> must SAVE the original values (in the class).
> 6) If the timestamp differs, then you have to READ the record a second
> time and compare old values to current values to see what specific fields
> were changed.
> 7) If a field that you have modified is different from the OLD value, then
> THAT field was modified and you cannot blindly overwrite the changes.
> 8) Any fields that were not modified can be safely overwritten.
> 9) Business rules have to be determined to figure out how to handle edit
> collisions.  Often it comes down to warning the user and discarding the
> changes, rereading the (modified) data and allowing the user to re-edit as
> they see fit.
> 10) If a record is deleted while your user is editing it then????
> 11) Eventually (most of the time) perform the write and unlock the record.
>
> When performing a write, you will be updating ONLY changed fields.
>
> As you can see from this discussion, simply writing changed data back
> without performing this analysis will cause previous edits to be lost and
> will cause users to not like you.
>
> You can also see why a class is so useful.  We can create a CRUD instance
> for each open form.  That instance can hold all of the old data, the code
> required to perform the analysis, the connection and recordset objects and
> so forth.  To stuff all that code directly into a form class is insanity.
>  When I did it, I created a helper class to hold the field name, the old
> value and the new value.  This helper class would be created when the
> record was read initially, one instance for every field read.  The field
> name was stored (for creating the UPDATE SQL statement later) and the value
> at the time originally read.  If a timestamp was different, then a new read
> would happen and the NEW value placed in this helper class.  The helper
> class then had the old value and the new value and code would return a
> "changed" flag if the old and new values were different.  The CRUD class
> would read the (modified) record, update every helper and then look for
> changes. If any changes were for a field that my user had modified, then I
> had a problem, an edit collision.  If not then, even though the record had
> been edited, I could simply write my user's changes back - and ONLY my
> user's changes.  Since there were no edit collisions, my write of ONLY
> edited fields would not corrupt previous edits.
>
> Simple in theory, moderately complex in practice, required in any case.
>  Unless of course you want to just use a "last edit survives" strategy.
>
> And you can see why the CRUD class is the center of the framework.  It
> does a lot of the work.
>
> John W. Colby
>
> Reality is what refuses to go away
> when you do not believe in it
>
> On 3/24/2014 9:30 PM, Jim Lawrence wrote:
>
>> Hi John:
>>
>> I must admit that number, to me is exceptional. Do you really feel that,
>> in an active company, a single network of bound Access databases could ever
>> be supported?
>>
>> I still feel that, for the Windows desktop (excluding Windows 8.x of
>> course), an Access FE is the best database presentation manager ever built
>> so if you feel that a set of unbound classes could ever be created, for the
>> product, I would be very interested.
>>
>> Jim
>>
>>
>>
>
> ---
> This email is free from viruses and malware because avast! Antivirus
> protection is active.
> http://www.avast.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>