Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2003
    Posts
    107

    Unanswered: Auditing Changes

    Hi there.
    Can anyone suggest a quick and simple way to log any changes that are made to the database? This is a requirement in the environment that I work in. I need to record:

    userid, date, either SQL statement that was run, or b4 and after values of the tables that were modified.

    please and thanks.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I will usually have a DateUpdated and UpdatedBy fields in the main data table. I set the DateUpdated field to the current time (=now()) and the UpdatedBy to the getuser() (found in the code bank to get the current user loginID) in the AfterUpdate event of the form. This will record changes to any fields on the form. To get further details of what field is actually updated, you may need to add a table which could have fields like: DateUpdated, UpdatedBy, FieldUpdated, OldValue, NewValue. Then in the Afterupdate event of the form, write some code to populate this table with the values.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its a bit fiddly in Access, but it can be done
    JET doesn't have anything like the audit trails of server database products. Unless the security of the db, where the data is actually stored the best designed audit trail can be very very easily compromised... so unless you know your access security don't rely on it....

    As Paul says you can can add some code in the forms on update / on change / on delete events. However there are a couple of potentail gotchas to be aware of.. its possible to do a cut and paste of a record, so its a new record, but the old primary key is still in the data.

    ypu need to test if the record is an addition, deletion or change, you may well find that a new record may also trigger a record change event.

    probably the best thing to do at present is understand which events do what and when. Try putting a message box or write to the debug panel .

    AS regards running an audit log then you need 4 or five columns
    usually this is

    the userid of the person who did the change (don't use Environ btw.. have a look at Dev Ashihs's API Calls in google, or the code bank on this thread by Paul PKStormy)
    the computer ID that made the change (as above use the API calls)
    the time the update occurred then
    the table that was affected
    the data that was affected

    ..you may want to have use a timesatmp column as well so you can attempt to track down events in the audit log which may have been tampered with (where the timestamp doesn't match the time the record claims the event occurred). its quite hard (not impossible, but quite hard) to fake the time the record was updated AND a timestamp. If you have an audit event ID then if a timestamp is out of sequence with its adjacent records and audit event ID then thats another indicator of potentially fishy events.

    How you record the data that has been changed is up to you
    you can find the controls previous value using .oldvalue property (I think, but if it isn't the intellesense thing should pull out the correct property for you. Again a word of caution you need to know what the values were set to and when the old property changes)

    In the past when Ive implemented these sort of logs Ive written the SQL statement that would be used to re-create the data...
    eg my audit text would be
    update <mytable> set blah........
    delete from <mytable> where blah
    insert into myothertable where blah di blah

    so if a table gets corrupted I can identify what needs to be added back to the backed up data to re-create the data set.

    HTH

  4. #4
    Join Date
    Feb 2003
    Posts
    107

    Thanks

    Thanks for the tips guys. I'll review them in detail and try your suggestions out. I have a stupid question though.
    In access can i just use the DoCmd.RunSQL "UPDATE Audit SET userID = 'blah blah blah'"

    This is my first ms access application... you'll have to excuse the stupid questions.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what did the help file tell you?

  6. #6
    Join Date
    Feb 2003
    Posts
    107

    docmd

    i know that i can create a query and save it or run a sql query "on the fly". just wondering if you guys had any comments either way.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DoCmd.RunSQL "UPDATE Audit SET userID = 'blah blah blah'"
    WHERE ...
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I do the same as pkstormy, but I run the updates in the BeforeUpdate event procedure.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dotolee
    Hi there.
    Can anyone suggest a quick and simple way to log any changes that are made to the database? This is a requirement in the environment that I work in.
    Hi

    Is this some sort of legal or mission critical requirement? Will your auditing procedures be subject to audit themselves? If so I would recommend to not use Access\ JET as the storage RDBMS and instead use a server RDBMS instead.

    HTH

  10. #10
    Join Date
    Feb 2003
    Posts
    107

    RE: access vs server based rbdms

    i have no choice in the selection of the db. i've already voiced my recommendation to use another technology set but for this time round, I will have to use ms access.

    Thanks for all your suggestions!

  11. #11
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    Before and after values are probably easier; definitely easier to audit for the users to figure out.

    How about you store all the values to variables when the form is opened? When something happens that causes Access to save, like before closing or moving to a new record, it checks the variables against what's on the form at that time. If there are any differences, store the old and new values to the audit table along with user and date. Your audit table could either be a duplicate of your master table but with old and new fields you fill in, or you can have a field in your audit table called "Updated Field" for storing the field name. #1 is probably fine if you don't have too many fields or tables, but #2 would be better if you have a complex database.

  12. #12
    Join Date
    Feb 2003
    Posts
    107

    sample

    hi lisa,

    Would you be able to include a code sample on how to do this? or direct me to an article?
    Thanks.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Personally I would use unbound forms for this. Actually, I would use unbound forms anyway but especially with this requirement. That way you aren't having to trap events.

    BTW - Lisa - I believe there are control properties in bound forms that allow you to retrieve the old values (I think it is called OldValue...) without needing to use variables. Someone else might be able to wade in with something more concrete.

    HTH

  14. #14
    Join Date
    Feb 2003
    Posts
    107

    oldvalue

    I like your idea about using the oldvalues...
    So far, from my tests, it looks like it'll work.
    Just a question though.
    I'd like to do the following: (the check changes function is written using pseudocode because i couldn't figure out the actual syntax)

    Private Sub Form_AfterUpdate()
    'determine which fields have changed
    checkchanges ()
    End Sub

    Private Sub checkchanges()
    Dim
    Dim ctlTextbox As Control
    For Each ctlTextbox In Me.Controls
    if ctlTextbox.OldValue <> ctlTextbox.NewValue or .Value
    writeAuditLog(oldvalue, newvalue, userid, date, tablename)
    end if
    Next ctlTextbox
    End Sub

    The problem is that there is no such properly called NewValue on ctlTextBox.
    How would I do this? I guess if I can't check all "dirty" fields in one shot, I can write code in each control's afterupdate event handler... but that seems to be a little tedious.

    Please and thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •