Set user's status to "Active" when he logged in

[domingsun]

hi all ,

im looking a solution that can track who logged into my access database ...
i have an ideal about this but don't know which way to do it ....
how to set user's status to "Active" when he logged in through Login page ?
when he close the access database ... i want the user's status set to "Offline" , how to do this ?

 

[AOB]

Hi domingsun,

I do something similar but rather than having a login page, I use the GetUserName API to retrieve the user's identity based on how they logged into their PC.

My method uses its own table which I call tblConnections - I have a separate user table which I link into this (if you were driving yours off a Login form, you could put everything into the one table - the only reason I separated mine was to account for unregistered users, which I still want to be able to detect)

I have a hidden form which opens as soon as the DB is opened. I use an AutoExec macro to automatically open this hidden form. The form itself is just blank, I don't have any controls on it, it is just something I use to allow me to trigger an event

In the Form_Load event, I write to the connections table and mark that they are 'active'. (I actually do slightly more than that, I note the name of the PC they're using, the network domain and timestamp of the point of entry)

And in the Form_Close event, I repeat the exercise but mark them as 'inactive'.

Here's the code in that hidden form :

Option Compare Database
 
Private Sub Form_Load()
 
    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL As String
 
    Call InterfaceInitialise
 
    ' Check if this is the first time this user / hostname combination has connected
 
    Set dbs = CurrentDb
 
    With dbs
 
        strSQL = "SELECT [tblConnections].* " & _
                "FROM [tblConnections] " & _
                "WHERE [tblConnections].[UserID] = '" & UCase(cSysInfo.UserName) & "' AND " & _
                "[tblConnections].[Hostname] = '" & UCase(cSysInfo.ComputerName) & "'"
 
        Set rst = .OpenRecordset(strSQL)
 
        With rst
 
            Select Case .RecordCount
 
                Case 0        ' First use - add to list and mark as logged on
 
                    strSQL = "INSERT INTO [tblConnections] " & _
                                "SELECT '" & UCase(cSysInfo.UserName) & "' AS UserID, " & _
                                "'" & UCase(cSysInfo.ComputerName) & "' AS Hostname, " & _
                                "'" & UCase(cSysInfo.ComputerDomain) & "' AS Domain, " & _
                                "True AS Connected, " & _
                                "Now() AS LastLogon"
 
                    dbs.Execute strSQL
 
                Case 1          ' Returning user - update list
 
                    .Edit
                    .Fields("Connected") = True
                    .Fields("LastLogon") = Now
                    .Update
 
            End Select
 
        End With
 
    End With
 
    Set rst = Nothing
    Set dbs = Nothing
 
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
 
    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL As String
 
    Set dbs = CurrentDb
 
    With dbs
 
        strSQL = "SELECT [tblConnections].* " & _
                "FROM [tblConnections] " & _
                "WHERE [tblConnections].[UserID] = '" & UCase(cSysInfo.UserName) & "' AND " & _
                "[tblConnections].[Hostname] = '" & UCase(cSysInfo.ComputerName) & "'"
 
        Set rst = .OpenRecordset(strSQL)
 
        With rst
 
            .Edit
            .Fields("Connected") = False
            .Fields("LastLogon") = Now
            .Update
 
        End With
 
    End With
 
    Set rst = Nothing
    Set dbs = Nothing
 
End Sub

Might get you started anyway?

 

[domingsun]

I will try with this solution first