Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Limit Number of Simultaneous Users to 10

Posted on 2014-11-18
13
Medium Priority
?
269 Views
Last Modified: 2014-11-22
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
0
Comment
Question by:Mohamed Singh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40449861
You could use a commercial product such as:

http://www.peterssoftware.com/ka.htm

or build something in yourself using the ideas here:

http://www.experts-exchange.com/Database/MS_Access/A_5328-Resource-locking-in-your-applications.html

 Using this, you would create one "resource lock" for each user as they started up the app.  It would then be a simple matter to check the current number of locks and stop them if the limit was exceeded.

Jim.
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40449869
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
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40449903
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.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Mohamed Singh
ID: 40449935
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
0
 

Author Comment

by:Mohamed Singh
ID: 40449944
Actually I have Keyed Access as well, just didn't get around to using it.
0
 
LVL 58
ID: 40449949
<<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.
0
 

Author Comment

by:Mohamed Singh
ID: 40449952
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
0
 

Author Comment

by:Mohamed Singh
ID: 40449978
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
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40449981
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
0
 

Author Comment

by:Mohamed Singh
ID: 40449982
Also the function should be version independent.
0
 

Author Comment

by:Mohamed Singh
ID: 40449989
Thank You Dale,
I will certainly look into it.
Kind Regards,
Mohamed
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40449993
Mohamed,

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

Author Closing Comment

by:Mohamed Singh
ID: 40459084
Hi Jim,

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

Kind Regards,
Mohamed
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question