Should I use my existing Access database or create a new mySQL database?

Hi All,

I am developing a web application based on a desktop application that I created in Microsoft Access. Initially I had planned to create a new database in mySQL and connect my web app to that but I recently found that I can connect to my Access db using ODBC and build the web app on that.

My dilemma is whether that is the best approach. I would like to build it using Dreamweaver in PHP and JavaScript because I know that VB script is only compatible with Internet Explorer. I tried connecting to my Access db in Dreamweaver and it was very difficult. I was only able to do it using an ASP page and Dreamweaver only supports mySQL connections with PHP. I am fairly new to web developing and I want to ensure that I make this as flexible as possible and not be tethered to Microsoft but the idea of keeping my data and db structure is very appealing. I don't have a problem with recreating it in mySQL if that is the best approach.

I was hoping I could get some guidance from the experts.

Thanks in advance for your support.

Ken

I don't think that Access is a good approach at all. From my personal experience, when data reaches a certain size, the mdb file will start to crash or to generate problems.

Brief, when the data size gets big the mdb file won't be reliable.

I would highly suggest that you re-create your data and data model using mySQL. It is very strong and reliable and in the future if it needs it can be distributed.

Hope I answered your question.

Another consideration is that Access only allows one person to connect at a time so as soon as your site visitor count gets as high as 2 at the same time you have exceeded the capacity of Access.

Thanks for your thoughts guys. I didn't realize the size and user limits. I know that my desktop app supports multiple users but I never tried using the database on a web app. It seems that mySQL is easily the better way.

Thanks,

Ken

Most welcome.

Pay attention as well to the synchronization issue mentioned by felgall. It is a very important matter.

Just to make you aware, you can export your access database into a mysql database very easily. Didn't want you to think you'd have to start from scratch.

Teed,

This is very exciting! Can you point me in the right direction as this would be ideal? I knew there were ways to do this but all of the ones I found involved very complicated processes.

Best regards,
Ken

There are several ways to do that, but I guess the easiest is open a table in access, then on your database tools tab select export "Text File" which will walk you through saving the table as a CSV(Comma Separated Value) file. The wizard should walk you through that. I usually save them to the desktop so I can easily find them when its time to import them into MySQL database.

Have you installed MySQL yet? If not do the steps above for all your tables first.