Description ...
This is an update to the utility originally posted here. The utility will log the users who are connected to an MDB/E or ACCDB/E file via the UserRoster feature of the Jet/ACE database engine implemented with the introduction of Access 2000. In addition, the utility can manage the Connection Control (aka: Passive Shutdown) property for the database file being monitored.

The file format of the utility is Access 2000. However, as indicated, All Jet/ACE formats from Access 2000 and forward can be managed as long as the correct OLE DB Provider for the Jet/ACE engine is installed.

When to use it ...
- Use to log who has/is connected to your datafile.
- If you set it to "AutoRefresh" at a given interval along with the "Update Records" option, you can find all the users that have logged on and when they logged off.
- You can use it to figure out who was connected to the datafile when the database goes "corrupt"
- I have found the app quite useful in tracking usage levels during the day and which computers are using my datafiles.
- To manage the Connection Control property (Passive Shutdown) on database files. The Connecton Control property, when enabled, prevents new connections, thus allowing your connection count to shrink passively.

How to use it ...
1. Download and unzip the file, then open the file in Access 2000 or higher.
2. The interface form opens automatically (frmConnectedUsers)
3. Browse for the .MDB/E .ACCDB/E file you want to monitor or examine.
4. "Refresh" the list of connected users as needed via the Refresh button or toggle the AutoRefresh option.

Upon selecting a file, the code will interrogate the file and extract the Computer Names and User names that are connected (aka: have the file open). Subsequent interrogations are invoked via a command button or the AutoRefresh option. The results of the interogation are stored in a local table (tblConnectedUsers)

Options available when using this utility. (Some are static between sessions and stored in the local table named tblSettings.)
- You can set an "AutoRefresh" rate which will run the analyzation at a given interval (uses Form_Timer)
- You can DELETE, MODIFY, or APPEND the records in tblConnectedUsers upon each "Refresh" attempt.
- You can either keep the connection object to the datafile static (aka: means you don't have to reconnect to the MDB after each Refresh ... this is MAY be helpful in finding the user who causes your database to go corrupt!)
- You can select to record either the Access Workgroup Username or the Windows User name.

Data that is recorded in tblConnectedUsers ...
RecordID - AutoNumber
ComputerName - The computer name that was connected to the datafile
UserName - The id of the user from the .MDW file on the ComputerName (typically Admin) (aka: Access User Name) --- OR --- The remote computer's Active Directory logged in userId (aka: Windows User Name). This is a user selectable option.
Connected - Is the User Connected (will only show No/False if you have the Update Records option set)
SuspectState - If the user has left the database in a "SuspectState" will show true, otherwise NULL

Notes and Cautions ...
- You should not have mulitple instances of this app open, unsless you change the name for each instance. For example: you should not have two instances of "WhoIsConnected.mdb" on the same machine, however, you can copy and rename the app as much as you want and open each copy, even on the same machine: "WhoIsConnected_1.mdb","WhoIsConnected_2.mdb","WhoIsConnected_3.mdb". The reason for this restriction is that the data collected is stored in a local table, plus many of the settings are stored in a local table so if multiple instances of the same mdb are created, conflicts will arise.

- Permissions or other IT related configurations may prevent the code from reading the Windows User name.

- I have not tested this utility on a CORRUPT database. The option of keeping the connection to the datafile as a "STATIC" object (controled with the "Force a New Connection" option), should maintain the "Connection" to the datafile and allow an analyzation of the UserRoster when/if the datafile goes corrupt during the monitoring process. I did not have a database go corrupt during the time I was monitoring it ... so ... well there you have it ... I hate to "Release" an app that has not been completely "Tested" ... but I just could not get a datafile to become corrupt while I was monitoring it ... go figure!

-----

Here it is ... Enjoy ... and PLEASE send me any recommendations for improvement or notifications of bugs. Also, if you "track this topic" (under the Options button) with email notification, you will be notified of updates and other postings on this thread topic regarding this utility

Edits:
Attachment updated on 2012-03-21 (datAdrenaline)

Updated to v1.6. v1.6 includes an option to enable/disable "Passive Shutdown" via the Connection Control property available with Jet 4.0 and higher (ie: ACE 12) and exposed by ADO. In addition, there is now an option to display the remote userId (the Windows user name connected to the db), or the Workgroup User name (Access's User Level Security name). The user name to show is selectable via a combo box above the user name column in the display grid.

FYI: v1.4 download count was 471. Count was reset upon updating to v1.6

Notes:
- Will not work in Access 2010 64-Bit (32-bit seems fine). API calls need some work to be valid in a 64-bit environment. {See post #5}
- In the Windows 7 OS, users have reported issues with the "Windows User Name" feature -- I *think* it has to do with permission levels. I don't have a Win 7 box running a 32 bit version of Access to debug the utility (as of the date of this post at least (IMG:style_emoticons/default/smirk.gif) ).

Updated to v1.6.1
- Removed some extraneous stuff that I used to testing/playing --- and forgot to remove before posting v1.6.

Hi,

Loved this program since v 1.2

Only issue I have is the same I had before. It only displays my Windows name and shows others as blank. I can see machine names for them but that's it.

Version 1.6.3 has been uploaded. v1.6.1 had 608 downloads (2374 total download history of utility); the D/L is reset upon upload of new version.

Updates:
Removed dependancy on API's and therefore is compatible with 64bit Access. With this change, the strategy for obtaining the Windows User is different than in v1.6.1. If you get a message of "Unable to Aquire" it is likely that you don't have permissions to read those values.

Please let me know of issues or recommendations.

Regards.

Uploaded v1.6.3a

Converted code to use "late binding" for ADODB objects in order to avoid VBA reference conflicts.

Updated to v1.6.3b ... Fixed a DAO reference issue caused by developing in A2010 x64. v1.6.3b has been tested in A2003.

I have this setup so that both YES and NO show for connected. I'm wondering what the date represents if I connect via the utility for Yes and No. Yes seems to maybe be the time the utility is run.

JT

The "Timestamp" column indicates the last time the user was showing as "connected" when the utility was ran. The "Connected" column represents weather or not the user was connected when the utility ran (data was refreshed). The "connected" column will show No if you have the logging option set to "Update" and the user/computer indicated was not connected when the data was Refreshed.

So, if you have the logging option set to Update and the AutoRefresh enabled, you will see that the Timestamp field updates with each refresh, if a user disconnects from your monitored db, that users row will show 'Connected' as No and the Timestamp will remain set to the last time the utility saw that user/computer as being connected to the db.

Does that make sense?

Is it possible to capture the local computer login name of the person connecting in 2010? Not using workgroup security. I use the FOSUSERNAME() utility locally but would love to have that captured into this connection table?

Thanks, Bonnie

This post has been edited by bhicks11: Aug 27 2012, 08:05 AM

Oops sorry, found it. Thanks very much for sharing this very useful utility.

Bonnie

You are quite welcome Bonnie! I am glad you find it useful --- and I am glad you found the drop down for the user name (IMG:style_emoticons/default/smile.gif) (IMG:style_emoticons/default/thumbup.gif)

Got a question please: I have users on VDI (terminals) and those the Computer Name shows uniquely for each one, the UserName seems to report their supervisors name though they login uniquely. Is that because of the terminal? And thanks again for sharing this - finding it very helpful.

Thanks, Bonnie

This post has been edited by bhicks11: Aug 28 2012, 02:54 PM

We use Citrix here with the utility you mention. When the user is connected to Citrix, we can only see the name of the Citrix machine. I haven't tried grabbing the username, but suspect I'd run into the same issue as you.

JT

RCJT,

>> I haven't tried grabbing the username, <<

Are your using the latest version? Can you try to grab the Windows User by using the drop down above that column and let us know what you find out?

bhicks11,

>> Got a question please: I have users on VDI (terminals) and those the Computer Name shows uniquely for each one, the UserName seems to report their supervisors name though they login uniquely. <<

???? ... Wow! I have never heard of that before! I have know way to test this scenario to find out what is happening, but I will do some digging in the WMI classes (which is how I get the remote user name) to see if I can find anything.

I am using the lastest version and have selected the Windows User option. Got me baffled too.

Hello bhicks11,

I figured you were using the latest --- I was wondering if RCJT was :-/... but I found this yesterday when looking at the UserName property of the Win32_ComputerSystem WMI Class that is used to get the remote user name in the Who's Logged On utility:



The second part of the description is what struck me --- and made me wonder if you were somehow being effected by this behavior.

Yup, sounds like it. Thanks for your time.

This seems to be exactly what I've been searching for. However, my database is in .accdr format. I created the database in .accdb format. When I move the "production" version to a shared folder for the users, I rename it to .accdr which is just a runtime version of it. It easily allows people to run it with either the client or runtime version of Access. However, it doesn't allow people with the client version to get into the design. Is there any way that I can run this against my .accdr database? When I tried, I get a message that it must be an MDB/MDE or ACCDB/ACCDE file.