Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Who's Logged On / Who's Connected (v1.6.3b)

Posted by: datAdrenaline Oct 25 2009, 12:02 AM

Description ...
This is an update to the utility originally posted The utility will log the users who are connected to an MDB/E or ACCDB/E file via the feature of the Jet/ACE database engine implemented with the introduction of Access 2000. In addition, the utility can manage the 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" (possibly corrupted) 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!
THere 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. ( 66.66K ): 2731

Posted by: datAdrenaline Dec 6 2010, 12:11 PM

Updated to v1.6. v1.6 includes an option to enable/disable "Passive Shutdown" via the 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.
YI: v1.4 download count was 471. Count was reset upon updating to v1.6
- 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 ).

Posted by: datAdrenaline Dec 20 2010, 02:43 AM

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

Posted by: datAdrenaline Mar 19 2012, 02:53 PM

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.
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.

Posted by: datAdrenaline Mar 20 2012, 01:23 PM

Uploaded v1.6.3a
onverted code to use "late binding" for ADODB objects in order to avoid VBA reference conflicts.

Posted by: datAdrenaline Mar 22 2012, 10:52 PM

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

Posted by: zyxblof Aug 8 2018, 07:04 AM

Still very useful. I've used the utility to check who's connected on a 2013 accdb backend and discovered an "unofficial " user locking the database.

Using access 2013, converted the mdb to accdb.


Posted by: tina t Jan 21 2019, 06:16 PM

When to use it ...
- Use to log who has/is connected to your datafile.

hello datAdrenaline, is there anything i can tweak to get just this core functionality for A97 mdb files? sometimes we really need to know who is connected to a database right now. tina

Posted by: datAdrenaline May 3 2019, 01:35 AM

You can actually use the utility to point to an A97 file, you just have to run the utility in an A2003 or higher version. If you want the utility in A97 format, I think I have a copy somewhere in the depths of some flash in the "top kitchen drawer" smile.gif -- the key is the ADO provider. By the way, sorry for the late reply!

Posted by: tina t May 3 2019, 01:25 PM

no problem, and if you actually have an A97 version already built, that would be wonderful! :) tina

Posted by: pnjones99 May 14 2019, 05:20 AM

Thanks - customised for my needs and works well