[AccessD] Watching data

Jim Dettman jimdettman at earthlink.net
Sun Feb 29 13:37:26 CST 2004


John,

<<No, by "drilling down" I mean discovering what the SOURCE of the data is
by
(I think) opening the query that the form is bound to, looking up the field
that the control is bound to, and looking at the properties of that field in
the query to discover what table / field the data comes from.>>

  Thought I had missed the boat on that one<g>.

<<Thus when it's time to log data and we want "table / field name / oldval /
new val" how do we discover the Tablename / Fieldname portion?>>

  I used the brute force approach in the past; I coded the table and field
name that I wanted logged in the tag property for each control.

  Without doing that, you'd need to grab the form's recordsource and figure
out what it is.  It could be a table name, query def, or SQL statement.  The
first would be easy, as from that point you'd only need the controls record
source.  The second you can figure out easy enough, but parsing it would be
a problem, especially if it in itself is based on sub queries.  That applies
to querydef's as well.

  I'm not sure what Drew was thinking of, but I don't see any easy answers
there.

  I just thought of another one; what if you assign the forms recordsource
to a recordset variable in code so you can do transactions?  I've never
looked at runtime to see what that looks like to a form object.


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


>but I'm assuming what you mean by "drilling down" is looping through all
the controls

I do control collection iteration collection all the time, starting with my
form class which has a control scanner (as I call it) which iterates the
controls collection loading classes for each control found.

No, by "drilling down" I mean discovering what the SOURCE of the data is by
(I think) opening the query that the form is bound to, looking up the field
that the control is bound to, and looking at the properties of that field in
the query to discover what table / field the data comes from.

When a form / field is bound to data, what the data is can be non-trivial to
discover.  For example the form could be bound to a query.  The developer
decides that rather than leaving the field names of that query alone, he is
going to rename CL_Addr1 to Address1 using field name aliases.  Further the
query could be two or more tables linked together in the query (and still
editable) so that fields from the  claimant and a specific claim are all
bound to controls on a form.  Now when the data logger tries to log the
data, what table is the data coming from, the claimant or the claim?
Further is Address1 the actual field name in the table or is it an alias?

Thus when it's time to log data and we want "table / field name / oldval /
new val" how do we discover the Tablename / Fieldname portion?

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


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


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