access vs MySQL

[jburatti]

Hi, I just had some questions about the differences between MS Access and MySql. I am fairly new to MySQL, but have been using Access for a long time now with various uses. After starting to learn SQL (as a matter of fact I never even heard of it till about a year ago) I hear from many different sources that it is SO much better than Access. I am not saying that everyone is wrong, but I myself just cant see how that is possible, at least with they ways I've used Access. With Access I have a user interface where I can view all tables and create queries with only a few clicks of the mouse, but with MySQL it seems that to do the same I have to type in (sometimes long and confusing) lines of code each and every time. Also with Access I can create custom forms for adding records, order placing, or easy organized viewing of records, and with MySQL all the data returned are in table or spreadsheet format only.

I've used Access for the following: Mail Merge, Employee "Time Sheet", Maintenance logs, Order Forms, etc.

So if everyone is saying MySQL is better, I believe them, but can someone explain in better detail on HOW it is, especially with the ways I use Access?

[wspeeckaert]

I often use Access as the front and MySql as the backend. I just use the Mysql ODBC connector. In that way I can have a MySql database on a web server and also access is via Access.

[jburatti]

Thats funny! I just discovered that MySql ODBC driver and was playing around with it last night. Does that connect "live" to the MySQL database? When I was palying around with it, I added a few tables and changed some data inside Access, but when I logged on to MyPHPAdmin and viewed the same database, those changes I made in Access were not updated. I also noticed that after connecting via MYSQL ODBC, I had a new database file on my computer as if I was just copying my MySQL database from my server to my computer and working on it locally, hence not connecting "live" like stated earlier.

Also, with MySQL ODBC driver, if I create graphical forms or reports, as Access lets you do, how does that work with the MySQL data? Or am I working with a new file/import every time I open it?

[wspeeckaert]

I should have been clearer. In Access you have go to File, get External Data, Link Tables and then select ODBC databases. Now select the tables you want to use.

You can't modify the tables using access, but you can edit/delete data.

Go to the MySql web site and search for the ODBC connector. There are a few options you have to check for the connector to work properly with Access.

[brunoais]

I think you missunderstood what is mysql.
Mysql is just a server program that interprets and executes commands.
Ms access is a program that dows everything that mysql does and more. It's not only a database interpreter and executer. It's a GUI program. Were the user has interactivity with the program. Ms access generates the code for you as you give the orders, the rest is exacly like Mysql. In both programes all the coding can be translated to sql. But Mysql saves all files as xml and Access saves in an other format (I don't know which one)
If you want to you may create some code with access and then send to Mysql.

[wspeeckaert]

I understand MySql and ms Access very well. I program using both.
I use MySql as the backend on Access as the frontend.

[brunoais]

I was refering to the author

[wspeeckaert]

That's what happens when you reply too early in the morning.;)

[JavaServlet]

It comes down to what are your requirements and performance. MySQL is better for mult user applications. MySQL can be deployed across various platforms and also allows multiple concurrent users. I only use robust databases such as MySQL or Oracle for all my JSP/Servlet projects. Access is supposed to be used as a single user program for local use, although it has some sharing features, your data cannot be accessed concurrently in Access. Also, if your requirements call for a large database then MySQL would be your choice because it can manage alot of data. If you need a small database and you are happy with your MS Access performance and the quick easy tools for creating reports etc then you made the right choice for your requirements. MySQL also has many tools out there that are also quick and easy to use.