Results 1 to 3 of 3

Thread: who's logged in

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: who's logged in

    All; I am using access 2010 but working with an access 2003 database. This is split with frontend mdes on users desktop and be on the server. I've been looking for a way to find out who's logged in. All the ones I found gives me only the computer name and time users log in and off.
    I did find this code courtesy of datawright that seems to give me what I need but the concept is that a hidden form is suppose to launches when the users open the database. I do have a login form user name and password. When I test it; nothing happened. But then; it was not until I opened the monitoring form that it gave me the results I needed. The problem with this is; it doesn't give me the other users in the database. I am afraid that is because It's not launching when they open the database. The following is the code in a module:

    Code:
    Function LogOn()
        Dim sUser As String
        Dim sSQL As String
        
        DoCmd.SetWarnings False
        sUser = Environ("username")
        sSQL = "INSERT INTO tblUserLog ( UserID )" _
            & "SELECT '" & sUser & "' AS [User];"
        DoCmd****nSQL sSQL
        DoCmd.SetWarnings True
    End Function
    
    Function LogOff()
        Dim sUser As String
        Dim sSQL As String
        
        DoCmd.SetWarnings False
        sUser = Environ("username")
        sSQL = "UPDATE tblUserLog SET tblUserLog.LogOff = Now() " _
            & "WHERE tblUserLog.UserID='" & sUser & "' AND tblUserLog.LogOff Is Null;"
        DoCmd****nSQL sSQL
        DoCmd.SetWarnings True
    End Function
    This is the code that is suppose to launch the monitor form:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Current()
        Me.Visible = False
    End Sub
    
    Private Sub Form_Load()
        modUserLog.LogOn
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
        modUserLog.LogOff
    End Sub
    I also have a form that only I can get to in a frontend mdb. All the forms and modules were in the mdb frontend before creating the mde. So all the users have the monitor form that is suppose to launch. The table is in the be that everyone points to. Here is the code for the form:

    Code:
    Option Compare Database
    Option Explicit
    Const sSELECT = "SELECT tblUserLog.UserID, tblUserLog.LogOn, tblUserLog.LogOff " _
                    & "FROM tblUserLog "
    Const sWHERE = "WHERE (((tblUserLog.LogOff) Is Null)) "
    Const sORDER = "ORDER BY tblUserLog.LogOn DESC;"
    Dim sSQL As String
    
    Private Sub cmdAll_Click()
        sSQL = sSELECT & sORDER
        With Me.lstUsers
            .RowSource = sSQL
            .Requery
        End With
        Me.lblUsers.Caption = "Full Log"
    End Sub
    
    Private Sub cmdClose_Click()
        DoCmd.Close acForm, Me.Name
    End Sub
    
    Private Sub cmdCurrent_Click()
        sSQL = sSELECT & sWHERE & sORDER
        With Me.lstUsers
            .RowSource = sSQL
            .Requery
        End With
        Me.lblUsers.Caption = "Currently Logged On"
    End Sub
    Can anyone see why the monitor form does not launch please!
    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I can understand the logic behind the code you posted, it goes as follows:

    1. In the BE database, there must be a table named tblUserLog with three columns:
    - UserID Text(128)
    - LogOn DateTime, Default Value: Now()
    - LogOff DateTime, No Default Value, Null Allowed (i.e. Required = No)

    2. The following functions are located in a Standard Module:
    Code:
    Public Function LogOn()
    
        Dim sUser As String
        Dim sSQL As String
        
        sUser = Environ("username")
        sSQL = "INSERT INTO tblUserLog ( UserID ) VALUES ( '" & sUser & "');"
        CurrentDb.Execute sSQL, dbFailonError
    
    End Function
    
    Public Function LogOff()
    
        Dim sUser As String
        Dim sSQL As String
        
        sUser = Environ("username")
        sSQL = "UPDATE tblUserLog SET tblUserLog.LogOff = Now() " _
            & "WHERE tblUserLog.UserID='" & sUser & "' AND tblUserLog.LogOff Is Null;"
        CurrentDb.Execute sSQL, dbFailonError
    
    End Function
    3. There must be a Form in the FE database with the following code in its Module:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Current()
        Me.Visible = False
    End Sub
    
    Private Sub Form_Load()
        LogOn
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
        LogOff
    End Sub
    This form must be automatically opened when the FE datatabase starts (either in the AutoExec macro, using the StartUpForm property of the Database object, or by any other mean).

    4. Either in the FE database, in an independant database or in the BE (the later would be a bad design), there must be a Form with several controls in it:
    - A listbox: lstUsers, 3 columns
    - A Label: lblUsers
    - A Command button: cmdAll
    - A Command button: cmdClose
    - A Command button: cmdCurrent
    The OnClick events of the three Command buttons are associated with their respective event handlers (see hereafter).

    The following code goes in the Module of this Form:
    Code:
    Option Compare Database
    Option Explicit
    Const sSELECT = "SELECT tblUserLog.UserID, tblUserLog.LogOn, tblUserLog.LogOff " _
                    & "FROM tblUserLog "
    Const sWHERE = "WHERE (((tblUserLog.LogOff) Is Null)) "
    Const sORDER = "ORDER BY tblUserLog.LogOn DESC;"
    Dim sSQL As String
    
    Private Sub cmdAll_Click()
        sSQL = sSELECT & sORDER
        With Me.lstUsers
            .RowSource = sSQL
            .Requery
        End With
        Me.lblUsers.Caption = "Full Log"
    End Sub
    
    Private Sub cmdClose_Click()
        DoCmd.Close acForm, Me.Name
    End Sub
    
    Private Sub cmdCurrent_Click()
        sSQL = sSELECT & sWHERE & sORDER
        With Me.lstUsers
            .RowSource = sSQL
            .Requery
        End With
        Me.lblUsers.Caption = "Currently Logged On"
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by amr gharieb
    i talk to you for asking about the tracking the logging users in the preceeding link

    i wonder if i can add the computer name to the code how i can add

    thanks
    There 2 ways to retrieve the computer name:
    1.
    Code:
    Public Function GetComputerName() As String
    
        GetComputerName = Environ("COMPUTERNAME")
        
    End Function
    This method which uses the environment variable COMPUTERNAME is easy to use but is not considered as safe. This is because a user with admin privileges can alter the contents of the environment variables if the computer is not connected to a domain with a strong security policy applied.

    2.
    Code:
    '
    ' Windows API declarations (in the Declaration section of a module).
    '
    Public Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    '
    Public Function GetNTComputer() As String
    '
    ' Retrieve the name of the computer
    '
        Dim strComputerName As String
        Dim lngComputerNameSize As Long
    
        strComputerName = String$(255, 0)
        lngComputerNameSize = Len(strComputerName)
        GetComputerName strComputerName, lngComputerNameSize
        strComputerName = Left$(strComputerName, lngComputerNameSize)
        GetNTComputer = strComputerName
    
    End Function
    Though more complex, this method is safer than the first one.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •