[AccessD] Unbound Form Check For Changes

I like the method you use:

...but I would tend to use a real timestamp than going to a random number generator. Considering a system can create a timestamp number with six places below the decimal point, the odds of a data conflict is almost mathematically impossible. Additionally, a record can be tagged (a field) as being in "process mode" or just have a field where the current-user code is stored (this will allow the situation where a single user is working from multiple stations) and again only then does the user system have to be concerned with possible modification conflicts. 

...A file handling transaction logs can be used to document and record data changes...this is particularly important with invoicing as these data records are very active and can be used by multiple people, sometimes at the same time.

...another technique that I used when modifying a record was to AND and OR every field in the record, store that resulting number and when about to update the record, run the process again. If there was a difference in the two values, then handle it. 

...Through overnight processing all flags would be cleared by locality and/or inactivity duration.    

...deleted records have always been an issue. In my way of thinking records should never be deleted and should only be toggled/flagged active or inactive. If some user is somehow in the process of modifying a deleted record it can be easily handled (This also resolves the serious error on all big active databases where a situation called "the deadly embrace" used to be able to freeze a system up and require the DBA to unlock it). There should always be a garbage collection system that archives records that have been truly deleted...even then nothing is ever completely deleted. Sometimes records have to be recovered from archive; again not a complex issue.  

I have used these methods in most of my databases and in over twenty years and millions of records there are probably only a couple of situations, I can recall that manual intervention was required and then all the data was available in the transaction logs and archive files...and the causes for these issues were never discovered and I suspect a internal database failure but that was outside my realm of expertise.    

Jim

----- Original Message -----
From: "John W Colby" <jwcolby at gmail.com>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Monday, March 24, 2014 7:15:05 PM
Subject: Re: [AccessD] Unbound Form Check For Changes

 >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