Solved Determine time of activity.

[The_Doc_Man]

When I had this same goal and this same kind of problem, I quickly realized that I needed a couple of facts. One of them was whether it was EVER reasonable to log in to the DB twice from two different terminals as the same user AND from a separate window to allow two simultaneous sessions from the same terminal. I got permission from the security guy to say "NO" to both questions. My login table included username and computername from the session login and also had session start date/time and end date/time - plus the "Abnormal" flag. (If that seems a bit strict, remember I was working for the U.S. Navy at the time. Their idea of security includes some things I wouldn't have guessed.)

That table enabled me to open a recordset to the session table to find the session record with the highest start date/time (before I wrote the current record) for the username logging in again. Relatively easy. If the record had an end date/time, I was done. If not, I updated that record to (a) put the current date/time as "END" for that session and (b) set a flag in the record that said "Abnormally terminated." Once that record was done, I could then enter a new session. I made it a point to, after the fact, capture the session number (which was an autonumber for that session table.) That way, if the user was exiting normally, in the dispatcher form's Close routine I could write a simple

"UPDATE SESSIONS SET ENDSESS= Now() WHERE SESSID = " & Me.SessionID & ";"

That way I could count sessions, abnormal sessions, and the duration of normal sessions easily. There was just about NEVER a chance to capture the correct session end for an aborted session, so the question was what to do about it. Therefore, by having a way to at least know the difference between orderly and disorderly session terminations, I had very few dangling sessions. I ALSO had a supervisor's maintenance tool that I ran during scheduled maintenance when the DB was still "officially down" and thus could not be logged in. It looked for sessions with no end date/time and immediately capped those off, too.

I don't know if that will help, but it was how I approached the problem.

 

[pbaldy]

My plan is to store the Logon and Logoff as TempVars and then write one "Insert Into" statement to the table. That is unless of course someone convinces me that the idea is flawed...

That still leaves you vulnerable to the issue Colin described, "where a user doesn't logout due to e.g. an illegal shut down or a power failure." In this case you wouldn't have an orphaned login, but no record at all of activity. I suppose that could be a preference in some situations, but I wouldn't go this way. I did what Pat described.

 

[isladogs]

My schools apps included code which allowed program developers and admins to check which items each person used during each login session together with the time that item/control was last touched. This was mainly used for several purposes
1. Prioritise future development priorities
2. Manage an audit of error logging...again related to development priorities
3. Manage forced logouts after a specified period of inactivity
However it had one further use - to determine an approximate logout time for sessions terminated abnormally. This was taken to be the latest activity time in that session. The missing logout times were plugged as one small part of the automated maintenance update that ran each night at around 03:00

 

[The_Doc_Man]

The discussion about normalization is at least partly obscured by the question of "What are you storing?"

If you are storing events which can be either login or logout or other kinds of events, that table structure in post #1 works.

If you segregate login and logout from other events and put them in a separate Session table, you are storing sessions, not events, so the session isn't finished until you toss in the end date/time. Normalization DOES depend on what you are normalizing. In this case, the attributes of the session would include end date/time because that IS an attribute of a session.

 

[NauticalGent]

I read this article years ago, found it too geeky for me to understand and moved on...

DBG's solution gave me real world application of it and the light bulb came on. Took awhile but what else have I got to do??

Great stuff, appreciate it!