number of connections

[dboira]

How can I know the number of connections to a database from a client?

 

[930driver]

Here is a function that I use to see who is using a back end. You can populate an unbound field on a form with it.

It would be easy for you to modify the loop to count the number of users rather than display the names.


'-------------------------------------------------------------------------------------
' Subject : WhosOn()
' Purpose : Will read *.LDB file and read who's currently
' logged on and their station name.
'
' The LDB file has a 64 byte record.
'
' The station name starts at byte 1 and is null
' terminated.
'
' Log-in names start at the 33rd byte and are
' also null terminated.
'
' I had to change the way the file was accessed
' because the Input() function did not return
' nulls, so there was no way to see where the
' names ended.
'-------------------------------------------------------------------------------------
Private Function WhosOn() As String

On Error GoTo Err_WhosOn

Dim iLDBFile As Integer, iStart As Integer
Dim iLOF As Integer, i As Integer
Dim sPath As String, x As String
Dim sLogStr As String, sLogins As String
Dim sMach As String, sUser As String
Dim rUser As UserRec ' Defined in General
Dim dbCurrent As DAO.Database

' Get Path of current database. Should substitute this code
' for an attached table path in a multi-user environment.

Set dbCurrent = DAO.DBEngine.Workspaces(0).Databases(0)
'sPath = dbCurrent.Name
sPath = getattachedpath(CurrentDb.Name, "tblCustomers&quot
'dbCurrent.Close

' Iterate thru dbCurrent.LDB file for login names.

sPath = Left$(sPath, InStr(1, sPath, ".&quot) + "LDB"
Me.txtDB = sPath

' Test for valid file, else Error

x = Dir(sPath)
iStart = 1
iLDBFile = FreeFile

Open sPath For Binary Access Read Shared As iLDBFile
iLOF = LOF(iLDBFile)
Do While Not EOF(iLDBFile)
Get iLDBFile, , rUser
With rUser
i = 1
sMach = ""
While .bMach(i) <> 0
sMach = sMach & Chr(.bMach(i))
i = i + 1
Wend
i = 1
sUser = &quot;&quot;
While .bUser(i) <> 0
sUser = sUser & Chr(.bUser(i))
i = i + 1
Wend
End With
sLogStr = sMach & &quot; -- &quot; & sUser
If InStr(sLogins, sLogStr) = 0 Then
sLogins = sLogins & sLogStr & &quot;;&quot;
End If
iStart = iStart + 64 'increment to next record offset
Loop
Close iLDBFile
WhosOn = sLogins

Exit_WhosOn:
Exit Function

Err_WhosOn:
If Err = 68 Then
MsgBox &quot;Couldn't populate the list&quot;, 48, &quot;No LDB File&quot;
Else
MsgBox &quot;Error: &quot; & Err.Number & vbCrLf & Err.Description
Close iLDBFile
End If
Resume Exit_WhosOn

End Function


&quot;The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!&quot;

 

[sachiel]

If you are trying for a SQL server, use the system stored procedure:

EXEC sp_who

It will give you all the information on who is connected and what they are doing.

Good luck.