[AccessD] Unbound Form Check For Changes

John W Colby jwcolby at gmail.com
Mon Mar 24 21:15:05 CDT 2014


 >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



More information about the AccessD mailing list