Generating logged-in user list

[stevekos07]

Hi all. I have a table that records user activities based on a public function that is called when logging in. The table shows a list of users who have logged in and logged out, but in some cases the user may have logged in and out several times in a day.

I am looking to generate a list of currently logged-in users. One way I thought of doing this is through a query that uses the MOD function to produce users with an odd number of the sum of values "Logon" and "LogOut" in the Activities field.

The logic is that any user that has logged in but not logged out will have a total of values in the Activities field that is odd, which should mean that they are still logged in.

I have not used the MOD function but I have read about it, but I am still a bit confused about how to write the code.

Any help would be appreciated.

 

[CJ_London]

what happens if the computer loses power, or the network fails? - so the user has logged in, but not been able to log out - their next action will be to login again.

but to answer your question, MOD is not a function, it is an operator . to use it it is just

X MOD Y

to determine odd or even, Y will be 2

1 mod 2=1
2 mod 2=0
3 mod 2=1
4 mod 2=0
etc

 

[isladogs]

Using your logic, you would need something like this where SumLogs = total number of logins and logouts

MOD([SumLogs]) 2 = 1

to indicate who is still logged in

However I wouldn't do that.
I assume you have 2 separate fields for recording logins and logouts.
You only need to consider the latest login for the current day
Find this using a DLookup.
Then check whether the logout field is null
If so, the user is still logged in

 

[stevekos07]

Thanks CJ. You are always so ready to help. I really appreciate it!

So the syntax in SQL would be:

WHERE (COUNT([Activity] MOD 2) > 0) ??

I'm still a toddler when it comes to SQL syntax!

This is what I have so far:

SELECT tblUserActivityLog.ID, tblUserActivityLog.TimeStamp, tblUserActivityLog.UserName, tblUserActivityLog.Activity
FROM tblUserActivityLog
WHERE (((tblUserActivityLog.UserName) Is Not Null))
ORDER BY tblUserActivityLog.TimeStamp;

How do I show a list of IDs where the Sum of ID in the Activity field is Even? I have tried a few ideas but I have something fundamentally wrong going on here.

(Yes there will be instances of errors for all sorts of reasons, including termination of the program from within Windows itself etc., but this is not meant to be absolutely foolproof).

 

[isladogs]

As all fields are from the table tblUserActivity you can omit that & simplify your code

To list those still logged in, how about:

SELECT ID, TimeStamp, UserName, Activity
FROM tblUserActivityLog
WHERE (((UserName) Is Not Null) AND (COUNT([Activity] MOD 2) = 1))
ORDER BY TimeStamp;

Those not logged in:

SELECT ID, TimeStamp, UserName, Activity
FROM tblUserActivityLog
WHERE (((UserName) Is Not Null) AND (COUNT([Activity] MOD 2) = 0))
ORDER BY TimeStamp;

but you should also restrict to the current day only for that one by filtering the TimeStamp field.
Something like this:

SELECT ID, TimeStamp, UserName, Activity
FROM tblUserActivityLog
WHERE (((UserName) Is Not Null) AND (COUNT([Activity] MOD 2) = 0) AND Format(TimeStamp,"mm/dd/yyyy") = Date))
ORDER BY TimeStamp;

Did you consider my first response?

 

[CJ_London]

not tested but probably

WHERE (COUNT([Activity]) MOD 2 > 0

and to use the count, you will need to group your query just on those fields where the value will be the same - e.g. username - and exclude the others

but I see you have a timestamp - why not just get the latest timestamp - assuming activity means something like logged in or logged out

 

[stevekos07]

As all fields are from the table tblUserActivity you can omit that & simplify your code

To list those still logged in, how about:

SELECT ID, TimeStamp, UserName, Activity
FROM tblUserActivityLog
WHERE (((UserName) Is Not Null) AND (COUNT([Activity] MOD 2) = 1))
ORDER BY TimeStamp;

Those not logged in:

SELECT ID, TimeStamp, UserName, Activity
FROM tblUserActivityLog
WHERE (((UserName) Is Not Null) AND (COUNT([Activity] MOD 2) = 0))
ORDER BY TimeStamp;

but you should also restrict to the current day only for that one by filtering the TimeStamp field.
Something like this:

SELECT ID, TimeStamp, UserName, Activity
FROM tblUserActivityLog
WHERE (((UserName) Is Not Null) AND (COUNT([Activity] MOD 2) = 0) AND Format(TimeStamp,"mm/dd/yyyy") = Date))
ORDER BY TimeStamp;

Did you consider my first response?

I tried all of these and I keep getting an aggregate error. Apparently I can't aggregate both of these criteria in the WHERE clause. ??

 

[isladogs]

Suggest you upload a copy of your db with some sample data in the table tblUserActivityLog

 

[stevekos07]

Suggest you upload a copy of your db with some sample data in the table tblUserActivityLog

I'll do that if I don't work it out by the end of today.

 

[The_Doc_Man]

The best results I've ever gotten on this came when I had an explicit login event and an explicit logout event. Since I had a switchboard form that was ALWAYS open until the user clicked the Exit button, it was easy to make a Login Event (switchboard OnLoad event) and a Logout Event (Exit button click). So in my user record, I would have a "last action" slot and it would tell me "IN" or "OUT" plus a "last action time" slot (with obvious usage). Then I could search my home-grown event log to decide how long each user was in or out. AND if it happened that I got two "IN" events with no intervening "OUT" event then I knew the user had either done something wrong or had lost power to his/her workstation. The count of users in the database was then just a list of users whose "last action" flag was "IN" and their session time was just the time between the login time and Now().

Further, I could flag the suspicious cases where login time was longer than 24 hours. Since our side used a smart-card login and we were required by work rules to log out when we left but to leave the workstation powered up so security could jam-cram some patches on us, folks would have to log out explicitly because I would not allow them to close the database or exit the session without exiting the app first. (But even that didn't always work.)

So here's my advice. Do your best to figure In/Out status and DON'T SWEAT IT when things don't quite balance. 'cause they won't.

 

[arnelgp]

this will show how many is currently using your database (fe or be).


if standalone:
 
Dim numofOfUser As Integer
numberOfUsers = fnNumberOfUserInDatabase()


If you have BE, pass the complete path and name of the database, eg:

Dim numofOfUser As Integer
numberOfUsers = fnNumberOfUserInDatabase("\\SharedFolder\Production_BE.accdb")


 Remember to Add Reference (goto VBE Tools->Reference) to:
Microsoft ActiveX Data Objects 2.8 Library , or

 Microsoft ActiveX Data Objects 6.0 Library

[code]

Public Function fnNumberOfUsersInDatabase(Optional ByVal strPathDatabase As String = "") As Integer
    Dim cn As ADODB.Connection
    If strPathDatabase = "" Then strPathDatabase = CurrentDb.Name
    If strPathDatabase = CurrentDb.Name Then
        Set cn = CurrentProject.Connection
    Else
        Set cn = New ADODB.Connection
        ' Open the connection
        With cn
            .CursorLocation = adUseServer
            '.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Tmp.accdb"
            .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPathDatabase & ";Mode=Share Deny None;Extended Properties="""""
        End With
    End If
    fnNumberOfUsersInDatabase = ADOShowNumberOfUsers(cn)
    cn.Close
    Set cn = Nothing
End Function

Public Function ADOShowNumberOfUsers(cnnConnection As ADODB.Connection) As Integer
  ' Comments: Uses the new Jet 4 User Roster to list all users in the specified database
  ' Params  : cnnConnection     Open ADODB connection to the Jet Database
  ' Returns : String of all users seperated by a new line
  ' Source  : Total Visual SourceBook
 
  Dim rstTmp As New ADODB.recordSet
  Dim strTmp As String
  Dim intCount As Integer
 
  ' This is the value to pass to Jet to get the user roster back.
  Const cstrJetUserRosterGUID As String = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"

  On Error GoTo Proc_Err
 
  ' Jet exposes the user roster as a provider-specific schema rowset.
  ' To get Jet to return this, we open a recordset and pass the special GUID value.
  Set rstTmp = cnnConnection.OpenSchema(adSchemaProviderSpecific, , cstrJetUserRosterGUID)
  With rstTmp
      While Not (.EOF Or .BOF)
        .MoveNext
        intCount = intCount + 1
      Wend
    End With
  'ADOShowNumberOfUsers = rstTmp.AbsolutePosition ' intCount
  ADOShowNumberOfUsers = intCount
  rstTmp.Close


Proc_Exit:
  Exit Function

Proc_Err:
  MsgBox "Error: " & err.Number & ". " & err.Description, , "ADOShowNumberOfUsers"
  Resume Proc_Exit
End Function

 

[stevekos07]

Ok, now I have a choice. Arnelgp, your solution seems to be the best way to track TRUE userlogs but there are big slabs of code here that I don't fully understand. That always makes me nervous, in case something goes wrong.

On the other hand The Doc Man's solution is much simpler but less bulletproof. I think I'll try his first, and play around with yours before I attempt to implement it.

I'll see how I go playing with these over the weekend.

Thanks everyone for your contribution, I'll let you know what I end up doing. Cheers.

 

[arnelgp]

i understand what your concern.
create a test database
one stand alone the other split
copy the code to both (the other on fe).
test it.


there is nothing destructive this function is doing
only inquiring the number of users.



the code comes from FMS, Inc., reputable co selling Access Add-ons and Utilities.




in fact there is another way to show who these users are.

 

[The_Doc_Man]

Arnelgp, doesn't this part depend on the version of Access?

  Const cstrJetUserRosterGUID As String = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"

And if so, wouldn't it change after an Access update (patch) or upgrade (to a new version)?

And I also wonder if there would be a timeout event before a lost connection registers as such, which would mean an occasional answer that is higher than it should be.

Steve, you are correct - my code is NOT bullet-proof. But then again, neither is Windows connection management.

 

[arnelgp]

Doc:

i have used that in A2007 x86.
i have now A2016 x64, just tested it and its fine.

 

[Mark_]

Steve,

Just to stir the pot, only time I ever had to do this we had a timer even handle most of the work for us.

User starts program, program updates the USER record in the USER file to show "Logged in" and the current date/time.

In the timer, this file was updated every minute. Also stored some other bits, such as what procedure the user was in and what they were doing. We also tossed an event.timer every time the user opened a new process. We did this to note the following;

1) What processes were taking excessive amounts of time (separate application monitored the file looking for users who had not update in over a minute, thus showing a problem with code)
2) What processes would/could crash the program (had when the user last updated as well as what process they went into)
3) Users who were acting silly. Ever see a user decide that turning off the power to the computer end of day?
4) Users poking around where they are told not to go. Management had decided nothing should be out of sight from any user.

 

[stevekos07]

The best results I've ever gotten on this came when I had an explicit login event and an explicit logout event. Since I had a switchboard form that was ALWAYS open until the user clicked the Exit button, it was easy to make a Login Event (switchboard OnLoad event) and a Logout Event (Exit button click). So in my user record, I would have a "last action" slot and it would tell me "IN" or "OUT" plus a "last action time" slot (with obvious usage). Then I could search my home-grown event log to decide how long each user was in or out. AND if it happened that I got two "IN" events with no intervening "OUT" event then I knew the user had either done something wrong or had lost power to his/her workstation. The count of users in the database was then just a list of users whose "last action" flag was "IN" and their session time was just the time between the login time and Now().

Further, I could flag the suspicious cases where login time was longer than 24 hours. Since our side used a smart-card login and we were required by work rules to log out when we left but to leave the workstation powered up so security could jam-cram some patches on us, folks would have to log out explicitly because I would not allow them to close the database or exit the session without exiting the app first. (But even that didn't always work.)

So here's my advice. Do your best to figure In/Out status and DON'T SWEAT IT when things don't quite balance. 'cause they won't.

Thanks Doc for this solution. I played around with this kind of thing on the weekend and actually found a YouTube video taking very similar approach. I think this will suit my needs quite ok. The very rare situation where there may be a power outage of forced shut down of Access will not significantly affect the utility of this method. Cheers!