|
Log Each User's Session
By The Gunny
Create a session log to record each user's opening and closing of the Access database application.
(Thousand Oaks, CA -- Nov. 12, 2005) -- Managers often need to know who is using a database application, but Microsoft Access has no built-in capability to record each user's session. With a little bit of VBA programming, a table, a query and a hidden form, each user's session can easily be recorded. Another query and report can calculate how much time each user spent in the database application. After just a few minutes of work, you can have your own session log by following these instructions:
Create a new table with the following structure:
ID, AutoNumber, primary key
UserName, Text
ComputerName, Text
BeginTime, Date/Time
EndTime, Date/Time
Name this table tblUserLog. Next, create a new query and name it qryUserLog. Paste the following SQL statement into the SQL View pane:
SELECT *
FROM tblUserLog
ORDER BY BeginTime, EndTime, UserName, ComputerName;
Save the query and close it.
To use the tblUserLog table as a log, use the Form Wizard to create a new form and name it frmHidden. Use the qryUserLog query as its Record Source Property, and add all of the fields in the query as text box controls on the form.
When the Form Wizard is finished, open the form in Design View and open the Properties dialog window. Rename each text box control to reflect the type of control and the control's Control Source. For example, change UserName to txtUserName. The Form Wizard will automatically use the Caption Property of a bound field, and if one doesn't exist, it will use the name of the bound field for the control's name. To avoid bugs, never accept the default names that Access gives a control if it will be referenced in VBA code or in the Property dialog window.
Select the Event tab and scroll down until you see the Timer Interval Property. Set this property to a low number such as 10.
Copy the code on the following two Web pages into one new standard module:
http://www.mvps.org/access/api/api0008.htm
http://www.mvps.org/access/api/api0009.htm
Make sure that the API function declarations are moved to the Declarations section of the module, not placed after executable lines of code. For example:
Option Compare Database
Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Make sure that the name of this new module doesn't use the same name as any procedure. Next, copy the following code into your frmHidden form's code module:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me!txtUserName.Value = fOSUserName()
Me!txtComputerName.Value = fOSMachineName()
Me!txtBeginTime.Value = Now()
RunCommand acCmdSaveRecord
Exit Sub
ErrHandler:
MsgBox "Error in Form_Open( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
Private Sub Form_Timer()
ErrHandler:
MsgBox "Error in Form_Timer( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
Private Sub Form_Unload(Cancel As Integer)
ErrHandler:
If (Err.Number = 2448) Then
' Ignore, since the form is going into Design View.
Else
MsgBox "Error in Form_Unload( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If
Err.Clear
End Sub
Select the Tools -> Startup... menu to open the Startup dialog window. Select the frmHidden form name in the Display Form/Page combo box. Select the "OK" button to save the change. Save all of your work and close the database application. Open the database application again and the frmHidden form will open and record the log information, but after a very brief "blink," it won't be visible to the user. When the database application closes, this form will close, thereby saving the EndTime for this user's session in the log table.
To calculate the time elapsed for each user session, use a query such as the following:
SELECT UserName, ComputerName, BeginTime, EndTime,
Int(DateDiff("h", BeginTime, EndTime) / 24) AS Days,
DateDiff("h", BeginTime, EndTime) - (Days * 24) AS Hrs,
(DateDiff("n", BeginTime, EndTime) MOD 60) AS Mins
FROM tblUserLog
ORDER BY BeginTime, EndTime, UserName, ComputerName;
Remember that pressing the <SHIFT> key while opening the Access database application will prevent the startup settings from being used, so the frmHidden form won't open and record the user's information. If this presents a problem, then implement User-level Security and disable the shift key bypass capability of the database application.
You can read about your favorite expert contributors.
Copyright © 2005 - 2006 Q-Built Solutions. All rights reserved.
 Top
Visitors since 23 Nov. '05:
|
