469,315 Members | 2,141 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

How to make MS-Access Log file?

180 100+
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(abcd) with the database

Any idea, somebody already did it, any example, please let me know
Jan 10 '11 #1

✓ answered by 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?

9 24430
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(name of form accessed by user) Operation( values of viewed, edit,add,delete,recordnum,field)
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. ; )
Jan 10 '11 #2
eneyardi
180 100+
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.
Jan 10 '11 #3
Oralloy
983 Expert 512MB
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
Jan 10 '11 #4
eneyardi
180 100+
How to do the audit-trails into access 2007?
Jan 11 '11 #5
eneyardi
180 100+
Can u send me a link of a sample program of audit_trails?
Jan 11 '11 #6
Oralloy
983 Expert 512MB
Well, it's pretty simple, actually:

I'm doing this off the cuff, so please bear with my mistakes.
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [Audit Trail]
  2. (
  3.   id AUTONUMBER NOT NULL PRIMARY KEY,
  4.   user CHAR(16) NOT NULL,
  5.   timestamp DATETIME NOT NULL,
  6.   action CHAR(254) NOT NULL,
  7.   description CHAR(254),
  8.   comment MEMO,
  9. );
And perhaps a subs to insert records:
Expand|Select|Wrap|Line Numbers
  1. Public Sub log(ByVal action As String, _
  2.                Optional ByVal description As Variant = Empty, _
  3.                Optional ByVal memo As Variant = Empty)
  4.   Dim q As String
  5.  
  6.   q = "INSERT INTO [Audit Trail] " & vbCrLf
  7.   q = q & "  ([user], [timestamp], [action], [description], [memo]) " & vbCrLf
  8.   q = q & "  VALUES('" & UserName() & "', " & vbCrLf
  9.   q = q & "         #" & Now() & "#, " & vbCrLf
  10.   q = q & "         '" & action & "', " & vbCrLf
  11.   q = q & "         " & IIf(IsEmpty(description), "NULL", "'" & description & "'") & ", " & vbCrLf
  12.   q = q & "         " & IIf(IsEmpty(memo), "NULL", "'" & memo & "'") & ")"
  13.  
  14.   Database.Execute(q)
  15. 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
Jan 11 '11 #7
eneyardi
180 100+
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?
Jan 12 '11 #8
Oralloy
983 Expert 512MB
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?
Jan 12 '11 #9
eneyardi
180 100+
i get it now, thanks alot..
Jan 13 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

14 posts views Thread by BOOGIEMAN | last post: by
reply views Thread by Sean W. | last post: by
3 posts views Thread by Christian McArdle | last post: by
18 posts views Thread by Rob R. Ainscough | last post: by
5 posts views Thread by John Smith | last post: by
7 posts views Thread by Joe Ross | last post: by
42 posts views Thread by Martin Jørgensen | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.