How to make MS-Access Log file?

[eneyardi]

Hi Guys
I need to set up or create a routine will keep a log file of what was done by whom
Is there any easy way or is it implemented already in Ms access 2007, i need a log file which keeps track of any interaction(abc d) with the database

Any idea, somebody already did it, any example, please let me know

[malcolmk]

Depending on exactly what you want to track then the tracking table could become huge.
the way I would go about it is to have every user login with a unique id, have a table called tracking with fields somewhat like Userid( obviously the user logged in ) When(date and time)Formname(n ame of form accessed by user) Operation( values of viewed, edit,add,delete ,recordnum,fiel d)
Each time a form is opened, record edited, deleted, added the event fired writes info to the tracking table
Don't know if this would reduce application sped too much, never tried it out. My thoughts anyhow on possible way to implement. ; )

[eneyardi]

Thanks, just what i thought too, our 4380 records will be multiplied for only to view every records routing history. i can depend it now on program presentation that is not adviceable to have that function.

[Oralloy]

eneyrardi,

I've built audit-trails into access before. They aren't difficult. You can hide the whole thing in a module with one or two interface functions, and it's done.

The problem is not in implementing the data storage mechanism. The execution time for the mechanism is generally so small, that it's never a concern.

Where the problem lies is in determining what to audit and how. For example, if the user has unrestricted access to a table, you will basically be unable to audit the changes. On the other hand, if all data access is controlled through a forms based application, you can simply capture each record write event and implement the audit there.

Needless to say, this can get to be messy, quickly.

Also, be careful about what you record. I worked on a database once, where we regularly wrote measurement 100,000 data records. In this situation, there is almost no value in tracking each data record; instead, the top-level measurement is more the item to track.

So - be sure of your requirements, and be certain you can even record the information. Requirements, requirements, requirements.

Hopefully that helps a little.

Cheers!
Oralloy

[eneyardi]

How to do the audit-trails into access 2007?

[eneyardi]

Can u send me a link of a sample program of audit_trails?

[Oralloy]

Well, it's pretty simple, actually:

I'm doing this off the cuff, so please bear with my mistakes.

CREATE TABLE [Audit Trail]
(
  id AUTONUMBER NOT NULL PRIMARY KEY,
  user CHAR(16) NOT NULL,
  timestamp DATETIME NOT NULL,
  action CHAR(254) NOT NULL,
  description CHAR(254),
  comment MEMO,
);

And perhaps a subs to insert records:

Public Sub log(ByVal action As String, _
               Optional ByVal description As Variant = Empty, _
               Optional ByVal memo As Variant = Empty)
  Dim q As String

  q = "INSERT INTO [Audit Trail] " & vbCrLf
  q = q & "  ([user], [timestamp], [action], [description], [memo]) " & vbCrLf
  q = q & "  VALUES('" & UserName() & "', " & vbCrLf
  q = q & "         #" & Now() & "#, " & vbCrLf
  q = q & "         '" & action & "', " & vbCrLf
  q = q & "         " & IIf(IsEmpty(description), "NULL", "'" & description & "'") & ", " & vbCrLf
  q = q & "         " & IIf(IsEmpty(memo), "NULL", "'" & memo & "'") & ")"

  Database.Execute(q)
End Sub

At least, that's a quick-and-dirty start.

Better to use parameterized query, that way you don't have to worry about value translations, quoting, or recordlimits.

Good Luck!
Oralloy

[eneyardi]

thanks, its pretty cool but it saves all the changes in records. i want only those add record to be audit, how to do that buddy?

[Oralloy]

eneyardi,

The problem with audit trails in Access is that there is no database level mechanism that implements auditing.

What that means is that you have to implement auditing as a part of your application. If the user can access a table directly, without going through a form (or other programatic) interface, then the audit trail can not track every desired change.

The code example I provided is just a simple module to write audit trail records to a table in the database; nothing more. It's the core of an application level audit trail implementation. What you choose to write is, of course, up to you. If all you want to do is record "new" records, then that's all you have to audit. If you want more sophisticated auditing and information, you'll have to implement it.

Am I making sense?

[eneyardi]

i get it now, thanks alot..