Microsoft KB Archive/198833

= ACC: How to Create a History Log of Users and Dates =

Article ID: 198833

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q198833



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
This article shows you how to create and how to maintain a log of users who open a Microsoft Access database. This includes the dates and the times that the users open the database.



MORE INFORMATION
The following example uses the sample database Northwind.mdb to show you how to create a log table the first time that a user opens the database. The example also shows you how to record the date and the time that each user opens and closes the database.

To maintain a log, you must have a Startup form through which users enter and exit the rest of the database objects. If you open the database in the Database window, or if you close the database from the Database window, the data is not written to the log. Visual Basic for Applications code that creates and that maintains the log table is run in the Open event of the Startup form. Visual Basic for Applications code that maintains the close data is run in the Close event of the Startup form.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To create a history log of the users and of the dates that they open and that they close a database, follow these steps:  Open the sample database Northwind.mdb. If the Northwind startup screen appears, click Don't show this screen again, and then click OK.  Create the following new Switchboard form:   Form: frmOpeningMenu Caption: Open and Close Database  On the Tools menu, click Startup. Type frmOpeningMenu in the Display Form box.  In the "Declarations" section of the frmOpeningMenu form module, type the following: Public SessionID As Long   Set the OnOpen property of the form to the following event procedure: Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_Form_Open

Dim db As Database Dim rs As Recordset Dim strLogName As String Dim tdfLogTable As TableDef

Set db = CurrentDb

strLogName = "tblUsageLog" Set tdfLogTable = db.TableDefs(strLogName) Set rs = db.OpenRecordset(strLogName, dbOpenDynaset) rs.AddNew rs!User = CurrentUser rs!Opened = Now rs.Update rs.MoveLast SessionID = rs!LogID Set tdfLogTable = Nothing Set rs = Nothing Set db = Nothing

Exit_Form_Open:

Exit Function

Err_Form_Open: If Err.Number = 3265 Then ' Table does not exist CreateLogTable (strLogName) Resume Next Else MsgBox Err.Description Resume Exit_Form_Open End If

End Sub </li>  Set the OnClose property of the form to the following event procedure: Private Sub Form_Close On Error GoTo Err_Form_Close Dim db As Database Dim rs As Recordset Dim strLogName As String

strLogName = "tblUsageLog"

Set db = CurrentDb Set rs = db.OpenRecordset(strLogName, dbOpenDynaset)

rs.MoveLast Do Until rs.BOF If rs!LogID = SessionID Then rs.Edit rs!closed = Now rs.Update GoTo Exit_Form_Close End If     rs.MovePrevious Loop

Exit_Form_Close: Application.CloseCurrentDatabase Exit Sub

Err_Form_Close: MsgBox Err.Description Resume Exit_Form_Close

End Sub </li>  Type the following procedure: Function CreateLogTable(strLogName As String)

Dim db As Database, td As TableDef, fld As Field Set db = CurrentDb Set td = db.CreateTableDef

td.Name = strLogName

Set fld = td.CreateField fld.Name = "LogID" fld.Type = dbLong fld.Attributes = dbAutoIncrField td.Fields.Append fld Set fld = td.CreateField fld.Name = "User" fld.Type = dbText td.Fields.Append fld

Set fld = td.CreateField fld.Name = "Opened" fld.Type = dbDate td.Fields.Append fld

Set fld = td.CreateField fld.Name = "Closed" fld.Type = dbDate td.Fields.Append fld

db.TableDefs.Append td

Set fld = Nothing Set td = Nothing

End Function </li> To test this procedure, open the sample database Northwind.mdb and then close it. Open the database again, and then open the tblUsageLog table that is created when you open the Startup form the first time. The first record shows the times that you first opened and first closed the database. The second record shows the time that you open the database this time.

NOTE: To open the form in Design view with Application.CloseCurrentDatabase in the Close event of the frmOpeningMenu form, you must remove the frmOpeningMenu form from the Display Form box of the database Startup dialog box (step 4).</li></ol>

<div class="references_section">