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

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:

1. Open the sample database Northwind.mdb.
2. If the Northwind startup screen appears, click Don't show this screen again, and then click OK.
3. Create the following new Switchboard form:

      Form: frmOpeningMenu
      --------------------------------
      Caption: Open and Close Database
   					

4. On the Tools menu, click Startup. Type frmOpeningMenu in the Display Form box.
5. In the "Declarations" section of the frmOpeningMenu form module, type the following:

   Public SessionID As Long

6. 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
   					

7. 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
   					

8. 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
   					

9. 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).