[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Max number of people Access w/ backend

Posted on 2013-11-07
10
Medium Priority
?
485 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 85
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 34

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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 800 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
 
LVL 34

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 400 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 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 40

Assisted Solution

by:PatHartman
PatHartman earned 400 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 34

Author Closing Comment

by:Mike Eghtebas
ID: 39653553
Thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

872 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