[AccessD] Watching data

John,

<<Make sense?>>

  Totally.  Unfortunately, it's pretty rough to do in Access because there
are no engine level triggers in JET.   With Access, everything needs to be
done at form level and it will never be fool proof as anything done directly
in a table will by pass your efforts.

  In products like SQL Server, this type of feature is generally built-in
and it falls outside your app.  In others, like VFP, you have to do the
work, but it's quite easy to do.

  For example, right now in VFP, I can very easily define a trigger at
engine level for adds, deletes, or updates.  On an update, I can compare
.Oldvalue and current value on every field in a record and write a log as a
separate table/file.  I could also modify my base classes at the
intermediate class level (I have 4: VFP, Framework, Intermediate Class, and
App specific classes). This would be the approach your talking about.
Another alternative is that there is a commercial product I can buy that
hooks into the framework I use (but it uses the engine level triggers).

 BTW lack of control at the engine level is one of the reasons I've moved
away from Access.  But if the JET team had added them, it would have stepped
on too many SQL Server toes<g>.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John W. Colby
Sent: Friday, February 27, 2004 5:03 PM
To: AccessD
Subject: [AccessD] Watching data


I need a system for watching specific data fields in specific tables for
changes.  For example, if the Policy holder address changes, the claimant
address changes, the Payment location (address) changes etc.  If ANY of
these change then I need to gather the information and at the end of the day
email a report to the client (the insurance company) spelling out the
changes, what object the fields belonged to (Claimant, Policy Holder etc.).

Make sense?

Of course I could launch into building code in every form I can find where
these objects are used and this info can be saved.  However this seems like
a "framework" kind of task.  I envision a class (let's call it
dclsMonitorCtlChg for now) in the framework that the form class loads if a
form class method (perhaps MonitorCtlDataChg(ParamArray varCtls as
variant) ) is called with controls specified.

The form class already has a collection of the classes for each control's
class.  The form class MonitorCtlDataChg() could grab a pointer to each of
the controls that this method says needs to be monitored and pass them to
the dclsMonitorCtlChg which would place them in a collection.  Then a form
event or events (Before update, After update) could call a method of
dclsMonitorCtlChg telling it to "look for changes in your control set".  The
class could raise an event or simply return a value to the form caller if
any change was detected.

Of course it would then be useful to know what controls (fields) were
changed, the old value and the new value.  This would allow the application
to generate a report:

Object Monitored (Claimant)
Field: 	Addr1: OldValue: NewValue
		Zip: OldValue: NewValue

IOW the claimant moved to a new location, but in the same city, just changed
the address1 and the zip.

So.... is anyone doing anything like this?  If so any words of advice,
things to look out for etc?

John W. Colby
www.ColbyConsulting.com


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com