[AccessD] ...building, managing, and using a page favorites list

jwcolby jwcolby at colbyconsulting.com
Sun Mar 8 23:22:50 CDT 2009


William,

 > ...but if you have a better solution, like I said, I'm all ears :)

I was not intending to step on toes.  From the thread it sounded as if you were discussing a full on 
generic solution for an audit trail, and that you had something in place that you already used.  For 
a one-off situation I would suggest a one-off solution, which it sounds like you already have.

A full on generic audit trail gets sticky in a hurry as my observations describe.

If anyone is interested in designing a full blown generic audit trail, these kinds of problems have 
to be solved.  You quickly run into the need to normalize to reduce the sheer size of the text data 
that you would otherwise be storing every time.  You also need to make decisions about whether you 
are going to try to discover the name of the actual table that a given field is coming from or just 
be content with the object that the form is bound to.

For my code which applies a standard date format across the application I did some of this stuff. 
Using DAO and the form's recordsetclone, I did drill down into the field object which has a ton of 
information about where the data comes from.  At the time I wasn't really looking at it from an 
audit trail perspective however so I can't really say whether it would deliver that kind of detail.

I do know that the field object has several properties that would likely help:

1) SourceTable
2) SourceField
3) DataUpdateable

I placed the code that did the drill down into my clsFrm control scanner.  As I scanned for controls 
I was looking for the data type to see if it was a date.  I passed the data type off to the control 
class just so that it could later do any other system wide formatting or input mask kind of thing.

In the case of audit trail code you would already have the source table and field at your 
fingertips.  This would be the place to perform the normalization such that if a source table name 
was not in a normalization table already (not recorded yet) then the clsFrm code would just store it 
and get the PK to use in the audit trail.  Likewise with the field name.  Self normalizing audit 
trail code.

There is a lot of development effort there though and I don't have a client paying me for that.

John W. Colby
www.ColbyConsulting.com


William Hindman wrote:
> ...lol ...you live to make my life difficult :)
> 1) bound fields in a badge issuing system designed to be as fast as possible
> ...every record update/add has the time and user recorded in the record 
> itself ...I don't use the add/insert function in the basAudit for anything 
> at the moment.
> ...the audit changes functionality was added when we needed to know which 
> user was generating bad data ...it worked.
> ...there is no delete functionality ...if a mistake is made, the user 
> cancels the print and corrects the badge data ...no deletes are allowed 
> ...if a mistake is made, the user puts it in the record notes field and 
> moves on ...it is corrected after the show by users who know what they are 
> doing ...I never let the temp worker at the show do anything that is 
> irreversible.
> 2) the table that the data comes from is in Const txtTableName at the top of 
> the form code, no query, no SQL ...its a table produced from the main mdb 
> before the show ...I fully grant you that it isn't an everyday solution but 
> it sure keeps me from putting the real data at the mercy of temp hires 
> ...now if you have a better idea for applying this to the real world, I'm 
> all ears :)
> 3) granted, but its not :) ...any class resolution should provide the 
> functionality for me to id controls that are not to be audited ...for 
> instance, I sure as hell don't intend to audit memo fields or fields that 
> are concatenations :)
> 4) granted ...which is why I audit only those records/fields that are 
> critical ...no one promised you a rose garden :)
> ...but if you have a better solution, like I said, I'm all ears :)
> 
> William
> 
> --------------------------------------------------
> From: "jwcolby" <jwcolby at colbyconsulting.com>
> Sent: Sunday, March 08, 2009 9:04 PM
> To: "Access Developers discussion and problem solving" 
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] ...building, managing, and using a page favorites 
> list
> 
>> Other problems I see:
>>
>> 1) The AuditTable needs an "Edit type" field.  We need to know whether the 
>> data in the table was an
>> edit to the field, a new record, or a delete.
>> 2) The table that the data comes from... how do I determine that?  Am I 
>> drilling down into the DAO
>> under the control?  What happens if this is a complex query?  Even worse a 
>> complex SQL statement?
>> 3) The field name can be determined from the control source, but it may be 
>> an alias if the form's
>> recordsource is a query or SQL Statement.
>> 4) Recording full on text fields for the table name, field name and 
>> computer name is going to be
>> very expensive.  If you have a form with 40 fields and you are performing 
>> an add record...
>>
>> John W. Colby
>> www.ColbyConsulting.com




More information about the AccessD mailing list