MS Access: Log Messages to a Table and Know What’s Happening When Your User is Stuck

Here’s some code to help you log messages to a table. First, make a table called tblLog, with at least these columns: Timestamp, User, Computer, Message. (You don’t need a primary key.)

Set the default value of Timestamp to NOW().

Copy the following code into a code module.

Also, add a reference to “Active DS Type something or other”. It has the active directory functions you need to discover the username.

Function StartUp()
    Dim dummy
    dummy = LogOpen()
    DoCmd.OpenForm "frmHidden", acNormal, , , , acHidden
    StartUp = Null
End Function

Function LogOpen()
    LogMessage ("User opened database.")
End Function

Function LogClose()
    LogMessage ("User closed database.")
End Function

Function LogMessage(Mess As String)
    Dim sysInfo As New ActiveDs.WinNTSystemInfo
    Dim UserName As String
    UserName = sysInfo.UserName
    If UserName <> "" Then
        Dim dbs As Database
        Dim rst As Recordset
        Set dbs = CurrentDb
        dbs.Execute ("INSERT INTO tblLog (User, Computer, Message) VALUES ('" & sysInfo.UserName & _
            "','" & sysInfo.ComputerName & _
            "','" & Mess & "')")
    End If
    LogMessage = True
End Function

(StartUp looks messed up. I don’t know what I’m doing. There’s also a pointless temporary variable in LogMessage.)

To enable startup and shutdown logging, create a macro called AutoExec, and in the macro, call the StartUp function.

Then create a new form called “frmHidden”, and add a hander for the Close event. In that event, call the LogClose function. Save all that.

What’s happening is that the frmHidden form is opened up during startup, but is hidden. Then, during shutdown, it’s Close event handler is called. This is a crappy hack. Improvements are appreciated.