Go Premium for a chance to win a PS4. Enter to Win

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

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

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
OSI-IT
Asked:
OSI-IT
1 Solution
 
Don ThomsonCommented:
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
 
Joseph O'LoughlinCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Armen Stein - Microsoft Access MVP since 2006Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now