Who is logged onto MS Access or LAN

I have 150 staff using an Access 2003 database with a SQL backend.

I would like to find out which users are still logged onto the network, or onto MS Access when a certain task needs to be performed.  This  task is usually performed by one person, but in their absence ( out to lunch, on vacation, gone home etc ) there are 6 other people who can perform this task, unfortunately they are located on different floors and in different buildings, so knowing who to reassign the task too is difficult.

Can Access ( on a click of a button ) check to see if person 1 is still there and if not, check to see if person 2,3,4,5,6 is still logged on and assign them the task instead ?
MarkFairheadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access can use the JetRoster to see who is logged into the database but determining who is on the network is another story.
 See the "ShowUserRoster" example here; http://allenbrowne.com/func-ADO.html
I'm not sure how you assign tasks, so it's impossible to say how you would do that.
0
MarkFairheadAuthor Commented:
Thanks, unfortunately the Access 2003 database is a local database on each users C: drive.  So the ShowUserRosterMultipleUsers() function, just lists the local user.  The MDE and MDW files are copied down from the server each time a user logs onto the network.

For you information, the task I need completing is manual task on another computer system.  Once a Partner has approved a bank payment on Access 2003, Access simultaneously sends an email and a print out to a specific user for them to manually retype the payment instruction onto the relevant banking application.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Thanks, unfortunately the Access 2003 database is a local database on each users C: drive.  So the ShowUserRosterMultipleUsers() function, just lists the local user.  The MDE and MDW files are copied down from the server each time a user logs onto the network.>>
  You would check the roster for the backend datastore, not the front end on each computer.
  But your best bet would be to build this into the application via a login/logout.  Then you know whos in the application and can asssign tasks.  An alternative is to use a little bit of code and get the machine name and network login name.  That may suffice for your purposes.
  Part of the reason I say that you should build this yourself is that the only thing you'll get out of the roster is machine name and username.  For username, unless your using ULS (User level security), the username will always be 'admin', which is the name everyone logs in under unlesss ULS has been implemented.
JimD.
 
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd agree with JimD. It appears you're using ULS (since you mention a MDW file), so assuming you've assigned usernames then you could tell from using the JetRoster on the backend.



0
Luke ChungPresidentCommented:
As instructed by the moderators in situations where I provide a solution related to one of our commercial products:
Warning: This mentions a commercial product that addresses this issue, and the commercial product happens to be provided by my firm. This information is provided as a resource and is not meant to be a commercial solicitation.
====
Our Total Access Admin product does exactly what you're seeking.  Point it to your back end MDB and see users logging in and out of it in real time.  
A free demo version is available so you can use it without cost.  It also catches dropped connections which is a potential source of database corruption (nice to know who'd doing that repeatedly).  It can create a log file of all the activity. You can even compact the database after all the connections are closed.
For more info, visit: http://www.fmsinc.com/MicrosoftAccess/monitor.asp
0
MarkFairheadAuthor Commented:
Thank you for your advice, but I dont have a backend MDB ( all SQL ) although I could build one just for this solution.

Currently ( when a user loges onto the database ) I update a SQL Table with the users name and time of log on.  I also clear this entry when they log off.

The problem I am having ( despite endless reminders ) is that a number of users log off incorrectly, either by closing the Access window instead of using the Exit button on the form or by shutting down their PC with the applications still open, or just leaving their PC on overnight.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I entirely missed the SQL Server part of this ... sorry ...

You can use sp_Who to see the users logged into the server:

http://msdn.microsoft.com/en-us/library/ms174313.aspx

You can run this in SQL Query Analyzer, or build a simple interface in Access that shows these results ...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The problem I am having ( despite endless reminders ) is that a number of users log off incorrectly, either by closing the Access window instead of using the Exit button on the form or by shutting down their PC with the applications still open, or just leaving their PC on overnight.>>
  You can disable the x in the Access windows easily enough by opening a hidden form on startup.  On that form, include a checkbox or text control called something like cbxOKtoclose.   Then in that forms Unload event, run something like the following:
 
Private Sub Form_Unload(Cancel As Integer)
          ' A close was started - need to wait until menu says its OK to close.
10        If Not (Me![cbxOKtoclose]) Then
20            gstrMBTitle = "Close error."
30            gstrMBMsg = "You cannot close the application by closing the main Access window."
40            gstrMBMsg = gstrMBMsg & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Please close the application normally to exit."
50            gintMBDef = vbCritical + vbOKOnly
60            gintMBBeep = True
70            gintMBLog = False
80            Call DisplayMsgBox
90            Cancel = True
100       End If
End Sub
   Set the control cbxOKtoclose from the exit or logoff button of your app.   Because it's the first form to open, it will be the first form that Access tries to close.  By checking the flag, you can determine if the user is logging off properly or is trying to use some other means (like the X on the main Access window).

  I missed to that it was a SQL backend too.  Sorry about that.
JimD.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MarkFairheadAuthor Commented:
Thanks, this really helped.

Mark
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.