[AccessD] Audit Trails

Rocky Smolin rockysmolin at bchacc.com
Sat Jul 18 18:38:55 CDT 2009


I think you're right Stuart.  The add can go into the same audit trail table
as the edits.  Just nothing it as an add. Now the deletes could be handled
the same way if I use Colby's method of never deleting a record but just
marking it as deleted with a Boolean field.  Then I could just record the PK
(I ALWAYS use Autonumbers) of the deleted record in the same table noting
the transaction type as Delete.

Thinking out loud here...

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Saturday, July 18, 2009 4:25 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Audit Trails

To a man with a hammer, everything looks like a nail <g>

I certainly wouldn't try to trigger an audit trail at the control level. The
logical place is at the 
time you insert, update and delete a record.   

For the insert, you don't need to store any info about the record other than
its PK.

For the delete, you need to store all fields for the record whether they are
displayed on the deleting form or not.

In neither of these cases do you need to iterate through controls.  It's
only the Edit audit that needs iterate through the fields exposed to
controls on the editing form and record which ones have changed.

There is a lot to be said tor using three different audit tables for the
three different cases.
(That's exactly the way I've done it in the past in SQL Server using
triggers).

--
Stuart


On 18 Jul 2009 at 16:48, jwcolby wrote:

> Sounds like a case for a class, sinking the events for the control.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Rocky Smolin wrote:
> > Just Googled Access Audit Trail and got a couple of hits.  This KB seems
to
> > be the most popular: http://support.microsoft.com/kb/197592
> > 
> > Looks like I may have to roll my own for edits - but not too hard - the
> > OldValue is available in the BeforeUpdate event.  So there I could call
a
> > Public function, pass the form name, cycle through the controls looking
for
> > controls that are bound, and create the appropriate audit trail records
for
> > the changed fields. 
> > 
> > It looks, however, like I'd need a second function for the New records
added
> > to the table and another function for Deletes.
> > 
> > Rocky
> > 
> >  
> > 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
> > Sent: Saturday, July 18, 2009 7:40 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: [AccessD] Audit Trails
> > 
> > Dear List:
> > 
> > I am developing an application where the user needs to record changes to
> > fields in several tables - table name, field name, old value, new value,
> > date/time of change, user name.
> > 
> > What is the best approach to this? 
> > 
> > Is .OldValue available after the record has been updated?  If so I could
> > loop through the bound fields checking for changes and add an audit
trail
> > record if changed. 
> > 
> > It would be nice to have this audit trail function packaged in one
function
> > that could be called form anywhere in the app since several tables will
have
> > the requirement.
> > 
> >  
> > 
> > MTIA
> > 
> >  
> > 
> > Rocky
> > 
> >  
> > 
> > 
> -- 
> 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