Limiting The Number of Simultaneous Users

[MSingh]

Hi,

In a multi-user environment, i need to limit the number of simultaneous users to 10.
There are multiple-back ends on the server linked to each front-end on each users desktop.
A permanent open connection (via linked tables to each of the back-ends) is created when the first form,frmDashboard opens.
(fsubDashboard1, fsubDashboard2, etc. has tblLink1, tblLink2 as the record source).

Please could you advise how i can prevent the 11th user from logging-on, seeing that only 10 users can be logged-on simultaneously.

Your assistance is greatly appreciated.

Kind Regards,
Mohamed

[HansV]

I'm not sure this will work. You could use the following function (courtesy Microsoft):

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.ACE.OLEDB.12.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
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Function

In the On Open event procedure of your dashboard form, use this function to count the number of users in the first backend database; if it is > 10, get out.

[MSingh]

Hi Hans,
That Worked - in my very limited test of just 2 simultaneous users ie. only permitting 1 user.
Though if i run it from the immediate window i get 2 connections when 1 user is logged-on,
so for now i just used:
If CountUsersInDb("MyDbFilePathName")>2 Then
Application.Quit
else
'...
End If

Many Thanks,

Kind Regards,
Mohamed

[HansV]

The code itself also creates a connection, so it's included in the count.