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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

615 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