Avatar of Mohamed Singh
Mohamed Singh
 asked on

MS Access Limit Number of Simultaneous Users to 10

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 etc as the record source).
How can I prevent the 11th user from login - on,  seeing that only 10 users should be logged-on simultaneously.
Your assistance is greatly appreciated.
Kind Regards,
Mohamed
Microsoft AccessProgramming

Avatar of undefined
Last Comment
Mohamed Singh

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
James Elliott

There's probably any easier way of doing this, but you could use a logon trigger to write to a table. You could integrate some logic to rollback the 'transaction' if the server already has 10 active entries in your table.

http://technet.microsoft.com/en-us/library/bb153915(v=sql.105).aspx
Dale Fye

Can I ask why you want to limit concurrent access to 10 users?  Are you seeing a significant degradation in performance when you reach 10 users?

In addition to logging users into your application, you must also log them out of your application.

Occasionally, users will fail to log out of your application neatly, where neatly is defined as closing the application so that their logon will be released.  Sometimes, they will just close Access or their system will lock up and they will have to use the task manager to close Access (hopefully your error handler will prevent this necessity).  So you must provide a way to reset your logins.
Mohamed Singh

ASKER
Hi,
Thank You Jim Dettman and James Elliott for responding.
I actually used a procedure similar to James Elloitt's suggestion, and then granted the database administrator the access to uncheck tblUser.LoggedIn via frmUsers to provide for the unforseen instances where a user still has a record as being logged-on.
But, perhaps Jim Dettman's resource locking is a better solution.
Nevertheless, is there no method of interrogating the database engine ?
Kind Regards,
Mohamed
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Mohamed Singh

ASKER
Actually I have Keyed Access as well, just didn't get around to using it.
Jim Dettman (EE MVE)

<<Nevertheless, is there no method of interrogating the database engine ?>>

 No.  You need to store the number of concurrent users in some way, either in a table, as a lock on a file, in a file, etc.

There's nothing built into JET/ACE that allows a limit on the number of users.

Jim.
Mohamed Singh

ASKER
Hi Dale Fee,
I'm guarding against degradation and associated problems by limiting the number of simultaneous users.
Thank You for participating.
Kind Regards,
Mohamed
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mohamed Singh

ASKER
Hi Jim,
Please see:
(But how reliable is this and it should provide for password protected backends )

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
Dale Fye

Mohamed,

I honestly don't remember where I found the code in the attached, but it identifies the computers (not the specific user) that are logged into a particular mdb or accdb file.  You might be able to incorporate it into your application to do the count for you.
WhosUsing.accdb
Mohamed Singh

ASKER
Also the function should be version independent.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Mohamed Singh

ASKER
Thank You Dale,
I will certainly look into it.
Kind Regards,
Mohamed
Dale Fye

Mohamed,

I believe it is mostly the same as the code you posted just a minute before I posted
Mohamed Singh

ASKER
Hi Jim,

I went with the resource locking as it has the potential to address many issues from one central point.

Kind Regards,
Mohamed
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.