How many users are connected on access database and listing...
Possible in VBA For Excel?
Tks.
How many users are connected...
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How many users are connected...
For example:
Call like this:
Dim lngCount As Long
lngCount = CountUsersInDb("C:\Databases\MyDatabase.mdb")
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
Dim lngCount As Long
lngCount = CountUsersInDb("C:\Databases\MyDatabase.mdb")
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4334
- Joined: 26 Apr 2010, 17:36
Re: How many users are connected...
HansV wrote:For example:
Call like this: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
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?
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How many users are connected...
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.
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
Hans