Max Wanadoo
max.wanadoo at gmail.com
Mon Mar 9 02:28:56 CDT 2009
JWC, > 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. Just as an aside, to avoid the need to test for this type of thing, I use variants in the audit-trail subroutine and that makes it a bit more generic. As far as an audit trail is concerned I just need to know (others may have different needs) what the original value was and was it was changed to. I am not going to be working with it in the sense that it becomes a date or a value etc that I am going to manipulate as if it was on the form, etc. A sample construct might be: What table was changed. What field was changed. What was the original contents What was it changed to Where was it changed (form name for example) When was it changed Who changed it Notes - a Memo field (I might want to store certain info so this would be an optional param in the subroutine) I would not store any information about the data type, the table properties etc, because for the most part they are not changeable by the user and if I need to refer to them, I can do so directly. It is about data content only for my needs. Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: 09 March 2009 04:23 To: Access Developers discussion and problem solving Subject: Re: [AccessD] ...building, managing, and using a page favorites list 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com