Detecting the number of users logged into an external database.

Got some really useful code from JezWalters on how to detect the number of users using the same front-end (see code).

Could it be adapted to check on the connections within an external database?

Thanks as always.
Option Explicit          ' Force explicit variable declaration 
Option Compare Database  ' Use database comparison order 
 
' 
' Count current database users 
' 
Public Function CountUsers() As Long 
 
    ' Declare constants 
    Const USERROSTER_SCHEMA As String = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"  ' User roster schema GUID 
 
    ' Declare variables 
    Dim rstUserRoster As ADODB.Recordset  ' User roster recordset 
 
    ' Count users 
    Set rstUserRoster = CurrentProject.Connection.OpenSchema(adSchemaProviderSpecific, , USERROSTER_SCHEMA) 
    Do Until rstUserRoster.EOF 
        CountUsers = CountUsers + 1  ' Can't use RecordCount! 
        rstUserRoster.MoveNext 
    Loop 
 
End Function

Open in new window

Andy BrownDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"how to detect the number of users using the same front-end (see code)."

But isn't this front end 'external'?  If it was local on your system, then you are the only it would detect. So ... that code can certainly read the number of users in an external back end ... if you see what I mean?

mx

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
btw ... that code if from http://support.microsoft.com/kb/285822

mx
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are you referring to an external database other than Access ?

mx
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Example code here:
http://www.fmsinc.com/free/newtips/access/accesstip2.asp
 To use the JET USER ROSTER function on a DB.
 Example shows Northwind being polled.
JimD.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Looks similar to the code in the KB above. ... just an fyi.

mx
Leigh PurvisDatabase DeveloperCommented:
There's either a problem:
A Front End application file being shared by several users from a single network location instead of local client installs.
Or a misconception:
That the user roster information will return data about users in the database to which the FE is connected, via linked tables or otherwise
Or the answer is one distinct user - just the running user - and that's all there should be and is the purpose of the test.
To check for an external Jet file it's exactly the same, but as simple as pointing your connection object to that file.
i.e. Where the code above uses:
Set rstUserRoster = CurrentProject.Connection.OpenSchema

The original KB from MS uses
Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema
You'd change that one italic line to
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\YourData\File.mdb"

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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Looks similar to the code in the KB above. ... just an fyi.>>
 it's similar, but not the same.  The MSKB article simply uses the currentproject object of the currently opened database.   As Leigh pointed out, the connection object needs to be changed.  If you look at the sample code, that's what it does.
 It's hardcoded for Northwind, but you could replace that with anything.  
JimD.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I see.  
mx
Leigh PurvisDatabase DeveloperCommented:
Yeah, I'm just not as fast at posting as I used to be.
And giving me things like italics to play with... They might as well hand over a pack of crayons! ;-)
Andy BrownDeveloperAuthor Commented:
Thanks guys, i'll take a look into this a little later on.
Andy BrownDeveloperAuthor Commented:
Thanks everyone for your help.  This answer was perfect for what I needed to do - thanks again.
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.