Question How to determine who is logged on to a database
- Thread starter beyer
- Start date
- Local time
- Yesterday, 18:48
- Joined
- Feb 28, 2001
- Messages
- 30,503
This can be a real problem. You can do this a lot of different ways but the problem will always be how you remove a person from your list when they close the DB - if they actually do so at all.
First, there is the matter of who logged in. You either (1) have a list of known users and they must identify themselves from the list. Whether there is a password challenge or not is up to you... or (2) you have a system-based or domain-based login and you can use the Environ("Username") function to identify your user. Whichever way you choose to identify users, the name they choose or use must be placed in a table that is visible to your user display function.
Second, when they log out of the database, you have to scrupulously control how that is done, because otherwise you run into the problem that if they left while "ugly" then you don't know that they have left.
By leaving "ugly" I mean things like... If the network connectivity drops, you lose track of who is logged in. If they just shut down the machine or do an "End Task" on Access, you lose track of who is online. If their system crashes, you lose track.
Sadly, Windows will not help you very much on this. Actually, in a front-end, back-end case, Windows CAN'T help you, because your copy of Windows has no way to see the network tables of the machine on which your shared database resides. Unless, of course, you are also running Access on that machine.
Even reading the LDB file doesn't help because the problems associated with a user leaving "ugly" apply to it, too. I don't know that there is any really good solution to this problem if you are using a Windows back-end for Access.
Now, if you are using SQL Server or MySQL or ORACLE on a back-end, there is a chance (not a certainty) that you could run code on the back end server that updates a table based on network connections. One of the ARP commands in a command window could build a file of connected network slots that have not timed out yet. You could build something that runs on a timer to poll the ARP buffers for active connects, update the database on the server, and then read the table. This would be code-intensive and in the non-Access back-end case, also probably would not be VBA-based.
I have no idea how far you are willing to go to get this information, but if you want it reliably, it is going to be a very expensive solution.
First, there is the matter of who logged in. You either (1) have a list of known users and they must identify themselves from the list. Whether there is a password challenge or not is up to you... or (2) you have a system-based or domain-based login and you can use the Environ("Username") function to identify your user. Whichever way you choose to identify users, the name they choose or use must be placed in a table that is visible to your user display function.
Second, when they log out of the database, you have to scrupulously control how that is done, because otherwise you run into the problem that if they left while "ugly" then you don't know that they have left.
By leaving "ugly" I mean things like... If the network connectivity drops, you lose track of who is logged in. If they just shut down the machine or do an "End Task" on Access, you lose track of who is online. If their system crashes, you lose track.
Sadly, Windows will not help you very much on this. Actually, in a front-end, back-end case, Windows CAN'T help you, because your copy of Windows has no way to see the network tables of the machine on which your shared database resides. Unless, of course, you are also running Access on that machine.
Even reading the LDB file doesn't help because the problems associated with a user leaving "ugly" apply to it, too. I don't know that there is any really good solution to this problem if you are using a Windows back-end for Access.
Now, if you are using SQL Server or MySQL or ORACLE on a back-end, there is a chance (not a certainty) that you could run code on the back end server that updates a table based on network connections. One of the ARP commands in a command window could build a file of connected network slots that have not timed out yet. You could build something that runs on a timer to poll the ARP buffers for active connects, update the database on the server, and then read the table. This would be code-intensive and in the non-Access back-end case, also probably would not be VBA-based.
I have no idea how far you are willing to go to get this information, but if you want it reliably, it is going to be a very expensive solution.
Thanks for a thorough and instructive reply and "warning". 
I think I can read what you are writing and will go on use my feet and phone to get this information. (30+ persons are using my application to day.)
It's essential ia when I, from time to time, have to make design-changes.
Thanks again.
Krs
Beyer
I think I can read what you are writing and will go on use my feet and phone to get this information. (30+ persons are using my application to day.)
It's essential ia when I, from time to time, have to make design-changes.
Thanks again.
Krs
Beyer
- Local time
- Yesterday, 18:48
- Joined
- Feb 28, 2001
- Messages
- 30,503
Ah, that's a different question.
Here's how you do that.
1. Make a table of scheduled events with date and time for start and end of your down-time.
2. Build a startup form that never goes away unless it decides that YOU are the person trying to connect. I.e. have it hard code for your username or less-hard-code for a role associated with your username but not associated with general users. You would have to use a table of authorized users and their assigned roles to make this work.
3. When the startup form opens, have it read the schedule table. Do a query to see if any entry exists such that Now() BETWEEN EntryStart AND EntryEnd
4. If no such event exists, compute the time until the next scheduled downtime and display that on the form. If such an event exists and they are not you, have it open up a message box with vbOKOnly. MSGBOX "A scheduled down-time event is going on now. Please come back again later.", vbOKOnly, "Down Time"
You could be nice to them and make the prompt into a string that includes the end-time of the down-time event, of course.
5. Now... to keep this from running afoul of foul users who won't log off? When you close the opening form, don't close it. Minimize it and hide it instead. Don't allow anyone except maybe you (again, based on login table or role) to actually close the table. When you open the form, start a timer on that form, Me.TimerInterval = number of milliseconds. I use 60000 so every 60 seconds the Me.Timer event goes off, and you can trap that code with a Form_Timer event. In that event, repeat your query that checks for that event that straddles Now(). As long as there IS no such event, just reload the timer interval. BUT... if there is such an event, pop up a message box that says "We are scheduled for down time now. You are leaving now.", vbOKOnly, "Beginning time for scheduled maintenance." Then do an application.quit from the timer routine.
If you are using traps on forms to prevent folks from changing things without properly saving according to special rules, this gets more complex, but the idea is still the same. You might do something like an iteration through the forms and reports collections (i.e. the OPEN forms and OPEN reports) using Forms
and just stepping through them, to do a DoCmd.Close acForm, Forms.Name, acSaveNO (or whatever the other parameters are), and ditto for closing reports.
Of course, you must then publish your list of scheduled down times, but that should not be a particular issue. The opening form could even be designed to automagically show everyone the next (i.e. closest) scheduled down-time event.
Here's how you do that.
1. Make a table of scheduled events with date and time for start and end of your down-time.
2. Build a startup form that never goes away unless it decides that YOU are the person trying to connect. I.e. have it hard code for your username or less-hard-code for a role associated with your username but not associated with general users. You would have to use a table of authorized users and their assigned roles to make this work.
3. When the startup form opens, have it read the schedule table. Do a query to see if any entry exists such that Now() BETWEEN EntryStart AND EntryEnd
4. If no such event exists, compute the time until the next scheduled downtime and display that on the form. If such an event exists and they are not you, have it open up a message box with vbOKOnly. MSGBOX "A scheduled down-time event is going on now. Please come back again later.", vbOKOnly, "Down Time"
You could be nice to them and make the prompt into a string that includes the end-time of the down-time event, of course.
5. Now... to keep this from running afoul of foul users who won't log off? When you close the opening form, don't close it. Minimize it and hide it instead. Don't allow anyone except maybe you (again, based on login table or role) to actually close the table. When you open the form, start a timer on that form, Me.TimerInterval = number of milliseconds. I use 60000 so every 60 seconds the Me.Timer event goes off, and you can trap that code with a Form_Timer event. In that event, repeat your query that checks for that event that straddles Now(). As long as there IS no such event, just reload the timer interval. BUT... if there is such an event, pop up a message box that says "We are scheduled for down time now. You are leaving now.", vbOKOnly, "Beginning time for scheduled maintenance." Then do an application.quit from the timer routine.
If you are using traps on forms to prevent folks from changing things without properly saving according to special rules, this gets more complex, but the idea is still the same. You might do something like an iteration through the forms and reports collections (i.e. the OPEN forms and OPEN reports) using Forms
and just stepping through them, to do a DoCmd.Close acForm, Forms.Name, acSaveNO (or whatever the other parameters are), and ditto for closing reports.Of course, you must then publish your list of scheduled down times, but that should not be a particular issue. The opening form could even be designed to automagically show everyone the next (i.e. closest) scheduled down-time event.
