[AccessD] Watching data

Jim Dettman jimdettman at earthlink.net
Sun Feb 29 10:14:56 CST 2004


John,

<<In order to do that I need to figure out the "drill down" thing - I
believe
that Drew was the one that assured me that it is possible.  I just have to
see if I can find the hints of how.  It had to do with opening the query and
inspecting the properties of the fields.>>

  I missed earlier parts of this discussion, but I'm assuming what you mean
by "drilling down" is looping through all the controls, then yes it
certainly can be done.  Any of the code floating out there to resize forms
contains the logic required.   If you don't have access to something like
that, it's a pretty straight forward matter.

  Starting at the form level, you'd use the controls collection to iterate
through all the controls for an object.  Just be aware that you'll need to
handle container controls (i.e. page frames and subforms), so it will be a
series of nested loops.  You might want to try and make it a recursive
procedure.  Performance wise, you also might want to restrict which controls
are looked at.  For example, if you don't place controls in anything other
then the detail section of forms, then no need to check the header and
footer.

  Once you locate a control, you use the TypeOf function to figure out what
it is and if you need to deal with it. I would not bother checking the
.enabled or .locked properties, but simply check .oldvalue vs .value, as it
should be faster that way.

  This would all be done in the BeforeUpdate event after your sure your not
going to cancel for any reason.

  As far as the change log, I've always used the approach of one record per
field change:

tblLogID  - Autonumber -PK
LogDateTime - D/T
LogType   - String - "A"dd, "D"elete, "U"pdate
TableName - String
RecordPK  - String
FieldName - String
TypeOf    - Numeric - Indicates where "NewValue" is stored
NewValue1 - String
NewValue2 - Integer
NewValue3 - Long
NewValue4 - Single
etc....

  I don't bother to hold the .oldvalue because you'd be duplicating it in
the table.  You can also see that I don't bother to try and convert the
.oldvalue into a common type (i.e. a string), but rather store the actual
value.   If I had access to the raw binary data, then I'd do so, but without
that, this seemed to be the cleanest way.

I've used two different types of logic in working with the log file:

Roll forward - used to recreate a database at a specific point in time.
1. Check point (backup)
2. Apply log changes in a "roll forward" to a specific date and time.

Auditing:
1. Log file records are maintained as long as possible.

  I've primarily used this in HR type apps or others where it was required
to recreate the data at a specific point in time.  I haven't had to use
logging/auditing in quite some time, but it was workable.  I went with
strings for the table name and field names, but I suppose you could come up
with some type of internal tracking system to use a numeric ID of some type,
but that is probably overkill. The only gotcha there is if you rename a
table or field.  Generally, that doesn't happen too often for me.

HTH,
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: Saturday, February 28, 2004 11:29 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Watching data


Jim,

>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.

Understood.  Fortunately "everything done in forms" describes the database.
This thing is an insurance claim processing system.  Users take calls, make
calls, talk to doctors, investigators, claimants and so forth, and document
everything using forms in the database.  My client then advises the
insurance company whether or not to pay the claim.  As such I have "complete
control".  Users don't go in to tables to edit things.  I use a handful of
processes that automate receiving data from the Insurance company (payment
records, new claim numbers etc.) where I take spreadsheets attached to daily
emails from the insurance company and use them to update the database but
nothing that I need to track here (I do track it in other ways of course).

I have a very small number of forms that all personnel use to edit the
database.  As such I can "brute force it" if I wish.  My first inclination
was to do a class directly in the FE that I instantiated in each form that
needs monitoring.  The class is told the controls to watch and logs any
changes to those controls.  I may still do it that way, but as I have
indicated I would prefer to embed this functionality in my framework so that
I can offer this to any client using the framework, turn it on and off using
SysVars at the FE or even the form level etc.

We'll see.

In order to do that I need to figure out the "drill down" thing - I believe
that Drew was the one that assured me that it is possible.  I just have to
see if I can find the hints of how.  It had to do with opening the query and
inspecting the properties of the fields.

If that is possible I may have a killer feature since my framework already
supports form / control classes.  It would be reasonably easy to add this
functionality into those classes and go.

I am thinking of adding a new DataLogging class to handle this stuff though.
The reason is that it would be useful to be able to select groups of
controls to monitor - as in this example "just the address portion".  By
designing a class, the form could load the DataLogger class and pass it
controls (actually the controls' class) such that an instance of the class
then performs the monitoring of a group of controls.  In the event that
several different groups of controls need monitoring, the form class just
loads more instances of the class passing the specific controls for each
group being monitored.

If all I ever anticipated was a single group I could just do it in a single
collection in the form class itself, but by building a class to do this, I
can have more than one group of controls being monitored if necessary.  The
logger class knows how to poll its group of controls for changes and write
the old/new/tbl/field info to the log table.  I think that would be sweet,
and would nicely encapsulate the process.

John W. Colby
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman
Sent: Saturday, February 28, 2004 3:27 PM
To: Access Developers discussion and problem solving
Subject: RE: [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


--
_______________________________________________
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