Audit Trail Problem

[Guest]

I need to create an audit trail for my database. I have referred to Allen
Browne's audit trail and read all posts regarding audit trails. However, my
database has 200+ tables and forms; therefore, Allen Browne's audit trail
example is not logical for me to use since I would have to create two tables
for each table I already have (please correct me if I am wrong).

For my database I have gotten part of my audit trail to work correctly. My
username, time changed, table changed, and user type are working correctly;
however, I can not get the previous value and new value to work. I have
tried using the OldValue and Value properties, but I can't get them to be
entered into my audit table.

Any help would be wonderful! Thanks in advance!

 

[David C. Holley]

It sounds as if you capturing the changes and saving them in a table. I
would take the information and spin it out to either a delimited text
file or an Excel file. I would probably go the text route. At any rate,
I would utilize a file naming schema that uses the log data for the file
name as in 20050809.txt.

 

[Allen Browne]

KatK, have you considered using SQL Sever tables instead of JET tables?

You can still use the Access interface (well, it will need modifications),
but use the SQL Server engine to provide transaction logs, or use triggers
to generate just the details you want.

 

[Guest]

Thanks for your reply. How can I go about doing this? Do you have any
resources you recommend I read to get me started?

I'm guessing since you suggested SQL Sever tables, this is the best
direction to go. However, is there another route to get the new and original
number values from a form passed to the audit trail table? Below is the code
I'm using to get the user type, user id, table changed, and time changed
inserted into the audit table. As I mentioned before, I tried using
ctl.oldvalue & ctl.value properties but couldn't get them to work. Thanks
for all your help!


**********************************
Public Function AuditTrail(FormName As String)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblAuditTrail")
With rst
.AddNew
.Fields("User") = UserID
.Fields("UserType") = UserType
.Fields("TimeChanged") = Now()
.Fields("TableChanged") = Form_MainMenu.cb_TableSelection
.Update
End With

ExitPoint:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing

End Function
*************************************

 

[Allen Browne]

The problem with that approach is that the old values are not available in
Form_AfterUpdate.

You could use Form_BeforeUpdate, but there is no guarantee when that event
fires that the write will succeed. It could fail due to validation failure,
duplicate indexes, or a write conflict, for example. Therefore the solution
requires stashing the values somewhere (temp table, array, ...) in
Form_BeforeUpdate , and then reading them in Form_AfterUpdate.

SQL Server is a big topic on its own. There are heaps of resources on that:
books, courses, knowledgebase and msdn articles, and separate newsgroups for
questions.