Track Changes in a database

Joelle101

New member
Local time
Today, 10:00
Joined
Mar 31, 2008
Messages
3
I have searched for quite some time how to track changes in a database and i could not find what i am really looking for.
I have a database that has 52 tables,some of them are linked each other others not.so i would like to track any changes made to any table.
i have managed to do it for one table but it's not what i am looking for.
What i did,is to create a form based on one table(in my case it's status) and a table named Audit(this one will contain the changes made in the table which the form is based on)
on the Before Update event of the from i pasted this code
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call TrackChanges(Me, StatusNumber)
End Sub

Code of TrackChanges function
Code:
Sub TrackChanges(frm As Form, recordid As Control)
   'Track changes to data.
  'recordid identifies the primary key field's corresponding
  'control in frm, in order to identify the record.
Dim cDQ As String
cDQ = """"
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  Dim strSQL As String
  On Error GoTo ErrHandler
  'Get changed values.
  For Each ctl In frm.Controls
    With ctl
 
    If .ControlType = acTextBox Then
      If .Value <> .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        strControlName = .Name
        'Build INSERT INTO statement.
        strSQL = "INSERT INTO " _
           & "Audit (EditDate, User, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & cDQ & Environ("username") & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ & ")"
        'View evaluated statement in Immediate window.
 
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      End If
    End If
    End With
  Next
  Set ctl = Nothing
  Exit Sub
ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub
This code works really good and i get all the changes in the Audit table
My problem is ,Is there a way to trackchanges made in all tables without making 52 forms??:confused:

Thank you for your help
 
No. You need to call this code from the AfterUpdate event of a form. Why aren't you using forms now to edit the data? Any database that is used by more than one person or used by a person who didn't create the database should be locked down and users should ONLY have access to forms and reports. They should NEVER be allowed to tinker with tables, queries, or macros or modify forms/reports/modules.
 
Pat Hartman,
Thank you for you quick reply but i still have some problems:
-if i call this code on the AfterUpdate event it does not work because the old value is not there anymore.
-About changing data using forms means that i will have to have as many forms as tables which i think is not helping me here
-and regarding locking down the users to have access only on forms and reports is not really what i want,i want to track even my own changes as an admistrator.
 
Sounds like you have tables whose names contain "data". Are your tables all the same format? If you combine them and add a column with the additional "data", you will have a lot less work to do to provide the users with a good form based interface. Once you combine all the data into one or a few tables, you would use queries with criteria to separate the data again into the separate sets you have now.
 

Users who are viewing this thread

Back
Top Bottom