Users are killing me..need to log user ID's

[Davers]

Hi everyone...This is the second time someone has put a password on our database. They've also deleted it once before. I have a database that has to be open to almost everyone. Daily call offs, etc are logged in here for 27 different departments. I can not set up users in this database because of problems in the past. Last night again, someone password protected the database so no one can get in....I have 5 days worth of backups...so it's really no big problem, however, we'd like to know who keeps doing this. I have created a form that looks at idletime and runs in the background. What it does is closes the database after 5 minutes of inactivity..(had to create this because of problems too) what I've done is created a network userID function and put 2 textboxes on this form as well. One text box brings up their ID, and the other is the current date and time. What I'd like is for this information to be exported to an excel spreadsheet everytime someone logs into the database. That way, we can see who the last one was to use it...I can do that, however, I'd like it to keep a running log of users...not keep replacing the spreadsheet. Does anyone have any idea how to do this, or suggestions as to a better way of keeping track of users?

Sorry this is so long winded...

Thanks for any help.

Dave

 

[mdmilner]

Just setup something to write the value somewhere to save. Probably could just add this to your hidden Timer Form to log while it watches for inactivity.

Think you need the Microsoft Scripting Runtime for this one in references
This just writes to a txt file - real quick and easy. Pick a path to open up.

Public Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If

End Function

    Open strPath For Append As #1
    strLog =  fOSUserName   ' I'd add other data here like timestamps
    Print #1, strLog
    Close #1

 

[Davers]

Sweet!! Thanks, I'll give it a shot to see how it goes!

Thanks again and have a good weekend!

Dave

 

[shortie]

Can you tell me about your form that looks at idletime and runs in the background.

How do you create it and make it close the database after a period of inactivity?

Regards,
Ben

 

[Davers]

Hi Ben, the form opens in the background whenever the database is opened. It first gets the user ID from another form, then it starts calculating how much time has elapsed without any activity. Since I am 1 of several people who may need to work on the database from time to time, I thought a way to bypass the close event would be helpful. All it took was the database closing on me once or twice before I came up with this. :wink: Once the time goes beyond a period of time I've set in the code, it saves and closes the database unless the USERID is one it is set up to ignore.... The code has changed quite a bit from what was originally posted here...I've added some comments to the code to try to explain my reasons for doing things. The below code would be pasted in the form which I've called "DetectIdleTime". I hope this helps...

This part gets the Log in ID

Option Compare Database

Private Sub Form_Load()
GetUserInfo "LOGIN"
End Sub

Here is the part that determines how much time has elapsed without activity or changes...

Sub Form_Timer()
         ' IDLEMINUTES determines how much idle time to wait for before
         ' running the IdleTimeDetected subroutine.
         Const IDLEMINUTES = 5

         Static PrevControlName As String
         Static PrevFormName As String
         Static ExpiredTime

         Dim ActiveFormName As String
         Dim ActiveControlName As String
         Dim ExpiredMinutes

         On Error Resume Next

         ' Get the active form and control name.

         ActiveFormName = Screen.ActiveForm.Name
         If Err Then
            ActiveFormName = "No Active Form"
            Err = 0
         End If

         ActiveControlName = Screen.ActiveControl.Name
            If Err Then
            ActiveControlName = "No Active Control"
            Err = 0
         End If

         ' Record the current active names and reset ExpiredTime if:
         '    1. They have not been recorded yet (code is running
         '       for the first time).
         '    2. The previous names are different than the current ones
         '       (the user has done something different during the timer
         '        interval).
         If (PrevControlName = "") Or (PrevFormName = "") _
           Or (ActiveFormName <> PrevFormName) _
           Or (ActiveControlName <> PrevControlName) Then
            PrevControlName = ActiveControlName
            PrevFormName = ActiveFormName
            ExpiredTime = 0
         Else
            ' ...otherwise the user was idle during the time interval, so
            ' increment the total expired time.
            ExpiredTime = ExpiredTime + Me.TimerInterval
         End If

         ' Does the total expired time exceed the IDLEMINUTES?
         ExpiredMinutes = (ExpiredTime / 1000) / 60
         If ExpiredMinutes >= IDLEMINUTES Then
            ' ...if so, then reset the expired time to zero...
            ExpiredTime = 0
            ' ...and call the IdleTimeDetected subroutine.
            IdleTimeDetected ExpiredMinutes
         End If
         
      End Sub

This part is what checks to see if one of these people are logged in..if so, don't close the database...

Sub IdleTimeDetected(ExpiredMinutes)
    Dim strUserName As String
    strUserName = NetworkUserId()
    
    If strUserName = "WPAREQ" Or strUserName = "MJELEN" Or strUserName = "DMORRI18" Then
        GetUserInfo "SUPERUSER - TIMEOUT DISABLED"
        Application.DoCmd.Close acForm, "DetectIdleTime", acSaveNo
    Else
        GetUserInfo "TIMEOUT"
        Application.Quit acSaveYes
    End If
End Sub

This last part writes who was in the database and at what time. The reason for this was because believe it or not...someone deleted the database, not once...but 2 different times. No one admitted to it, so I put this code in place. I figured whoever was the last one in the database would be the one who deleted it....

Private Sub GetUserInfo(strReason As String)

    Dim con As Object
    Dim stSql As String
    
    Application.DoCmd.SetWarnings (False)
    
    Set con = Application.CurrentProject.Connection
    stSql = "INSERT INTO UserInfo (CDSID, [Date], Reason) SELECT '" & Me.Text0 & "' AS cds, Now() AS dte, '" & strReason & "';"
    
    con.Execute stSql, , adExecuteNoRecords

    Set con = Nothing

DoCmd.TransferText acExportDelim, , "UserInfo", "W:\rcbwpa\ArchivedData\UserInfo.txt"

    Application.DoCmd.SetWarnings (True)
    
End Sub

Hope this helps...

Dave

 

[shortie]

Thanks heaps dave.

It is amazing what people will do to your data base - deleation and password changes - trust no one with your precious data - limit their access

You might want to try the replica function - you can set the file to prevent deleation.

Cheers
Ben