Database usage monitor

[lcannon]

I have several hundred access databases and need to see when the database was last used, the name of the database and identify who used it.

Is there some code or modifications to the code below that would do this??

I need help modify this code to capture the database (mdb) name, the time last used and the last user who used it. Is there a way to do this???



Private Sub Form_Open(Cancel As Integer)
Dim strPath As String, strDir As String, strFile As String, strSQL As String
Dim DirList As New Collection, FileList As New Collection

On Error GoTo ErrorHandler

CurrentDb.Execute "DELETE * FROM tblTables"
strPath = "x:\dbases\"

' add start dir to dir collection
DirList.Add strPath & strDir

strDir = Dir(strPath, vbDirectory)
While strDir <> ""

Select Case strDir
Case ".", ".."
Case Else
If (GetAttr(strPath & strDir) And vbDirectory) = vbDirectory Then
' add sub directories to dir collection
DirList.Add strPath & strDir & "\"
End If
End Select

strDir = Dir()
Wend

' loop through dir collection looking for *.mdb files
Dim x As Long
For x = 1 To DirList.Count
strFile = Dir(DirList(x) & "*.mdb")
While strFile <> ""
' add mdb file to file collection
FileList.Add DirList(x) & strFile
strFile = Dir()
Wend
Next x

' loop through file collection of mdb files
For x = 1 To FileList.Count
CurrentDb.Execute "INSERT INTO tblTables (DatabaseName) SELECT Name,'" & FileList(x) & "' FROM MSysObjects IN '" & FileList(x) & "')"
Next x

Me!Combo0.Requery
ErrorHandler:
If Err.Description = "Record(s) cannot be read; no read permission on 'MSysObjects'." Then
Resume Next
End If

If Err.Description = "Not a valid password." Then

strSQL = "insert into tblpassmdbs(Path) Values ('" & FileList(x) & "')"

CurrentProject.Connection.Execute strSQL
End If
'MsgBox ("Search Complete")
End Sub

 

[namliam]

Currentdb.name will return the full path + name of your current database

Environ("username") will return the current windows user....

Hope that helps

Season greetings from Amsterdam

The Mailman