I have setup a MySQL database on a hosted Windows server (paid offsite service). What I would like to do now is create a local front end for my users in MS Access so they can edit and run reports off the MySQL data.
I have setup this scenario using a test MySQL database and an ODBC DSN on my local machine and everything works beautifully. However, when I try to duplicate the scenario with the MySQL database sitting on the remote host, I cannot connect. I called the hosting company and they said that the only solution they could provide is a process involving: exporting the MySQL data to a backup file, importing it into the local MS Access database, running reports/making changes and then uploading back to the MySQL database (if necessary). I really do not want to go through all of these steps every time I run a report or want to edit data.
I would really like to be able to use MS Access as the front end because of its reporting features. I dont care as much about the backend and would be willing to use MS Access or MSSQL if it would make things easier.
What is my best option to accomplish this?