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 bookis 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.