Solved

Max number of people Access w/ backend

Posted on 2013-11-07
10
434 Views
Last Modified: 2013-11-16
Access front-end working with an Access back-end can have about 6 users without slowing down I suppose.

Question 1:  What is the number of people for Access front-end working with SQL-Server linked tables?

Question 2: Is a disconnected data source like ADO.Net possible in MS Access? If so what version of Access. If not ADO.Net perhaps OLEDB?

I appreciate some comments if you have experience using these configurations in the past.

Thank you.
0
Comment
Question by:Mike Eghtebas
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39629750
Question 1...

The max number of people does not change for your Front-end with a move to SQL Server. The max number of concurrent users, regardless of back-end platform is 250.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39629756
Sorry... 255.  This lists specifications/limitations of Access:
http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx


(That said... your users should not be sharing a Front-End.  They should each have their own copy)
0
 
LVL 84
ID: 39629794
Question 2: Yes, you can work with disconnected recordsets in Access. You can create an ADO Recordset with a clientside cursor and bind your form to that, and you're essentially working with a disconnected recordset.

Or you can work with truly unbound Access apps, where you manage all data manipulation, and the Forms are not bound to any recordsource - you pull the data from Access and fill the textboxes, etc. This does eliminate one of the best things about access (subforms), but it can be done.

Question is why would you do it? If you're moving into the unbound/disconnected world, then use .NET. It's made to work that way, and it's a much better platform for that type of application.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39629823
mbizup,

Thank you for the posts. But 255 is a big jump from what commonly known Access front-end with linked tables. Is this because it is Access 2010 or Access 2003 or 2007 could perform as good. I will look for 2003 and 2007 specs shortly.

LSMConsulting,
re> ...why would you do it?
I appreciate to comments and the observation. I have Dec 15 deadline and very comfortable with Access compare to c# ADO.net for example. I intend to build a functioning (prototype) application to be used for a while before I get to develop it in c# using ado.net.

I will have some new related question shortly and will provide a link here in case you had the time to respond.

Mike
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39629833
255 is the published limit. The actual limit is much less than that, and is highly dependent on how the app is constructed, delivered, and setup. In general, I believe the real-world cap is somewhere around 15 or so, although there are plenty of people around who run Access apps with more users than that.

If this is just a prototype, then why worry with unbound/disconnected at all? Just use a standard bound Access app for the prototype.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39629859
Although it is prototype but it will be used as a production app for a while. Max number of users will never get to 15 and there are not very complicated operations in it.

I will try to use some stored procedure to shift labor to SQL Server.
 
see the following link for a question on calling stored proc from an access form:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28287845.html

Thanks,

Mike
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 100 total points
ID: 39629871
Agree with LSM that the real-world maximum can vary greatly.

We know that the 'hard limit' is 255, but actual recommendations I have seen are between 15-20.  

In addition to construction and setup as Scott described, this is also dependent on how the database is used (and infinite combinations of all of those factors).  I personally have seen some databases fall over with more than 5 concurrent users and others work flawlessly with far more than 20.  

Also, if the SQL back-end itself is of interest to this question (ie: users with individual front-ends connecting to a SQL back-end), that hard limit of 255 users is not a factor.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
ID: 39629894
<<this is also dependent on how the database is used (and infinite combinations of all of those factors).  >>

I've actaully seen a JET based app running with 200+ users.   But it was read only reporting app.

I don't think there is any problem getting close to 40 with a typical read/write app that's written well.   But beyond that, it's tough to keep that many stations stable.

<<Also, if the SQL back-end itself is of interest to this question (ie: users with individual front-ends connecting to a SQL back-end), that hard limit of 255 users is not a factor. >>

 Sky really is the limit in that case...BUT your still dealing with an Access FE.  If I had anything that was going to scale into the hundreds of users, I would not be using Access.

 You still have the fundamental problem of Access/VBA being sensitive to it's environment.   Granted you have some of those problems with true compiled products as well, but not to the same degree.

Jim.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 39642192
Question 1:  What is the number of people for Access front-end working with SQL-Server linked tables?  
Since each user always has his own personal copy of the FE, the concurrent user count is limited by the SQL Server license and has nothing to do with Access.  Remember, Access apps are always split into FE and BE for stability and the FE is never shared.  If you were to share the FE, you would be limited to 255 which is the published limit for concurrent users.

Question 2: Is a disconnected data source like ADO.Net possible in MS Access? If so what version of Access. If not ADO.Net perhaps OLEDB?
There is no reason to use anything other than bound forms.  The key is to bind the forms to queries that include criteria.  Access makes every effort to pass-through every query so unless you have done something to prevent SQL Server from interpreting the query, Access passes it to the server and the server sends back a recordset.  Disconnected recordsets don't add any value to this process.  They just add considerably more code.  Although you may occasionally run into an I/O intensive process that will benefit from being written as a stored procedure rather than VBA code, I wouldn't start with that assumption.  I use SQL Server, Oracle, DB2, Sybase, etc for over 90% of my applications and only one ever needed a stored procedure and that was a complex overnight batch process.  I have a couple of instances where I used pass-through queries and a few more where I created views.
0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 39653553
Thank you.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Help - 12 42
SQL Maintenance Plan 3 17
How to calcualate lateness in Access 2010 11 28
BULK LOGGED - log full 9 17
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now