Solved

Is there a limit on the number of users that can use MS Access 2007 to connect to MySQL Database

Posted on 2014-07-20
5
813 Views
Last Modified: 2014-07-23
Situation: 100 users working inside an office network.  Back-end database is setup in MySQL.  Each user connects to this back-end with their own copy of MS Access which acts purely as a front-end user interface.  MS Access links to the back-end using MySQL ODBC driver.

Known: Plans are already in the works to move away from MS Access and the MySQL back-end in favor of a MS SQL back-end and a .Net front-end user interface.  We are already aware that MS Access does use more network resources in general.

Question #1: Given that each user has their own copy of MS Access 2007 and that MS Access is not acting as a database, but just a front-end...is there a user limit per se?  Microsoft lists a theoretical concurrent limit of 255 users...but is this limit only applicable when MS Access is also the acting back-end?  Currently, we are not experiencing any issues with the number of people using MS Access (on average, 90 copies are open and running at the same time).

Question #2: Does the MySQL ODBC driver connection to the back-end involve JET (as it is not explicitly stated anywhere in the program)?
0
Comment
Question by:OSI-IT
[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
5 Comments
 
LVL 14

Expert Comment

by:Don Thomson
ID: 40207662
Unless the SQL database has some limit set in the software it's 32768  concurrent users (SQL 2000  has less)

It really come down to how your memory, diskspace, network speed and bandwidth  does with the data - The more users - the longer the search times
0
 
LVL 11

Expert Comment

by:Joseph O'Loughlin
ID: 40207737
On the server or workstation hosting the mysql database open a cmd prompt and check
net statistics server
and check the max number of connections and those error-ed out
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40208677
<<Microsoft lists a theoretical concurrent limit of 255 users...but is this limit only applicable when MS Access is also the acting back-end?  >>

  255 users applies to a JET/ACE database.  Since your using something other than that, there is no limit.   If you had a single copy of the front end and everyone sharing that, you would be limited to 255 users.   But since everyone has their own copy (which they should just as you would install any "app" locally), then the limit does not come into play.

<<Question #2: Does the MySQL ODBC driver connection to the back-end involve JET (as it is not explicitly stated anywhere in the program)?>>

  Yes and no.  It depends on how you use the connection.  If your simply using linked tables in Access, then yes, JET is always in the picture.   It should be pointed out though that if no JET specific SQL, Expressions, or joins on local tables are performed, then JET is smart enough to pass off the SQL statement to the ODBC source.   JET is still loaded in memory, but it passes the brunt of the work through.

 If you use a pass-through query, then no.  Like wise if you use ADO in code, then no.

 It used to be that you could also use a ODBC direct workspace to by-pass JET, but those were dropped from the product when ADO came into the picture.

<<Known: Plans are already in the works to move away from MS Access and the MySQL back-end in favor of a MS SQL back-end and a .Net front-end user interface.  We are already aware that MS Access does use more network resources in general.>>

  Your mixing up some different things here.  An Access FE will use no more network resources than something like .Net as long as JET is not part of the connection.   It all depends on how the Access database is developed.

 It also depends on where your data is stored.   If you used a JET/ACE database and a .Net front end, then it would be no different than using Access.

Jim.
0
 
LVL 57
ID: 40208687
I should also add that a more realistic comparison between Access and .NET as an application should revolve around things Access cannot do or has issues with:

1.  Inability to do n-tier designs
2.  Not able to build a true .EXE (which makes it sensitive to the run time environment).  An Access "App" is more like a document that is read rather than a true program.
3. Cannot use many 3rd party controls.
4. Installation/version issues

 If none of that bothers you, then you would use Access, as it gives you many things right out of the box that you have to work hard for in other products.

Jim.
0
 
LVL 9
ID: 40215812
I know this is already closed, with great posts by Jim.  I'll just add this:

There are techniques to optimize Access FEs with a client server back-end like MySQL.  I've written a PowerPoint presentation on this for SQL Server databases, but most of it applies to MySQL too.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

Cheers,
Armen
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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