MS Access vs. MySQL

[dina]

Hey there everyone!

I'm just about to start a database assignment for uni but don't know whether to use MS Access or MySQL. I am an adequate use of both but does anyone know the advantages and disadvantages of them???

Thanks ;)

[dina]

Also would you call MS Access and MySQL an application or a software

[liliafan]

Also would you call MS Access and MySQL an application or a software

What do you mean by application or software, all applications are software.

Access isn't a database, it is a frontend for an external database server, and it deals with database type files, it isn't a true database though, mysql on the otherhand is a true database, so if you are planning on doing anything with any level of complexity I recommend mysql.

[Paladine]

True....sort of. Depends on what you classify a Database to be? Is not a DB a collection of relational data?

Hence SQL, MySQL, SQL Anywhere, etc are just flavors of the SQL DB Type, while Access is just a flavor of the OleDB Type? Kind of like Excel is not a Database per say, but it can store data. ... And what about Oracle? Hmmm..

It is really just a view point. Haha. But then I know the IT Experts would argue based on DB theory. So liliafan would be right is what was said.

:-)

[fEcAlMaTteR]

Use MySQL
:twisted:

[dazzlindonna]

MySQL. If you know both, then it is the better choice. Access will only hold up with a few users. Get too many, or the database gets too large, and you'll be dealing with crashes a lot. Lots of other reasons too, but that's the simplest.

[samaru]

Access isn't a database

An Access database is a database. Anything that's used to store information in a structural way is a database. Whether it's flat (stored in a file), hierarchical, network, object, or the most, popular relational, it's a database; they're all forms of storing data.

What's better? Well, Access is designed to function as a desktop application for a single user to store information. It's possible, however, to have multiple connections to it, but I wouldn't recommend it. The more connections you have to it, the more it slows it down (more severe than MySQL), and the higher the chances of data corruption (you could have multiple people modify a field in the table at the same time, and information may be overwritten; so you'd have to have some sort of locking mechanism to prevent this). I only recommend Access for personal use because it provides tools for reporting and easy data retrieval and insertion.

If you're going to create an application where multiple users have to retrieve data, I suggest you go with MySQL. Because it's a database server, it's ready to deal with multiple users. It handles locking and data corruption. It's fast too. It has security features, among other things. Also, it's free for non-corporate use, unlike Access.

[jackster]

what version of MySQL is it

if you need to create views.. then use access.. as if u have mysql of less than veriosn 5 it does not support them.

if you have a lot of CONCURRENT users, use mySQL

jack
www.ansariltd.com

[mnemtsas]

Access is only really good for up to 30 concurrent connections (according to MS). I can verify this as all my software has an optional MS Access backend and we have used 30 concurrent connection systems many times with no problems whatsoever.

MySQL is better for more concurrent connections and provides much better possibilities for future growth.

They are both databases, by the way. Regardless of what the MS bashers think.

[liliafan]

An Access database is a database. Anything that's used to store information in a structural way is a database. Whether it's flat (stored in a file), hierarchical, network, object, or the most, popular relational, it's a database; they're all forms of storing data.

By this definition a telephone book is a database since it stores information in a structural way.

Okay to correct myself earlier yes Access is a database, however, it doesn't compare on any level to more sophisticated databases, this is based on my experiences, I have used dozens of database systems everything from GDBM, to BerkleyDB, from Mysql to Postgres, from MSsql Server to Sybase and Oracle. Access has a pathetic method of locking (in the versions that support locking) so whilst it may support concurrent connections it may not be protecting the data. Database repairs often cause even more problems.

Also I wouldn't define myself as a MS basher admittedly I prefer unix like systems I also believe MS has a place and they fill that place very well, I do, however, have a particular dislike of Access this is based on my bad experiences with it, not on my personal feelings towards microsoft, if you want a database to store your personal records or your cd collection Access fills that space well, if you plan on having more than one person accessing it at a time then Access seriously lacks in certain important errors.

[mnemtsas]

By this definition a telephone book is a database since it stores information in a structural way.

Okay to correct myself earlier yes Access is a database, however, it doesn't compare on any level to more sophisticated databases, this is based on my experiences, I have used dozens of database systems everything from GDBM, to BerkleyDB, from Mysql to Postgres, from MSsql Server to Sybase and Oracle. Access has a pathetic method of locking (in the versions that support locking) so whilst it may support concurrent connections it may not be protecting the data. Database repairs often cause even more problems.

Also I wouldn't define myself as a MS basher admittedly I prefer unix like systems I also believe MS has a place and they fill that place very well, I do, however, have a particular dislike of Access this is based on my bad experiences with it, not on my personal feelings towards microsoft, if you want a database to store your personal records or your cd collection Access fills that space well, if you plan on having more than one person accessing it at a time then Access seriously lacks in certain important errors.

Facts not opinions please. I've been selling and writing multi user business software with Access backends for a while. I have never, ever, ever, ever had a data corruption issue due to poor locking. It's because I use it for what it's intended to be used for. And just because it doesn't have the same features of more advanced systems doesn't make it any less good at what it does.

Here's an analogy for you. Just because a $100000 500 series Merc could carry a trunk load of bricks doesn't mean you buy one to do that. You use something that's fit for purpose.

I'm no MS apologist by the way. I've used *nix and MySQL on many jobs, but I;m always careful to use the best tools forthe job.

None of this helps the original poster. Perhaps they could post some more information and we could make a more informed recommendation.

[liliafan]

Facts not opinions please. I've been selling and writing multi user business software with Access backends for a while. I have never, ever, ever, ever had a data corruption issue due to poor locking. It's because I use it for what it's intended to be used for. And just because it doesn't have the same features of more advanced systems doesn't make it any less good at what it does.

Actually my opinion is based on facts, if you had more carefully read my post you would have seen I have a lot of experience using all kinds of databases, this experience extends about 7 years now.

One of my more recent projects (in the past year) was to rebuild an application that had an Access backend it was written to make use of Access forms on the frontend it was using Access 2000 and never had more than 4 people connected at any given time, it was written by an "expert" in MSsql and Access, it was used for about 2 years often causing data corruption to itself when it was used, in the end just before I rebuilt the application to use MSsql and a visual basic front end (which you will notice are both Microsoft titles) it causes serious data corruption everytime it was used and this was a very critical application used to keep stock records for a middleweight ecommerce company. On reviewing the code I found it to be fine, and upon talking with several freelance consultants it was decided that Access wasn't designed to be used so often or as hard as it was (4 concurrent connections) we later found and isolated the corruption to be caused when 2 people made writes to the database at the same time, this caused a corruption in the recordset.

Other experiences include writing backends for coldfusion intranet applications coldfusion supports access and access supports coldfusion but still we would find that for some reason access would have about a 14% failure rate even under very slight load, by comparision when we rewrote these applications to use MSsql we had less than 1% failure rate on transactions. Please note that all these applications would have less that 30 connections at any given time.

So as you can see my opinion is based on fact, I have a lot of experience using Access and through my personal experience I have found it to have an unacceptable failure rate on multiuser transactions, it isn't knocking MS because I will recommend other MS products, and when I have fixed most problems involving Access I have recommended going to MSsql.

If you haven't experienced these problems with Access then I am very happy for you, I hope you never do because if/when you do you will find it very frustrating to have to fix and avoid in the future. In future I would also recommend you avoid making comments in an effort to question other peoples knowledge/experience just because you have never experienced a problem before doesn't mean it can't and doesn't happen with frequency.

Best regards

Ben

[mnemtsas]

Good answer :) *snip*

[samaru]

By this definition a telephone book is a database since it stores information in a structural way.

Yes, a telephone book is a database. :cool:

[Phaelax]

I know sql is more for server-client databases, and I think access is for personal databases. So it would depend on what you need your database to do and how it is interacted with.

[fsn812]

I have worked on large scale projects using Oracle, medium sized projects using MySQL, and some small projects using Access. All have worked great for the most part. However, I have seen problems with Access and I am not impressed overall. I'm sure Access is great for small single user environments, but I wouldn't trust it with the amount of data our DBA's and Admin's handle.

Of course, we use Oracle for our largest projects. Don't get me wrong, it's great that MySQL has transaction support (finally) and should have trigger support (probably in 5.1), but for large projects I am not convinced MySQL has the scalability yet.

For desktop or a small-medium server? MySQL or PostgreSQL all the way. MySQL has come along way since previous 3.x.x. Unless you can afford a high end scalable database program, or unless you need it, I would suggest going with MySQL and NOT Access.

[kc0arf]

Hello,

I like the cross-platform of MySQL too! While Access might be easier to initially program, I like the ability to use MySQL with Linux and Macintosh too.

Christian

[autocrat]

Hell, I'm no genius or expert.......
but it really does depend on several key factors;

application / usage
---- light load / heavy load
---- entering data / altering data/ processing data
connections / users
---- light / moderate / heavy
---- short periods / long periods
time frame
--- needs to be built yesterday / a couple of days / ages to go

I think these are the three main keys to making your decision!
If you only need a light horse to run quick and simple courses with only a few riders, then go with Access... it's simple enough to use, does the general things in life quite well, and generates a front end with the greatest of ease.
If you want something that can shift earth whilst running, entertain a large audience and don't have to beat a clock, then go wiuth myssql, (or just about anything else!).

Oh, and to chip in on the anti-access..... I've never, ever had a problem! I think it has something to do with they way they are built! I've "seen" MSADB's that fall over every tuesday at 09:00 every week for a ever, then I've used others that have crashed only due to stupidity! If it's used for the general tasks it was made for ,(note:- not marketed for - MS "claimed" so much, and it fails those goals!), then it's fine.... start trying to get to clever with it, and it has a fit!
It's a good little lightweight data sorter and cruncher, and easy to use so long as you ignore the helpfiule (:giggle:)..... so don't bully it... just because it's not manly enough to serve 35+ simultaneous connections all altering similar records and viewing the same data tree.... I wouldn't cope either!

[RussGreen]

I've been reading this thread with interest and have a couple of questions.

When you're all talking about using Access and the problems it has, do you mean Access.exe and using that to enter/delete/update data in your MDB files? Or is the problem with the MDB file itself, so if used with a VB front end the problems with "Access" still exist?

If it's the latter can someone just clarify, is the problem solely with MDB or is it with OLEDB?

Are there better OLEDB database file formats to use other than MDB?

The thing I like about MDB verses MySQL or MSSQL is that it is easily distributed with software and doesn't rely on users installing and running database servers with all the complexities that come with that.

I know sql is more for server-client databases, and I think access is for personal databases. So it would depend on what you need your database to do and how it is interacted with.

MS obviously don't think this are else the MDB file wouldn't support multiple users and Access itelf wouldn't workgroup security features would it?

Russ

[system]

MS Access and MDB files are good to keep data on local machine: application settings, user settings, dictionary etc - all data that you don't want to download from server.

But in this case process of configuration and distributing process is not easy.

We developed a small tool that helps to synchronize databases in different environments: www.databasecomare.com

OLEDB (ADO.NET) drivers definitely are well optimized to work with any kind of MS database and you can download OLEDB driver for MySQL as well.

[jwshepherd]

Hey there everyone!

I'm just about to start a database assignment for uni but don't know whether to use MS Access or MySQL. I am an adequate use of both but does anyone know the advantages and disadvantages of them???

Thanks ;)

I usually do my initial layout, design and refine my database with MS Access, I can carry it with me on my laptop and pocket pc.

I have a few programs that use access as a backend for over 200 users ( not concurrent ) and as a backend db for a few small sites I host.

Depending on the number of records, the number of concurrent users, the length this will be in use, and the OS it will run on, would be your determining factors.

access will be ok with about 20 concurrent users
access will only manage a db of about 2gb
access would be great for small short term projects
access will only work in a windows environment

anything parameters greater than those should be considered reason enough to switch to Mysql

[Sydney_H20]

MS Access is not such a bad application. The only draw back to using MS Access is its lack of scalability. With MYSQL you have more control. However Scalability might not always be a good thing. It really depends on your project. Recently I had work on a project which records the number of time employees logout to go on a meal break. The existing phone system had collected all the data of each employees log in and out time. However the data collected was to complex for a call center manager or supervisor to analysis. We had to create an application which would draw the data from the phone system and analysis it into graphs and charts for the call center management team. We had a choice or using either a MS ACCESS back end or MYSQL. We choose MS ACCESS because or the following reason
1. The rich odbc liberary
2. Easy Reports and Forms creation

We tested the project on both databases and found that MS ACCESS was by far the easier tool to deploy and use.

However without being bias MYSQL does have its advantage as well. At the end of the day it comes down to your projects objectives and complexity.

[Kegtapper]

Hey there everyone!

I'm just about to start a database assignment for uni but don't know whether to use MS Access or MySQL. I am an adequate use of both but does anyone know the advantages and disadvantages of them???

Thanks ;)

ummm people are getting tied into the semantics of the word DATABASE. Access is a program that can create databases. It can create true Relational databases. But that is left up to the developer whether they choose Flat or Relational.
dBase4 is FLAT only
edb (Exchange db) is FLAT only.

For a corporation or university being the case-
I would recommend MS ACCESS 2003. There is a huge knowlege and support base behind the product. MySQL in my opinion is for folks that don't want to spend money to do what it takes. Most of the Access developers I know went to classes for training, and MySQL developers were usually self taught.

Some of the problems arise when the programmer has gone on, to another position and code was not annotated well. The university can find hundreds of candidates for MS ACCESS, but is that the same for MySQL?

Now when the project gets huge and gets out of hand, migration to MS SQL server is seamless. Smooth transition path. Seen it, Done it. Plus MS ACCESS has VBA behind it to create complete VBA applications, without having to buy additional tools. However you can get VB6 if more strength is needed. Where the application client can be distributed over a network.

Another consideration is the server it will reside on. Is it a NT/SQL/Windows 2000 server? Or is it Apache/Unix?

Again, there are a lot of considerations that need to be taken before choosing either.

[Kegtapper]

True....sort of. Depends on what you classify a Database to be? Is not a DB a collection of relational data?

Hence SQL, MySQL, SQL Anywhere, etc are just flavors of the SQL DB Type, while Access is just a flavor of the OleDB Type? Kind of like Excel is not a Database per say, but it can store data. ... And what about Oracle? Hmmm..

It is really just a view point. Haha. But then I know the IT Experts would argue based on DB theory. So liliafan would be right is what was said.

:-)

Nope
one reason, the data don't have to be relational.
Excel can hold data , but it is not a database, The Data is.
You can keep a database in a word table, but performing queries to access the data is another topic.

To use Excel as a database is like using a butterknife for a screwdriver. Sure it works but ...

SQL is a language: Structured Query Language. This means that most of the commands that you use in Access SQL, or SQL 7, SQL 2000, mySQL, or Oracle to retrieve data from a DATABASE will generally adhere to ANSI standards from one platform to another.

UPDATE is UPDATE
SELECT is SELECT
DELETE is DELETE

Kegtapper

Preferences should not be confused with best. Nor Skills be a limitation.

[liliafan]

Some of the problems arise when the programmer has gone on, to another position and code was not annotated well. The university can find hundreds of candidates for MS ACCESS, but is that the same for MySQL?

Of course it is, there is always people available to fix any problem with any system, especially now that MySQL has become such a major competitor in the database market.

Now when the project gets huge and gets out of hand, migration to MS SQL server is seamless. Smooth transition path. Seen it, Done it. Plus MS ACCESS has VBA behind it to create complete VBA applications, without having to buy additional tools. However you can get VB6 if more strength is needed. Where the application client can be distributed over a network.

But when a project gets huge with MySQL you can scale the project quite well, especially with the more recent versions which support distributed transaction. MS Access may have VBA behind it and you can pay extra for VB6 but that is a pretty weak arguement, MySQL is opensource it has libraries to be supported by just about every programming language there is, free and none free, you can use PHP, Perl, Python, Ruby, C/C++. Java, Lisp, the list goes on and on, plus most of these are cross platform, and just about every language I mentioned will out perform VB6 in everyway.

SELECT is SELECT indeed but lets face it MS Access only supports a partial subset of SQL in the first place, trying throwing a few subselect and left joins in and notice the difference.

[lizrob]

awesome thread.

question -

is Access easy to learn if you know MySQL?

Also.. is PHP used to call to an Access database? I know it is supposed to be for desktop databases or for small web apps only.. (that is what I got from this thread.. correct?) but, how do you call to the access database?

[Kegtapper]

Typically PHP displays MySQL/DB2 formatted database not MDB. I don't use PHP but I suppose it could.

It's user base has grown so large and additions through open source has made it an option for those wanting to learn or use it on a budget. But being in the industry starting in the DB2 days and AS400 servers it is a similar argument as APPLE/PC and Windows/Linux. I feel the best tool for the job is:
the one that gets done
the one that can be changed or modified easily
within my budget or my clients
the one where support is readily available
the one that is tried and tested

Here is a statement from a program I wrote in VB6 using an Access DB backend
strSQL = "SELECT Label.Label, DiscographyIndex.LabelNo, DiscographyIndex.Artist, DiscographyIndex.Title, DiscographyIndex.SideB, DiscographyIndex.ReleaseDate " & _
"FROM Label INNER JOIN DiscographyIndex ON Label.LabelID = DiscographyIndex.LabelID " & _
"Where (((DiscographyIndex.Title) <> '***** Not Issued *****')) and ((Label.Label)<> 'Yesteryear') and ((DiscographyIndex.LP)=False)" & _
"ORDER BY DiscographyIndex.Title;"

It all boils down to an individuals understanding of what limitations are where, system cross platform usability and the end ability to PRODUCE desired results. I am one individual that do not put down one DB or OS (or religion, or language) simply because I cannot use it or understand it. And build up the ones that I can.

The above SQL arrived after several years and the project had grown to the point where previous development tools couldn't do the job. Paradox, SimplyDB, and others and controls. The end result was a commercial application, not a Net App.

Often a developer will get more tools, and never learn the core language. In the list below, I only see C++ outperforming VB6 for my needs. The others are only tools or webbased and I develop EXE programs not applications or web only. Though I still can port my MDB over to ASP, and make it a web app.

But thats what I use.

[morphosin]

Hi all,

Read thru this thread and was compelled to join the forum... Thanks all for your precious advice...

The reason I was reading this thread is I am about to start developing a database for an application for my company. Just to give all you gurus a briefing, the front end will eventually be an online application (which I am not gonna develop). My job is to develop a database and give it a VB frontend just as a proof of concept for the product. That being said, I would request any one of you to recommend whether Access suffices this purpose better or Mysql? From what I have come to understand, Mysql should clearly be a better option for this purpose but none the less I though I would take a confirmation from an expert.

Lastly, I have one (rather stupid) question... To the best of my knowledge, Mysql 5.0 comes in two packages (community server and enterprise server). Could you please shine some light on the difference between these and which would be a better option for me. Also I'd be obliged if you could point me the right direction to download the recommended version.

Thanks!!

mOrph

Thank you