Making a user logfile in acess 2007

[mose Mbugua]

Am trying to make an access report which will capture how a user after entering their username and password use system forms in a database. the report should show all the activities that took place after the user logged in. It should also show the time.
How do i go about that in access 2007?
Thanks

[twinnyfo]

mose Mbugua,

The short of it is that you will have to add code to every event procedure fired by every form, and every AfterUpdate event on every control on every Form. This is possible, but the type of thing you are asking for is incredibly extensive, as you are essentially asking the DB to log every mouse click and every data change.

You would also want a Table that could capture all those activities, so you could report it. There is no automatic activity log in MS Access.

[mose Mbugua]

twinnyfo
what if we reduce that so that the report can only capture only forms and reports which were opened by a user? Do i have to use code or is there another way?

[twinnyfo]

You must still use code. One way might be to use the OnOpen Event of each form, saving the Form Name, User Name and Time Accessed to your table.

The simplest way to do that is create a Public Function in a separate Module that saves that information to a Table--then you just call that Function any time the Form is opened.

[NeoPa]

Mose:
What if we reduce that so that the report can only capture only forms and reports which were opened by a user?

The original question was no different from this. Hence the answer is the same. Anything that is to be logged must be logged by your project by design. There is no automatic logging in Access.

[twinnyfo]

Mose,

Here is my five-minute solution--there may be other/better ways to do this, but this will work...

Create a Table named tblLogActivity with the following fields:

Field         Data Type
=======================
LogKey        Long, AutoNumber
FormName      Text
UserName      Text
TimeAccessed  Date/Time, Default Value = Now()

In the module behind each of your forms:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH
    LogActivity Me.Form.Name, Environ("USERNAME")
    Exit Sub
EH:
    MsgBox "There was an error opening the Form!  " & _
        "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
    Exit Sub
End Sub

And then, in a separate Module, include the following function:

Option Compare Database
Option Explicit

Public Function LogActivity(FormName As String, UserName As String)
On Error GoTo EH
    Dim db As Database
    Dim strSQL As String
    Dim rst As Recordset
    Set db = CurrentDb()
    strSQL = "SELECT * FROM tblLogActivity;"
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rst.RecordCount = 0 Then
        With rst
            .AddNew
            !FormName = FormName
            !UserName = UserName
            .Update
        End With
    End If
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Function
EH:
    MsgBox "There was an error logging the activity!  " & _
        "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
    Exit Function
End Function

Not the fanciest code every created, but should serve as an introduction to the process. Of course, the process could be expanded to include other values also.

[mose Mbugua]

Am new to code guys. Guide me to doing that.

[twinnyfo]

Do your forms only use macros? If so, I would encourage you to learn more about VBA and convert your macros to VBA. It is a much more powerful means of making your DB useful and flexible.

[jforbes]

This is some Code I recently started using for logging Errors and other Events of interest. Hopefully it's not to long to post or overkill for this Question.

Also, there are some Methods in there that are not included, like GetWindowUser() and dbLocal(), if anyone wants them, I can wrangle them together.

As for Logging, the logEvent() function creates a record in SQL Server. Like Twinnyfo's example, to log an event on Form Open (Load):

Private Sub Form_Load()
    Call logEvent("FormOpen",0, "", "FormName", 0, "")
End Sub

The customErrorHand ler() does pretty much the same, but also Logs it to a text file as well as displays the message to the user. So then, I put the following in error handlers throughout the Database:

Call customErrorHandler(mProcedureName, True, Err, Erl, "")

Here is the Module:

Private Const mProcedureName = "ErrorHandler"
Public gErrorMessage As String

Public Function customErrorHandler(ByRef sActiveObject As String, ByRef bShowError As Boolean, ByRef oErr As ErrObject, ByRef iErrorLine As Integer, ByRef sErrorMessage As String) As Boolean
    
    Dim sError As String
    Dim sSQL As String
    Dim iErrorFileHandle As Integer
    Dim sLogFile As String
    Dim iErrorNumber As Integer
    Dim sErrorDescription As String
    Dim sAppDir As String
    
    ' On Error GoTo Statments reset the error object
    iErrorNumber = oErr.Number
    sErrorDescription = oErr.Description
    
On Error GoTo GotoError

    DoCmd.Hourglass False
    DoCmd.SetWarnings True
        
    sAppDir = getAppDir()
    iErrorFileHandle = FreeFile
    sLogFile = sAppDir & gShortAppName & ".log"

    ' Build Error Message
    sError = sError & "" & Now() & vbCrLf
    sError = sError & " Code Library/Line: " & sActiveObject & "/" & iErrorLine & vbCrLf
    sError = sError & " User/Computer: " & GetWindowsUser() & "/" & GetWindowsComputerName() & vbCrLf
    sError = sError & "     Error Message: " & sErrorMessage & vbCrLf
    sError = sError & "     Error Number: " & iErrorNumber & vbCrLf
    sError = sError & "     Error Description: " & sErrorDescription & vbCrLf
    
    ' Write Error message to file
    If Len(gShortAppName) > 0 And Len(sAppDir) > 0 Then
        Open sLogFile For Append Access Write As #iErrorFileHandle
        Print #iErrorFileHandle, "~" & sError
        Close #iErrorFileHandle
    End If
    
    ' Display Error
    If bShowError Then msgBoxError ("An Error has occured in " & gShortAppName & ":" & vbCrLf & sError)
    
    ' Insert Error into SQL
    customErrorHandler = logEvent("RuntimeError", iErrorNumber, sErrorDescription, sActiveObject, iErrorLine, sErrorMessage)

GotoExit:
    Exit Function

GotoError:
    MsgBox "Error in ErrorHandler.customErrorHandler: " & Err.Description
    Resume GotoExit

End Function

Public Function logEvent(ByRef sEventType As String, ByRef iErrorNumber As Integer, ByRef sErrorDescription As String, ByRef sActiveObject As String, ByRef iErrorLine As Integer, ByRef sErrorMessage As String) As Boolean
    
    Dim sSQL As String
    
    logEvent = False
    
    ' Insert Error into SQL
    sSQL = ""
    sSQL = sSQL & " INSERT INTO EventLog ("
    sSQL = sSQL & "  EventType "
    sSQL = sSQL & ", ErrorNumber "
    sSQL = sSQL & ", Description "
    sSQL = sSQL & ", Object "
    sSQL = sSQL & ", Line "
    sSQL = sSQL & ", Message "
    sSQL = sSQL & ", UserID "
    sSQL = sSQL & ", Computer "
    sSQL = sSQL & ", Application "
    sSQL = sSQL & ", ApplicationVersion "
    sSQL = sSQL & ") VALUES ( "
    sSQL = sSQL & "  '" & sEventType & "'"
    sSQL = sSQL & ",  " & iErrorNumber & ""
    sSQL = sSQL & ", '" & trimForSQL(sErrorDescription) & "'"
    sSQL = sSQL & ", '" & sActiveObject & "'"
    sSQL = sSQL & ", '" & iErrorLine & "'"
    sSQL = sSQL & ", '" & trimForSQL(sErrorMessage) & "'"
    sSQL = sSQL & ", '" & GetWindowsUser() & "'"
    sSQL = sSQL & ", '" & GetWindowsComputerName() & "'"
    sSQL = sSQL & ", '" & gShortAppName & "'"
    sSQL = sSQL & ", '" & mVersion & "'"
    sSQL = sSQL & ")"
    
    dbLocal.Execute sSQL

    logEvent = True
GotoExit:
    Exit Function

GotoError:
    MsgBox "Error in ErrorHandler.logEvent: " & Err.Description
    Resume GotoExit

End Function