User system tracking -- add date time stamp to table (if DB was shut down "errorneously")

[skydivetom]

Experts:



I need some assistance with **populating** a field/record where a value **should have been populated** but for some reason has a NULL value.
First, please find attached sample database (frontend and backend). Once copied to your computer, please ensure to **re-link** the two (2) tables in the backend to the front.

Existing Process:
1. A user opens the frontend.
2. Table "T902_Admin_SystemUserTracking" should now show your name/computer in the latest record. Also, the latest record should show the date time stamp (DTS) of your most recent login in field "DbEntry".
3. Now, while you have this DB session open, the field "DbExit" should be blank. This field/record will be auto-populated once you close the current DB.
4. At this time, close the DB and re-log into it a 2nd time. Check out the table again and your previous session should now show a DTS for both "DbEntry" and "DbExit".

Assuming that's the case, so far so good...

Now, please allow me to describe a scenario where the above does not always work as intended.

Issue:
- Most of the time, I will see a DTS in both fields for any user who logged in/logged out of the database.
- However, once in a while (approximately 10% of the time), the "DbExit" is NOT populated with a DTS for a terminated session (see yellow highlighted cell in the attached JPG).
- For example, per the attached JPG, DbEntryID #3 (Fonda) is an example of such case. That is, upon exit, his DTS was NOT automatically added to the field "DbExit".

Note: In my actual database, I do NOT know how some users close the database. That is, did they actually close it or did they lose their network connection between frontend and backend?

Desired Solution:
- In order to have a complete record, I'd like to automatically identify and update any **past sessions** where there's a NULL value in field "DbExit".

Scenario #1:
- If any ID session has a NULL value where DBEntry Date <> Today's Date(), then take DBEntry DTS and add 0.5 hours and update record.
- Example: Record ID #3 has DBEntry = 03/10/2020. And, obviously 03/10/2020 <> Today's Date() = 03/12/2020.
- Thus, add 30 minutes to DTS 09:05 and replace NULL value with "03/10/2020 09:35".
- While this could be construed as "fudging", I am ok with that user's session being counted as 30 minutes.
- Note: Adding the 30 min only applies if there's a **single** instance of that user for that particular day. If there are multipe sessions (same user; same day), then see scenario #2.

Scenario #2:
- If any ID session has a NULL value where DBEntry Date = Today's Date(), then identify whether or not there are MULTIPLE sessions from **same user**
- In this example (see JPG), two users (Washington and Fonda) have accessed the system today. However, user = Washington entered system today only once. Hence, we must assume it is an active/current session (i.e., he hasn't logged out of the database yet).
- Alternatively, user = Fonda has 2 records where DBEntry Date = Today's Date(). Logically, the first session (Rec ID 5) must have terminated without the DTS being added to "DbExit".
- And RecID #7 must (could) be an active session. For purpose of this test, we must assume it is an active session.
- Thus, for Fonda Rec #7, I want to ID the active session's DBEntry (8:45 AM) and subtract 2 minutes. Then, I want to update Rec #5 and add "8:43" into field "DbExit".
- Ultimately, based on scenario #2, if this process were to work, it then would reflect that a user logged out and within 2 minutes logged back into the system.

In summary, while the existing solution works approximately 90% of the time as is, there are a few cases where DTS in field "DbExit" stays empty. I'd like to fix that through the procedures/scenarios described above.

Alternatively, if the existing VBA code has issues and it doesn't add the "DbExit" DTS because it only works when a DB is closed (but not shut down by the network), I'd welcome information/recommendaitons that would fix the DTS update regardless of how Access was shut down.

Thank you for your recommendations/suggestions in advance.

Cheers,
Tom

[Gicu]

Here you are Tom.
EDIT: You might need to adjust it for multiple sessions (scenario 1) as right now it adds 30 min to all records prior to today. How would you treat older records that don't have a today's date entry? Similar to scenario 2 leaving the latest entry empty even if it is from a week ago? Or add 30 minutes and close it? I suggest you add a Comments field to your T902...table and populate it with "AutoEntry" or "Exception" or something similar for these db populated dates so you could easily identify them.
Cheers,

[skydivetom]

Vlad -- that's genius... I've only had a quick look at it but it appears to be working perfectly. I'll do some more testing.

WRT to your questions. I can manually update older records. I'll check out the "AutoEntry" or "Exception" indicator.

Follow-up question:
- So, you added 1 table and 4 queries. Does the tmpCurrentSessions have to reside in the frontend (which is distributed to each individual client machine)? Or it go into the backend? If it resides in the individual client FE, will there be any interference given that this temp table exist on the clients' FEs?

Thank you,
Tom

[Gicu]

Hi Tom,

The new temp table can(should) stay in the front-end as it is emptied and populated every time the fe opens (to make the update query "updateable"). You could even modify the update queries to limit them to the current user (meaning each user/fe would only update their own records).

Cheers,
Vlad

[skydivetom]

Vlad -- excellent... that'll work. Once I integrated the solution and upgrade the FE version #, all users will automatically receive the latest FE copied onto their local machine. Thus, if I understood you correctly, the temp table will only store their user ID for any particular day. Then, if for some reason the session is not automatically receiving the DbExit from the original VBA, your update queries kick in and the update any sessions (for same date) where their user ID (on client FE) matches the user ID in backend.

Did I copy this correctly?

Thank you again!
Tom

[Gicu]

Hi Tom,

I just realized that what I gave you only works if a user has only two empty sessions in a day. I need to change it to allow for more than two, might need a ranking query or to use recursion in VBA.

I need to leave for few hours, I'll watch this thread and if nobody jumps in I will do it later today.

Sorry for the confusion!

Cheers,
Vlad

[skydivetom]

Vlad - you are awesome. I appreciate the follow-up and look fwd to additional changes.

And, yes, conceptually a user may log into DB infinite # of times any given day.

Thank you in advance!

[isladogs]

Tom/Vlad
I haven't looked at any of the attached files in this thread but, from my own experience with logging users in and out, the out time will not be recorded if the users bypasses the logout code.
You will never to able to prevent such scenarios completely
That will happen if the user shuts down the app 'illegally' e.g. using task manager, switching off the power button, losing the network connection etc.
What I'm not sure about is the purpose of adding a 'dummy' logout time in such cases. How does that actually help?

Perhaps more useful would be to add code to detect user inactivity and automatically close down the database after a specified inactivity time.
Typically I set this as 20 minutes with no user activity at which point a message appears giving the user 30s warning before closedown.
If the user does anything during that 30s the clock is restarted.

[skydivetom]

Colin:

Thank you for chiming in... 'much appreciated. I will check how users shut down the the system. For now, I very much like Vlad's approach. I look forward to Vlad's modified version w/o limitation of 2 max login sessions.

Break

Above and beyond this thread, I'd love to check out an DB example of the "30 min inactivity" scenario. Is it customizable where I could change it from 30 min to some other preferred time span?

Thank you,
Tom

[Gicu]

Hi Tom,
I am still out, but how exactly do you want to deal with older multiple records (prior to today's date) say someone has 3 null entries on March10?
Cheers,

[skydivetom]

Vlad - thank you for the follow-up. I only need to deal w/ NULL records going forward. That is, I recently started this process and the table only contains ~150 entries. Out of those, I have manually added the DTS where applicable. So, as long as I have a solution that will ensure future NULL values will be updated, that'll be ok.

Here's one caveat though: Per my original posting, I mentioned the following: "Note: In my actual database, I do NOT know how some users close the database. That is, did they actually close it or did they lose their network connection between frontend and backend?" Based on Colin's comment, it appears that some of these scenarios may have been the cause for the previously existing NULL values. So, going forward, there could be a situation where someone's record will have potentially multiple NULL values in the DbExit due to loss of network connectivity.

All that said, if the procedure allows to "clean up" those records within up to 2 weeks, I think I should be fine. Does that throw you a curve ball?

[Gicu]

Hi Tom,
I'm sure the update process will be able to do it all but I still don't know how you want the records to be updated, specifically how to deal with the last record(s) of previous days.

Cheers,
Vlad

[skydivetom]

Vlad -- again, thank you so much for the continued help. Please see attached JPG for additional info on the "2 min" and "30 min" rule(s).

Cheers,
Tom

[Gicu]

Hi Tom,

My pleasure, can you please check this one out and see if it does what you need. I also added a sample of some idle time code I use in some of my dbs, I don't really prompt the user but quit the db using a SaveAll option.

Please use the attached back-end as I did add a Comment field to the usage table.

Cheers,

[skydivetom]

Vlad -- unfortunately, I couldn't extract the FE from the zipped file. I tried extracting "all files", "copying files", etc. Each time, however, I get the error illustrated below. Not sure what's causing it.

Could you please re-post the BE and FE again?