How many users are connected...

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

How many users are connected...

Post by sal21 »

How many users are connected on access database and listing...
Possible in VBA For Excel?
Tks.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How many users are connected...

Post by HansV »

For example:

Code: Select all

Public Function CountUsersInDb(strDatabase As String)
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim i As Integer

  On Error GoTo ExitHandler

  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";Persist Security Info=False"

  Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
  , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

  Do While Not rs.EOF
    i = i + 1
    rs.MoveNext
  Loop

ExitHandler:
  CountUsersInDb = i

  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing
End Function
Call like this:

Dim lngCount As Long
lngCount = CountUsersInDb("C:\Databases\MyDatabase.mdb")
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: How many users are connected...

Post by sal21 »

HansV wrote:For example:

Code: Select all

Public Function CountUsersInDb(strDatabase As String)
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim i As Integer

  On Error GoTo ExitHandler

  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";Persist Security Info=False"

  Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
  , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

  Do While Not rs.EOF
    i = i + 1
    rs.MoveNext
  Loop

ExitHandler:
  CountUsersInDb = i

  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing
End Function
Call like this:

Dim lngCount As Long
lngCount = CountUsersInDb("C:\Databases\MyDatabase.mdb")

tKS...
But this code enumerate only the user have access to the file? or not?

But to listng the names of users?

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How many users are connected...

Post by HansV »

The code counts the number of users using the database.

Here is sample code that lists the individual users. This version lists them in the Immediate window, but you can easily modify it to populate an array, or a list box, or a list view.

Code: Select all

Sub ShowUsers(strDatabase As String)
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";Persist Security Info=False"
    Set rst = cnn.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

    Debug.Print rst.Fields(0).Name, rst.Fields(1).Name, rst.Fields(2).Name, rst.Fields(3).Name

    Do While Not rst.EOF
        Debug.Print rst.Fields(0), rst.Fields(1), rst.Fields(2), rst.Fields(3)
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
Best wishes,
Hans