Ms Access vs MySQL??

-Hi... My current vb application is using MS Access to store all the customer information and billing information and now the system is getting very laggy.... As there are about 50k of information and multiple query will being done at 1 time... I was thinking of changing the database to MySQL... Based on the current application, each query done by users would return about 2-4k of records....

Does changing to MySQL help to improve this?? I would appreciate if there are links to support the answer.... Thx....There are some very good reasons why you wouldn't use Access database in some cases. MS Access database is more suitable for desktop use with a small number of users accessing it simultaneously. One reason you might choose to use Microsoft Access over SQL Server is for compatibility/sharing. You might need to email someone a copy of your database. People are more likely to have Access on their desktop computer than SQL Server. You'll generally only find SQL Server on developers' computers/servers or on production server machines. Another reason you might use MS Access instead of SQL Server is money. You might already have installed Microsoft Access as part of the Microsoft Office suite. Purchasing SQL Server would be an extra expense that may not be necessary - depending on your situation. SQL Server can also be quite expensive. To add further, Access is used as local database & can't be accessed through other servers or rather from any remote location. In short it has a front-end GUI system [MS-Office] to design applications quickly but locally. You can also design/modify the Access database on your local machine & upload it to the web server where the web site hosting exists, but this becomes a tiresome job when the DB size increases gradually.

When it comes to security, MS Access is limited to security in terms of user-name/password on the database.



MySQL is the most famous or rather more loved amongst the professional folks because of it's multiple choice in custom storage engines. You can have custom storage engines developed in MySQL based on your requirements or choose amongst the inbuilt ones.From a database developer's point-of-view, the choice is very clear. MSSQL Server or MySQL Database Server Hosting are the most sensible choices because of its rich features in manipulating, securing and managing data.
Are you going to host a MySQL DB or get someone to host it for you? As your looking at two different beasts.



Access is an all in one database. MySQL is just the database. Or to put it it in very layman terms the table only version of Access.(yes I am aware that the analogy ends there and there is a 1000's other differences).



Unless your goig to use MySQL to host the data for the Access front end (Linked tables) using ODBC Which will improve nothing. Infact it will slow it down. I would rethink your stratagy a bit.
There is no comparison between mysql as a real commercial heavy duty database, and Access which is typically a local use, giveaway in Microsoft Office. It is no good for remote access, its' connection system is not solid. It is generally useless for multiple user purposes.And you are far better using a web based interface for mysql, VB is also limited in functions and power. Consider using one of the free distributions of the Apache Web server which carries php as the scripting language, and the mysql database pre-configured.
Ahhhh! Don't use Access, use MySQL. :0



Performance will be waaaaay better!