Allen Browne Audit Log

[David]

Hi
I have been testing the log with no problems. However, it is time to do
something with the data in the log table. But with no examples of how to
view the audit data on the website, I thought it would be a good idea to ask
on the newsgroup if any one had extracted the data from the table to a form
/ query so that the data could be read in a user friendly way.

To start with I would just like to be able to display the fields that had
been changed in each record.


If anyone is interested in a log the link is
http://allenbrowne.com/AppAudit.html

An example of the table could be:


audID audType audDate Followed by the
rest of the field you have in your table
1 EditFrom 1/02/2005 12:39:46
2 EditTo 1/02/2005 12:39:46
3 Insert 2/02/200509:09:30

Any help would be appreciated

 

[Rob Oldfield]

I haven't used the code... but you're missing something key. There will be
a link from an edited record to AudID 1 (in your example)

 

[David]

Rob, I do not understand the point you are making.
The example is the first few fields the log produces, just to give an idea
of the field format the code ( see link)produces.

 

[Rob Oldfield]

That audit table has a number (long integer) field (named the same as the PK
of your table) which you would use. That would allow you, for example, to
create a subform that displayed edits made to that record.

At that point I think you start running into limitations of what Allen has
put together. If you wanted to display just the fields that have been
changed then you'd need to create some code that looked at the two linked
EditFrom and EditTo records that will (?) have sequential AuditID (i.e. the
newly added autonumber field on the audit table) values. The question mark
there is regarding whether those ID values are guaranteed to be sequential
in a multi-user environment... I think they are - one of the benefits of
using the temp table - but only having had a brief look at the code I
couldn't guarantee it. From that code you could generate something like the
text "Field whatever was changed from x to y by user z on 2pm 1/1/5" and
then display that.

I use the word 'limitations' above when I don't really think it's right.
Allen's idea is, I think, to provide a 'system' audit... not really one
that's built for displaying its results prettily. I'll come back to it
later and see if I can come up with some code for you.

 

[Rob Oldfield]

OK. I've had a chance to have a play and come up with the code below. It
works at a basic level but needs error handling and more testing - for
example, I haven't taken null values into account.

You could use it by adding a label onto the form and then dropping

Me.lblControlName.Caption = AudText("auditTable", "PKField",
Me.PKControlName)

into the current event of the form. I obviously haven't bothered commenting
it, but hopefully you can see what's going on. One thing to note - it will
try to find changes in the audit table so will pick up the change from
EditFrom to EditTo - so I've told it to skip fields beginning aud. So you'd
also need to ensure that the PK added to the audit table begins with aud as
well.


Function AudText(sAudTable As String, sKeyField As String, AudID As Integer)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Dim sql As String
sql = "Select " & sAudTable & ".* from " & sAudTable & " where " & _
sKeyField & "=" & AudID & " and audtype like 'Edit*'"
Set rs = db.OpenRecordset(sql)
With rs
If .EOF Then
AudText = "No edits made"
Else
Dim arrComp() As String
ReDim arrComp(1, .Fields.Count - 1)
Dim i As Integer, fld As Field, EditCnt As Integer
Dim j As Integer
i = 0
For Each fld In rs.Fields
arrComp(0, i) = rs.Fields(i).Name
i = i + 1
Next
.MoveLast
.MoveFirst
EditCnt = .RecordCount / 2
For j = 1 To EditCnt
i = 0
For Each fld In rs.Fields
arrComp(1, i) = CStr(.Fields(i))
i = i + 1
Next
i = 0
.MoveNext
For Each fld In .Fields
If Left(.Fields(i).Name, 3) <> "aud" Then
If CStr(.Fields(i)) <> arrComp(1, i) Then
AudText = AudText & .Fields(i).Name & _
" changed from " & arrComp(1, i) & _
" to " & CStr(.Fields(i)) & vbCrLf
End If
End If
i = i + 1
Next
.MoveNext
Next
End If
End With
rs.Close
Set db = Nothing
End Function

 

[Rob Oldfield]

....there again... I've changed my mind. That approach is far too processor
intensive - having to recalculate all the edits every time a record is
accessed. I'd change it to...

Set up a new table containing fields something like... ChangedWhen, ByWho,
ChangeDescription, PKField.

After writing the EditTo record, run a similar process to the one I used
before, but use it to append a record describing the change onto that new
table.

 

[David]

Hi, Rob

Thank you for your interest in my problem.
For anyone following this I have received the following from Allen Browne:-
The article at:
http://allenbrowne.com/AppAudit.html
explains how to record two records for an edit: one containing the old
values ('EditFrom') and the second the new values ('EditTo').

If you want the two records to be combined into one, you could use a
subquery to identify the first EditTo record after the EditFrom for the same
key value and the same user.

If subqueries are new, Microsoft's introduction is:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
Once again thank you for your time
All the best
Dave

 

[Rob Oldfield]

Hmm. Same kind of idea, but I don't think a straight subquery is going to
be able to return the field(s) that are different.

 

[David]

Hi Rob
As a test I extracted all of the EditFrom in a query, then all of the EditTo
as a query, I then joined the two queries in a new query using the date as
the link. I then displayed using a form via If field x <> field x.x then,
text is red this worked ok.
But would be a bit long winded to code over 50 fields.
Is it possible via SQL to write code so that only the field that are not
equal are displayed in a query using the above EditFrom EditTo query
principle ?

 

[Rob Oldfield]

I don't think so. I think you're going to need to be playing with some VBA
similar to my earlier code to do it.

For reference - and I know that you were only testing - but a better link
between the two queries would have been to calculate the difference between
the two autonumber fields and return records where that is 1. Using the
date would go wrong where two or more edits had been done on the same day.