MS Access vs MySQL (1 Viewer)

This is not really a tech support question, but more of a debate/discussion starter about the differences between Access and mysql. Over the past few months I have learned a lot about Access and I also know a "little" about web design and mysql (not nearly as much as I do about Access though). I don't understand how mostly everybody I talk to about this agrees that mysql is far better than Access and I should give up on all my access projects and switch everything to mysql. Maybe my limited knowledge in mysql is the reason for not understanding why, but I cant see how that is the case.

1. I can understand that maybe mysql is faster and can hold more information, but none of my current uses will ever need the speed and storage of 1,000,000+ records.

2. My other reason for not agreeing with the general population on this debate is for the general ease of designing an Access database. It has a very neat graphical interface where you can create queries with a few clicks, design forms graphically on screen to look whatever way you please, create reports, navigate so easily through records and have them returned to you in the graphical manor you like.

3. To my understanding with a mysql database, the user that wants to add, view, or manipulate data/records needs to be fluent in the SQL language and type out long complex "select" statements each time they want to retrieve data, and then on top of that, the data is returned in a spreadsheet type format, as opposed to a easily readable form as Access does.

4. How to access the mysql database seems more difficult than Access to me also. Again, to my understanding, the main way to connect/access data in a mysql database is through a web browser and the mysql database NEEDS some type of server to be hosted on, where Access is just a single file located on your computer or can easily be split into front and back end files for a home or small office network, and no need for a server.

5. I'm contradicting myself on this one a little here and may actually be answering my own question, but with Access, a database designer designs it so the user can easily use it, and I'm guessing it is the same with a mysql database, but with mysql, I don't understand what platform the user uses and also can mysql database records be viewed in "form" or "report" view as is MS Access?

i don't mean to be against all the people who told me otherwise, but I just cant see how mysql is better and easier than MS Access is. The only thing I can think of is that maybe everyone was assuming that my database was for a large corporate situation being accessed by multiple users through multiple locations, but in reality all of my database applications would never need to be accessed remotely and are for small business applications with rarely more than one user at a time accessing it.

Please give me your input on this debate, and I know this is an Access forum, but maybe someone can clear up any wrong assumptions I listed about my knowledge about mysql.

(btw... All the people that told me that mysql was better was from web design forums where mysql is the standard practice for those forums. Maybe on an Access forum I will get people on my side and not feel like I just don't understand something)

To help you understand the difference, it will help uf you have a better understanding of Access. This way you will know what you really are comparing.

Is Access a database?
No. It is not.

Access really is a just Front end. Access by default uses the JET database engine in 2003 and eariler versions. With Access 2007, there is also a new database engine called ACE.

When making a database comparison between mySQL or any SQL server with "Access", you are really comparing the the datasbe engine that ships with Acess, not the actual Access program.

Since Access is just a front end to a database engine, you can also use other back ends besides JET/ACE. You can use mySQL serve, Microsoft SQL server, Oracle, an other database that have ODBC drivers available.

You do not create forms in an SQL Server (mySQL included). You still need a front end tool, like Access or PHP, to create the user interface.

What does this mean?

• Access really is a front end (User Interface/GUI).
• Jet/ACE (Access's default) is a back end (tables).
• mySQL is a back end (tables).

Note: Access stores all the objects (Forms, reports, etc) in tables using JET/ACE.

So if you are asking about mySQL (or any SQL Server) you need to compare back end to back end.


To compare just a few major things between an SQL server and the JET/ACE (Access's database engine).

About JET/ACE :

• NOT true Client Server
• They support 255 concurrent users
• They have a 2 GIG file size limit.
• Security level is considered low (not very secured)

SQL Server: Including mySQL

• True Client Server
• Support more that 255 concurrent users
• Can support larger that 2 GIG files (depends on the version)
• Security level is considered High

There are lots of over thigs that should be considered, but there as the major differences.


Can JET/ACE database (normally called an Access database for ease) be used as a back end for a web site?
Yes. It is good for site that have very few concurrent users. Not very good for high traffic sites that have lots of users at the same time.

How do users work with an SQL Server?

You must build a front end. You can build a front end to an SQL server, like mySQL, with:

• PHP, Asp, etc. for browser based
• .NET
• Access
• VB
• Any programming language that support ODBC

How do developers work with an SQL Server?

There are also GUI tools available to work with SQL server that make it as easy as using Access.

Security important?

If data security is important, I recommend using an SQL server. With Access, I like to use an SQL server for added back end security.


Your Questions

Based on your questions, I think you are comparing mySQL server (a back end) to the Access front end where you design form, reports, etc. They are not the same thing. You can compare Access to .NET, PHP, ASP, ASP.NET, Delphi, etc.

I think the other people that you are referring to as comparing mySQL server to JET/ACE databse engine, not the Access front end (forms, reports, etc.

1. I can understand that maybe mysql is faster and can hold more information, but none of my current uses will ever need the speed and storage of 1,000,000+ records.

Click to expand...

I would have to disagree. Speed is ALWAYS ALWAYS IMPORTANT to users!

2. My other reason for not agreeing with the general population on this debate is for the general ease of designing an Access database. It has a very neat graphical interface where you can create queries with a few clicks, design forms graphically on screen to look whatever way you please, create reports, navigate so easily through records and have them returned to you in the graphical manor you like.

Click to expand...

This is like comparing Apples to Oranges. You are comparing a Front end to a back end.

Access is a front end. This is where you create the user interface forms, reports, etc. You still need a back end.

mySQL Server is a back end. You still need a front end for crating the user interface.

3. To my understanding with a mysql database, the user that wants to add, view, or manipulate data/records needs to be fluent in the SQL language and type out long complex "select" statements each time they want to retrieve data, and then on top of that, the data is returned in a spreadsheet type format, as opposed to a easily readable form as Access does.

Click to expand...

Again, this is like comparing Apples to Oranges. You are comparing a Front end to a back end.

4. How to access the mysql database seems more difficult than Access to me also. Again, to my understanding, the main way to connect/access data in a mysql database is through a web browser and the mysql database NEEDS some type of server to be hosted on, where Access is just a single file located on your computer or can easily be split into front and back end files for a home or small office network, and no need for a server.

Click to expand...

Again, this is like comparing Apples to Oranges. You are comparing a Front end to a back end.

5. I'm contradicting myself on this one a little here and may actually be answering my own question, but with Access, a database designer designs it so the user can easily use it, and I'm guessing it is the same with a mysql database, but with mysql, I don't understand what platform the user uses and also can mysql database records be viewed in "form" or "report" view as is MS Access?

Click to expand...

Again, this is like comparing Apples to Oranges. You are comparing a Front end to a back end.



Hope this helps

WOW, that was one hell of a response. Thanks!!! you cleared a lot of things up for me with that. I never thought/knew that Access was considered a front end system. Hopefully you are correct on hoping that other people were comparing myslq to jet/ace otherwise I would still be lost.

What brought up this question in the first place is that... I have a very simple website hosted by Yahoo. I want to create a form submission or possibly a online order system (again something VERY simple and not including a shoping cart or anything) that would submit user data to the mysql database that Yahoo also hosts, (or straight to my Access database if at all possible) and then connect my current Access database to that mysql database. The reason would be to reduce the need to manually re-enter data inside my current Access db when that website data is received. I posted a thread in a web developing forum on how to do this and instead of answers on how to do this, most of the responses I got were (in short) "Access is crap!!! Give up on it and re-create a php based system" That is why I had the questions on the differences.
But also based on your explination, for my question about my website, the mysql database would of been the back end and Access would of been the front end, so I still cant see why everybody is still against Access for this situation.

Aside from the crux of the issue which HiTechCoach has covered very thoroughly, one must always be careful about listening to comparisons between a Microsoft product and its competitors, particularly Open Source or freeware.

Some people (I call them M$ogynists) are so bigotted against Microsoft they live in a fantasy world where everything Microsoft makes is automatically considered inferior. Often they have never even used the Microsoft product in question and if they have, they start with such an attitude that they are incapable of making a fair and reasonable assessment. Of course there are fair criticisms as well but they are so diluted by the bigotry that it can be difficult to know what to believe.

They seize upon every criticism of a Microsoft product as gospel and extend the stories of vulnerabilities way beyond the scope of actual weaknesses. For example the known vulnerabilities in IE6 are being used to criticise IE8.

IE8 was widely criticised for crashing when first released but the problems were shown to be helper applications that also needed to be upgraded to IE8 compatible versions. But that didn't stop the criticism.

Meanwhile they pretend that vulnerabilities and bugs in non-Microsoft products simply don't exist. Lately I have seen posts on some other sites like "I know Firefox is a better, faster, more stable browser than IE but I am having problems with it." They seem to need to justify their choice of browser or wear it as some kind of badge of honour.

Mac users doubly so.

buratti said:

everybody is still against Access for this situation.

Click to expand...

Here's two good reading materials:

10+ reasons why IT Pro hate Access
Database Evolution: Microsoft Access within an Organization's Database Strategy

Most of time people's criticism against Access is exactly just that: not actually having any idea what Access actually is. Microsoft didn't really do it any favor by treating it as if it was both a front-end system and a back-end system. For example, ODBC administrator doesn't say "JET database file" but rather "Access database file" and there's more places where this is conflated and thus opaque to casual reader.


FWIW, I've implemented MySQL + Access and was very happy with it.

Galaxiom said:

Aside from the crux of the issue which HiTechCoach has covered very thoroughly, one must always be careful about listening to comparisons between a Microsoft product and its competitors, particularly Open Source or freeware.

Some people (I call them M$ogynists) are so bigotted against Microsoft they live in a fantasy world where everything Microsoft makes is automatically considered inferior. Often they have never even used the Microsoft product in question and if they have, they start with such an attitude that they are incapable of making a fair and reasonable assessment. Of course there are fair criticisms as well but they are so diluted by the bigotry that it can be difficult to know what to believe.

They seize upon every criticism of a Microsoft product as gospel and extend the stories of vulnerabilities way beyond the scope of actual weaknesses. For example the known vulnerabilities in IE6 are being used to criticise IE8.

IE8 was widely criticised for crashing when first released but the problems were shown to be helper applications that also needed to be upgraded to IE8 compatible versions. But that didn't stop the criticism.

Meanwhile they pretend that vulnerabilities and bugs in non-Microsoft products simply don't exist. Lately I have seen posts on some other sites like "I know Firefox is a better, faster, more stable browser than IE but I am having problems with it." They seem to need to justify their choice of browser or wear it as some kind of badge of honour.

Mac users doubly so.

Click to expand...

Very well spoken!!! I actually agreeded with this before posting, but never mentioned so.

Galaxiom said:

...

Click to expand...

Every group, even Microsoft or Apple, or <your favorite company> will have their fanbois and zealots. Such is life.

At the end of day what really matters is simply this: The best tool for the job.

Excellent discussion.

This is exactly my evolution "Most Access developers evolved from non-programming professions. They fell into Access, discovered the amazing productivity gains, learned VBA, and become more and more sophisticated." Actually, still in environmental/land-use planning profession.

In looking over the articles "10+ reasons why IT Pro hate Access" and "Database Evolution: Microsoft Access within an Organization's Database Strategy", a missing concept; some people resort to building their own Access application as they can't get the attention of the IT department for providing database support. Clearly this would not be the case for small businesses and governments that don't have a full fledged IT department.

Brilliant thread - answered so many questions for me this has. I've been banging my head against a wall trying to workout how to re-write my database to work from a browser instead of access forms !!

Now i understand the differences and why folk have been suggesting i 'bin' access in favour of asp or similar !! I actually feel stupid

Up to now i find access relatively easy to build and develop a database and have even learnt an awful lot about VBA to be able to really customise it to do quite powerful things.

Now i feel i can take the next step. But first i'm going to try MS Office Pro 2010 as it reckons it can create web based forms too - won't hurt to try a 60 day trial i guess.

Thanks ALL

I really love Access - I couldn't do my job without it.

Every environment has a skill and knowledge base such that like learning a language a good user in one environment is likely to be comparably more productive than a poor user in another.

As a result I would argue the skill and knowledge base of the proponent maybe more important than the environment that they use.

NASAs Appollo space missions were all based on extremely competent individuals using what is now considered outdated technology.

Doesn't make the accomplishment any less spectacular.

Sometimes the imporance of the accomplishment and the skill set of the users go hand in hand.

Let employees free to work how they see fit and you might just be surprised by what they achieve.

And I would add - Access is the only development environment that I have easy access to at work.
I suspect that is the same for a lot of people. What a brilliant market lead MS had on everyone.

Always makes me chuckle when I see Access naysayers thinking the way to solve the problem is to disallow development.

or when their suggestions involve technologies that the majority of users don't have access to, a point they completely overlook.

In the first case its shortsighted and the second its not a solution.

shenty said:

Now i feel i can take the next step. But first i'm going to try MS Office Pro 2010 as it reckons it can create web based forms too - won't hurt to try a 60 day trial i guess.

Click to expand...

You will also need to set up a Sharepoint Server if you don't already have one. This is required because an Access 2010 web database only will publish to a Sharepoint Server.You Access tables get imported into Sharepoint as Lists.

I have to say, security issues aside, that it is clear that very successful and robust databases can be built with an access back end.

i am pretty sure that much of the time, SQL servers are used as straight replacements for access anyway, without necessarily taking advantage of SQL server facilities, which require some process re-engineering to implement anyway.

gemma-the-husky said:

i am pretty sure that much of the time, SQL servers are used as straight replacements for access anyway, without necessarily taking advantage of SQL server facilities, which require some process re-engineering to implement anyway.

Click to expand...

That is probably true for people using mySQL or MS SQL Server Express.

I have been using MS Access since the 90's and keep quite a bit of my data on it. Have not come accross any failures since moving to Access 2007. Its easy to backup since all reside on one file. If you just make a daily copy there you have it (even in your hosted web projects).

Currently I use it in .net web site projects. It is easy to do so because:
1) Its a single file you need to include in your project so nothing has to be installed on Hosting Side
2) If you have your own server, you don't even need to install Office. MDB files are recognised by aspx pages on IIS (not sure how the infrasturcture is with Jet engine and asp.net)
3) Debuging and editing is easy since you can edit using front end (on a development pc with office) and view web pages .aspx using the back end.

For web projects, I am not concerned about the speed because for sure the bottleneck is going to be at bandwith or .net page/project compilation. What worries me is the 255 concurrent user issue. If only if this was extended, Access would be perfect for web projects.

I don't understand the back end architecture with web projects. Is jet engine used? It runs on IIS on a server without office installation, so does this mean jet engine comes with Windows or .net Framework?

Its not possible to use .accdb extension (Access2007) but only .mdb in AccessDatasource adapter (asp.net object). Does this mean we get to use old jet engine?

Will Jet engine ever be updated to support unlimited users for the web?
Too many questions sorry. Any help/comment is appreciated.

I would also mention that whilst Access 2010 can be web based on a Sharepoint Server the samething can be acheived even with Access 1997 on a Terminal Server. So to present your solution so that it is web enabled, I may sound a bit of a luddite, but there is no massive re-engineering to the Front End Access solutiion using a Terminal Server. The performance is not so much of an issue as the Terminal Server either holds the Back-End Jet / Ace tables or looks at a local file server.

As for Access on the web I manged 10,000,000 hits and 3,000,000 pages using an Access 1997 Jet database. The issue these days is that ISP's stack so many web addresses on a one server that the upside of the bandwidth becomes squeezed so there can consideration performance degradation, including timing out.

To many companies it does not really matter what language or product a solution is written in, all that matters is functionality. Yes it would be nice to have a web based solution but whether ot not you go down the asp, php xhtml route is still is major undertaking. VBA I believe is not supported on Sharepoint.

One last consideration if you want a web based solution you need to think beyond just a database - what about all the documents, files, images and emails. Wtih a Terminal Server you can consolidate all the information the users need into one all embracing solution rather than looking at the problem in isolation.

In the end it is not the Back End tables that are so time consuming but it creating the forms and reports and that is where Access is very good. Yes, there are things that annoy us about any product but taking things in the round Access Forms and Reporting are very well established and robust, give or take our miscoding adventures.

So look at all your options and see what best suits your environment.

Simon

5senses said:

I have been using MS Access since the 90's and keep quite a bit of my data on it. Have not come accross any failures since moving to Access 2007. Its easy to backup since all reside on one file. If you just make a daily copy there you have it (even in your hosted web projects).

Currently I use it in .net web site projects. It is easy to do so because:
1) Its a single file you need to include in your project so nothing has to be installed on Hosting Side
2) If you have your own server, you don't even need to install Office. MDB files are recognised by aspx pages on IIS (not sure how the infrasturcture is with Jet engine and asp.net)
3) Debuging and editing is easy since you can edit using front end (on a development pc with office) and view web pages .aspx using the back end.

For web projects, I am not concerned about the speed because for sure the bottleneck is going to be at bandwith or .net page/project compilation. What worries me is the 255 concurrent user issue. If only if this was extended, Access would be perfect for web projects.

I don't understand the back end architecture with web projects. Is jet engine used? It runs on IIS on a server without office installation, so does this mean jet engine comes with Windows or .net Framework?

Its not possible to use .accdb extension (Access2007) but only .mdb in AccessDatasource adapter (asp.net object). Does this mean we get to use old jet engine?

Will Jet engine ever be updated to support unlimited users for the web?
Too many questions sorry. Any help/comment is appreciated.

Click to expand...

As someone has already mentioned in this thread - it is not Access vs MySQL, it is Jet or ACE vs MySQL. If you want to compare Access to something, compare it to VB or Visual Studio. Access is a development environment. Jet and ACE are database engines.

When you use an "Access" database as a back end to a web application, you are using Jet or ACE depending on whether you are using an .mdb or .accdb. Microsoft Access is probably not even installed on the server nor does it need to be. Jet used to be installed with Windows because so many non-Access applications used it. I'm not sure if it still is or if ACE is installed instead.

Jet/ACE are designed to be desktop databases. They support a limited number of concurrent connections - officially 255 but in reality closer to 25. They will never be expanded to support more connections since there is no need. They fill a niche and they fill it well. A local Jet/ACE database containing many thousands of records will often be faster than the same database in SQL Server. BUT, and this is a big but, Jet/ACE don't have the security or robustness of a real relational database and so should not be used for anything other than a small workgroup application or a stand alone app that runs on a single PC. Access on the other hand is infinitely scaleable since it connects to any RDBMS that supports ODBC connections so if you have a thousand seat licenses for SQL Server, you can have a thousand concurrent users for your Access application provided of course that each user has his own private copy of the FE. Never, ever share a copy of the FE. Only the BE is shared because that's where the data is. The FE contains only forms/reports/code/macros but no data.

5senses said:

I have been using MS Access since the 90's and keep quite a bit of my data on it. Have not come accross any failures since moving to Access 2007. Its easy to backup since all reside on one file. If you just make a daily copy there you have it (even in your hosted web projects).

Currently I use it in .net web site projects. It is easy to do so because:
1) Its a single file you need to include in your project so nothing has to be installed on Hosting Side
2) If you have your own server, you don't even need to install Office. MDB files are recognised by aspx pages on IIS (not sure how the infrasturcture is with Jet engine and asp.net)
3) Debuging and editing is easy since you can edit using front end (on a development pc with office) and view web pages .aspx using the back end.

For web projects, I am not concerned about the speed because for sure the bottleneck is going to be at bandwith or .net page/project compilation. What worries me is the 255 concurrent user issue. If only if this was extended, Access would be perfect for web projects.

I don't understand the back end architecture with web projects. Is jet engine used? It runs on IIS on a server without office installation, so does this mean jet engine comes with Windows or .net Framework?

Its not possible to use .accdb extension (Access2007) but only .mdb in AccessDatasource adapter (asp.net object). Does this mean we get to use old jet engine?

Will Jet engine ever be updated to support unlimited users for the web?
Too many questions sorry. Any help/comment is appreciated.

Click to expand...

Check out my article: Did you know that Access is NOT a database but a Front End?


Starting with Windows 2000, JET databases are actually used by the Windows OS. This means that JET comes installed with Windows. That is why you can use JET databases (.mdb) without installing Access on a Web Server.

5senses said:

Its not possible to use .accdb extension (Access2007) but only .mdb in AccessDatasource adapter (asp.net object). Does this mean we get to use old jet engine?

Click to expand...

It actually is possible. You can use the new ACE (.accdb) database on a machine by installing the Microsoft Access Database Engine 2010 Redistributable package. See the end of my article above for a link to the download.

5senses said:

Will Jet engine ever be updated to support unlimited users for the web?

Click to expand...

I doubt it. If Microsoft did it would just become another SQL Server. Why would Microsoft re-invent the wheel when they already have an SQL Server.

It has been my experience that If you have a site that regularly has 200+ concurrent users them you should be using Store procedure and Views which means you need to migrate to an MS SQL Server.

IMHO, Microsoft has given us a great development/migration path:
1) JET/ACE (2 Gig size limit on single database)
2) SQL Server Express ( 2008 R2 has a 10 Gig size limit on a single database)
3) SQL Serer Full version.

The one thing you have to remember is that when using a database on the web you open the connection and close the connection almost immediately. Therefore the number of concurrent users is determined by the read of the data simultaneously before the connection to the database is dropped.

Yes there are benefits to a full blown version of SQL server for large scale web applications and if your web solution is on the scale up-scale to SQL Server.

Even with 3,000,000 pages read p.a. this only represents about 6 pages a minute so you need to quantify the demand that is being placed upon your website.

Simon

Simon makes a good point. If you are not keeping any connections open this does help keep down the he number of concurrent users at any moment.