Solved

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

Posted on 2008-09-29
5
959 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.
0
Comment
Question by:spacecenterhouston
  • 3
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22599403
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.

0
 
LVL 84
ID: 22599661
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 ...
0
 

Author Comment

by:spacecenterhouston
ID: 22599973
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.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22603602
You cannot run an ACCESS database over the net, as Peter says. IOW, you cannot place your Access/Jet database on a webserver and connect to it from your desktop (unless your ISP allows you to VPN to their servers, which I can assure you they will not do).

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.

My ISP is webhost4life; I have both MySQL and MSSQL, and I can connect to either of them. My clients regularly connect to my servers for testing (I don't host applications, but I do use my ISP servers as testing grounds). There are plenty of them that offer this service, of course. Also, depending on what you're going to be doing with this, you might look into a "virtual" server, which is one that hosted and maintained by others, but basically "belongs" to you (and where you could insist that they allow you to connect via ODBC).

There are plenty of solutions you can use to build reports; I use Crystal Reports for non-Access solutions, but be careful in regards to their licensing. If you're using this on a web server, be prepared to spend some serious bucks.
0
 
LVL 84
ID: 22603606
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now