We help IT Professionals succeed at work.

Access / how many users connected?

Jess31
Jess31 asked
on
Using Access 2K.
I have two mdb files - a data.mdb and a fronend.mdb.
Is there a way to know how many fronend.mdb are connected to the data.mdb? I would like to know when one is the only person connected.
Comment
Watch Question

If it is network connections (not terminal) you can use operating system (Computer management), which will show you who is connected to shared files.
Top Expert 2016

Commented:
in the folder where data.mdb is located, locate for the file  data.ldb
you can open this with word and you will see the computer and user that is connecting to the data.mdb

see this link too

How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access
http://support.microsoft.com/?kbid=285822
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
You can use the MS LDB Viewer: (free)

http://support.microsoft.com/default.aspx?scid=kb;EN-US;176670

Download the Jetutils.exe file, which includes the LDB Viewer.  Easy to install.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Here is another version (the Lite version free) that supports A2007/10:

http://www.akcesoft.com/html/accuserlist.html

mx
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
There's also the  MSDBUSR.DLL for A95 and up if you want to stick with DAO and not use ADO.
JimD.

Author

Commented:
capricorn1:
Yes, that is that I am looking for.
However if one opens the mdb twice from the same pc it shows it only as once.
Is there away to show both or to prevent the mdb from opening again from same pc?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
:Is there away to show both or to prevent the mdb from opening again from same pc?:

Either of the links I posted will do this ... show both.

mx
Top Expert 2016

Commented:
Jess31,

see the codes from this link

http://www.mvps.org/access/api/api0041.htm

Author

Commented:
From the link I see that
>The simplest way to ensure that only one instance of the database can be opened on one desktop is to open the mdb file exclusively.
And since it is the front end mdb that is on a users desk that I want to prevent opening a second time this would seem the most direct way of doing it. Is this right?
How can I make it so that it opens in Exclusive mode?

Author

Commented:
DatabaseMX:
Thanks for the links.
The links you posted seem to be external programs that will give this information. But I need this from inside an access application. I also don't want to have to have another program to get this information since if it gets erased by somoneone that becomes a problem.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"But I need this from inside an access application."
That really wasn't mentioned in the Q.

"And since it is the front end mdb that is on a users desk that I want to prevent opening a second time this "
How would that happen anyway ?

mx

Author

Commented:
>How would that happen

Are you asking how would it happen that someone will open the same problem twice? Or how could it be prvented?

the "And since it is the front end mdb that is on a users desk that I want to prevent opening a second time this" is referring only to the part that I would like to keep the front end from being opened twice on the same PC, not to the orriginal question about how many people are connected to the back-end.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
I'm asking for a give PC, how would someone actually open the same MDB twice ?

mx

Author

Commented:
open access and open hte db
Database Architect / Application Developer
Top Expert 2007
Commented:
ok ... you can solve that issue with this:

http://www.mvps.org/access/api/api0041.htm

mx
>> The simplest way to ensure that only one instance of the database can be opened on one desktop is to open the mdb file exclusively.
And since it is the front end mdb that is on a users desk that I want to prevent opening a second time this would seem the most direct way of doing it. Is this right? <<

Yes, but to open exclusively is a pain (IMHO) because it is most often done through the target property of a shortcut.  What I often do is to set the "CONNECTION CONTROL" property to 1 in some start up code via the use of a public procedure that looks something like this ...


Public Sub ConnectionControl(lngState As Long)
'1 = Prevents NEW connections to the db
'2 = The default value which allows new connections.  db is reset to 2 when all users disconnect
   CurrentProject.Connection. _
      Properties("Jet OLEDB:Connection Control") = lngState
End Sub

For more information about Connection Control, check out:
http://msdn.microsoft.com/en-us/library/aa164890(office.10).aspx

-----

Now, for your connection count, here is some code that adapts the use of the UserRoster that has been mentioned through out the thread...

Public Function GetConnectionCount(strFileName As String) As Integer
'Get the count of connections to an .MDB file
   
    Dim cn As ADODB.Connection
    Dim rstConnections As ADODB.Recordset
    Dim strTemp As String
    Dim intX As Integer
   
    On Error GoTo Error_handler:
   
    'Open the connection
    Set cn = New ADODB.Connection
    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "MS Access;Data Source=" & strFileName
   
    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4 OLE DB provider.  You have to use a GUID to
    ' reference the schema, as provider-specific schemas are not
    ' listed in ADO's type library for schema rowsets
    Set rstConnections = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
   
    'Get the recordcount.  The recordcount will ALWAYS be at least one because
    'you OPEN a connection with this function.  If a user has two
    'open connections to the datafile, that counts as two, even though its the
    'same user.
    Do Until rst.EOF
        intX = intX + 1
        rst.MoveNext
    Loop
   
    GetConnectionCount = intX
   
    On Error Resume Next
    Set rst = Nothing
    cn.Close
    Set cn = Nothing
   
    Exit Function
   
Error_handler:
   
    MsgBox err.Description & " (" & err.Number & ") -- GetConnectionCount()", vbExclamation
    err.Clear
    On Error Resume Next
    Set rst = Nothing
    err.Clear
       
End Function


Remember, if you use the GetConnectionCount() code, remember that the app using the code will be counted as 1 connection, so the FE used to call this code will contribute 2 connections to the connection count if the FE has the BE opened in the course of its normal function when the GetConnectionCount() code is called.  Also, if you do upgrade to the ACCDB format (or check a file of that format), you will have to change your OLE DB provider.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"Yes, but to open exclusively is a pain (IMHO) because it is most often done through the target property of a shortcut. "

In fact ... >>  http://support.microsoft.com/kb/304528

mx