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

• Microsoft Access versions 7.0, 97

SUMMARY

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

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

MORE INFORMATION

The following example uses the sample database Northwind.mdb to show how to create a log table the first time that a user opens the database and how to record the date and 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 to the Database window, or if you close the database from the Database window, the data will not be written to the log. Visual Basic for Applications code to create and maintain the log table is run in the Open event of the startup form. Visual Basic for Applications code to maintain 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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

1. Open the sample database Northwind.mdb.



2. If the Northwind startup screen appears, click to select 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 tdf = 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 tdf = Nothing
    Set rs = Nothing
    Set db = Nothing

Exit_Form_Open:

    Exit Function

Err_Form_Open:
    If Err.Number = 3265 Then
        ' Table doesn't 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 was created when you opened the startup form the first time. The first record should show the times that you first opened and closed the database. The second record should show the time that you opened the database this time.
   
   Note: With Application.CloseCurrentDatabase in the Close event of the frmOpeningMenu form, in order to open the form in Design view, you must remove the frmOpeningMenu form from the Display Form box of the database Startup dialog box. (Step 4.)

REFERENCES

For more information about startup forms, search on the phrase "startup forms," and then "Display a startup form when a database or application opens," using the Microsoft Access Help menu.

Additional query words:

Keywords          : kbdta 
Version           : WINDOWS:7.0,97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 30, 1999