Solved

Who is logged onto MS Access or LAN

Posted on 2009-07-09
9
441 Views
Last Modified: 2012-05-07
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 ?
0
Comment
Question by:MarkFairhead
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 84
ID: 24812772
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
 

Author Comment

by:MarkFairhead
ID: 24813451
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
 
LVL 57
ID: 24814056
<<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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 84
ID: 24814095
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
 
LVL 10

Expert Comment

by:Luke Chung
ID: 24817393
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
 

Author Comment

by:MarkFairhead
ID: 24821248
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
 
LVL 84
ID: 24821917
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 24822417
<<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
 

Author Closing Comment

by:MarkFairhead
ID: 31601535
Thanks, this really helped.

Mark
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question