Using Microsoft Access as an interface to a MySQL database?

Question

My Microsoft Access database (FE / BE) has grown due to limitations, and I need to import more data, so Access is no longer suitable for me. I will switch to MySQL (NOT SQL Server) as a good alternative, but I am wondering what to use as the interface for the MySQL database. Should I store VBA passcode and frontend (using ODBC connection) or ...? High performance is not required.

Answer 1

If you have already invested a lot in developing your interface, it’s not easy for me to keep the front of the access.

On the other hand, if you do not like to develop in Access and you have a relatively trivial application, and you have a favorite development platform with which you can easily get up and work with it in a different direction.

In addition, if you have a change in requirements, such as the need to deploy on the Internet or for remote users, this may be a good reason for not making an investment in Front Access.

But overall, the first choice is to stick to what is already working, and then move on only if there are insoluble problems with it.

Answer 2

I use access all the time as my frontend. It works simple and simple. You can use the tools in Access to grow tables to SQL. After that, I will rename the linked tables to take out the dbo_ prefix, so the forms and code still look at the same table name.

You now have sql service and data backup. You can make different copies of the front end. You may have a copy of dev and one for users. After you change the appearance - new forms or reports, you can publish it after 2 seconds, when there will be no one in the published copy.

I wanted to learn asp or switch to other platforms for my db's front gui, but why bother when I don't need a bomb for ant hill? These are small applications, and Access does a good job with them.

NTN!

Answer 3

I think you should at least first try a copy of the existing Access FE with ODBC links to the new MySQL database. I have done similar with PostGreSql and SQL Server as a database database and it can work well. I don’t know if you may encounter a lot of errors or even robbers with MySQL, but it seems worth a try. And it won't take you a lot of effort to find out ... just undo or rename existing Jet tables and add ODBC links in their place.