Audit in Access

[mari_hitz]

Hi everyone, for the last two days you guys have been saving my life, hope you can give me a hand with this too.

I would like to perform something, that I believe is really difficult. I have a database that has users and passwords to login. This database sends e-mails to a list of people that I have in a table by pressing a button that contains my code.
I have another table to track changes with the following fields: "ChangeID"; "Date"; "Recipients"; "User". I would like to know if there is anyway I can track who press the button by adding to some sort of code that after the button is pressed, a new audit ticket is created by adding in the Change ID field automatically a number (this field is set as Autonumber); on "Date" the current date; on "Recipients" the e-mails of the people to whom the communication was sent (the e-mail is found in another table) and the "User" by completing with the user that the person has logged in.
After paste all this information as a new line to the tracker table I would like a pop up to appear saying: "A ticket number has been assigned for this action: #ticket number".

Is this possible? or I am not going to be able to perform this?

Thanks!

 

[Rx_]

Environ("username") is a function that returns the user ID
This function calls a table named TLogUsage. It is very basic with an autocounter.
In my code, this can be placed in different events to log values. To use
dim MySuccess as boolean
MySuccess = LogUsage("my Form Name", _
"Procedure i.e. Click", "OK Button")

Another function with an added field tracks actual values put into a field or other information.

Function LogUsage(ByVal strFormName As String, _
    strCallingProc As String, Optional ControlName) As Boolean
10       On Error GoTo Err_LogUsage
          Dim rst As DAO.Recordset  ' The tLogUsage table
20            Set rst = CurrentDb.OpenRecordset("tLogUsage", 2, dbAppendOnly + dbSeeChanges)
30            rst.AddNew
40                rst![UseDate] = Now()
50                rst![strFormName] = strFormName
60                rst![CallingProc] = strCallingProc
70                rst![UserName] = Environ("username")
80                If Not IsMissing(ControlName) Then
90                    rst![ControlName] = Left(ControlName, 75)
100               End If
110           rst.Update
120           rst.Close
130           LogUsage = True
Exit_LogUsage:
140       Set rst = Nothing
150       Exit Function
Err_LogUsage:
          ' just resume next and the usage is not logged
  'MsgBox Err.Number & Err.Description
160       Err.Clear
170       Resume Exit_LogUsage
End Function

 

[The_Doc_Man]

The audit function supplied by Rx_ is a start. Here are some things that you need to recognize, though. If your version of Access is 2007 or later, your username has to come from the external environment. I.e. your machine needs to have a username/password based login just to open the desktop. Older versions of Access allow you to setup a login, but the Environ function does not apply to Ac2003 or earlier user-level security. Therefore it is important to know which kind of security you are using.

Next, two things are important to remember about auditing.

First, you are GOING to consume disk space when you audit. On my system, even with rudimentary logging I will create 30-50 Kb per month. Since I export my audit logs on a regular basis, I am now at a point of equilibrium (more or less).

Second, Access won't audit anything that you don't tell it to audit. So if you want to capture something, you will have to customize your audit routine to include whatever you wanted to include and omit all else. Because there is a danger (see First important thing). There is a temptation to audit EVERYTHING. This means you WILL be cleaning up old logs. It means you WILL have to watch your system.

The more practical solution is to decide ahead of time what you want to audit, and ONLY audit that information, nothing else. Even then, you might take the approach that you only audit if/when someone commits their update. In my system, I have a COMMIT button and a CANCEL button. I only audit what gets a COMMIT button click. If you want to tantalize me with lots of changes but then just click CANCEL, I'm not the one wasting time.

Anyway, auditing is not only possible but not that difficult as long as no user EVER sees queries or tables directly. If all users come in through forms and cannot ever see a real table or a real open SELECT query, then they cannot do anything without giving you the chance to to make your audits. If the users can see tables or queries directly, you have a place where mayhem can occur and you won't be able to audit there, either.