Solved

Who is logged onto MS Access or LAN

Posted on 2009-07-09
9
402 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 10

Expert Comment

by:LukeChung-FMS
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now