[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

Max number of people Access w/ backend

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
Mike Eghtebas
Asked:
Mike Eghtebas
  • 3
  • 3
  • 2
  • +2
4 Solutions
 
mbizupCommented:
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
 
mbizupCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
mbizupCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
PatHartmanCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now