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
740 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
5 Comments
 
LVL 14

Expert Comment

by:Don Thomson
Comment Utility
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
Comment Utility
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
Comment Utility
<<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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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

Expert Comment

by:Armen Stein - Microsoft Access MVP since 2006
Comment Utility
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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

7 Experts available now in Live!

Get 1:1 Help Now