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.