We help IT Professionals succeed at work.

Connect local MS Access front-end to remote MySQL back-end

1,020 Views
Last Modified: 2013-12-12
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?

Thanks.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
You cannot run Access over the net.
You will either have to arrange for the data to be locally stored for each user, or else develop a proper web frontend if you want to access a shared backend.

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
So you ISP won't allow you to remotely connect to the MySQL database? I'd find a different ISP, if it were me ... my ISP allows me to connect to both the MySQL and MSSQL database. I have several Access FEs that connect to those ...

Author

Commented:
It sounds like these two answers are directly conflicting:

"You cannot run Access over the net." vs. "I have several Access FEs that connect..."

My web host says, "Your MySQL databases are stored behind a firewall to protect your data. You can only access each one exclusively through your server. Direct access to your MySQL databases using a home PC (external ODBC connection) cannot be established. "

@LSMConsulting: Can you recommend specific hosts that will allow remote connections to MySQL databases?

@peter57r: Can you recommend an alternative solution to MS Access so that I can design custom reports to run on the MySQL database? I have looked at Navicat(.com), which seems great, but I run into the same issue where I am unable to connect to the database from my local computer.

Thanks.
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Sorry, just re-read and this isn't really complete:

You CAN connect to a MySQL database (or an MSSQL, or Oracle, et al) database over the internet from your Access program on a local desktop; I just did it several times this morning, once to a MySQL and once to an MSSQL.

Should be:

You CAN connect to a MySQL database (or an MSSQL, or Oracle, et al) database over the internet from your Access program on a local desktop IF YOUR ISP WILL ALLOW YOU TO; I just did it several times this morning, once to a MySQL and once to an MSSQL.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.