ODBC Connection to MySQL?

software development

#1

Hi there,
I would like to make an ODBC connection to MySQL using either FileMaker or Access. Even willing to do this through a Virtual Private Network for security reasons to help facilitate manintenance issues like backup, and quicker data entry using the Client that is best for the job.

I searched here and never found a definitive answer.

Help.

– dave


#2

It’s possible, although we don’t have any publicly available documentation to point you to on this one. Let us know if you’re successful in getting this to work.

Basically, on our side, all you need to do is grant permission to the IP or range of IPs you’ll be connecting from, and then configure Access or whatever program properly.

Here’s some directions, modified from our internal documentation on this. I haven’t done this myself (mysqlf?), so I can’t give you a lot more information than this.

  1. You must also install the Mysql drivers so that access can connect to our databases. Just unzip the myodbc file and run setup on your machine.

  2. Now we have to set up the data sources. Go to control panel, open ODBC. You should have opened a window that says “ODBC Data Source Administrator”. Choose System DSN tab. Choose “Add…” Another window pops ups. Select the MySQL driver and Finish. Now fill in important info. Windows DSN name: (pick whatever name you like for the database) MySQL host: mysql.example.com MySQL database name: exactly that User: your database username Password: your database password Now hit OK. You will need to repeat this for each database that you want to connect to via access. *

  3. Start Access. Creat a new blank database. call it whatever. If you set up a system DSN for the users database, perhaps you want to name it users. :slight_smile: Now you should have a blank window. Right click inside the window. Select “Link Tables”. You can also do this by clicking on the “New” tab and selecting “Link Table”. Now you get an open file box. On the bottom left, go to the drop down list for “Files of type:” and scroll all the way to the bottom. The last option should be ODBC Databases(). Select that. If it’s not there, you did something wrong and probably need to reinstall the drivers. Now another box pops up. Select Machine Data Source tab. You should see a list of the data sources you’ve set up. Pick whichever one you need.

We do offer a VPN service - if you set this up, MySQL / ODBC connections will automatically go over the VPN.


#3

Will,

I’m trying to do just this, and I feel I’m reeeeally close to it, but I just can’t figure out how to grant permission for my host/IP, which is something like “ool-12c3456e.dyn.optonline.net”.

Any tips? I’d be glad to write up a FAQ for future users who want to do this if I could just cross this last hurdle :slight_smile:

For anyone who wants a good FAQ now, try this page:
http://www.washington.edu/computing/web/publishing/mysql-access.html

Thanks,
Jim


#4

That’s probably a dynamic IP, so you’ll need to find out the approximate range of IPs you’ll be connecting from (your ISP may be able to provide you with this information).

Allowing %.dyn.optonline.net would work, but would not really be restrictive enough for most situations.


#5

Once I know my IP or range of IP’s, how do I grant the IP/IPs permission to connect to my MySQL database?

-Jim


#6

Edit the MySQL user from the panel. There are some instructions there, but basically, you can grant a particular user permission to access the database from a particular IP or range of IPs. % is a wildcard, so:

%.newdream.net would allow hosts with reverse DNS in newdream.net

66.33.200.% would allow hosts in 66.33.200.0/24 (i.e., 66.33.200.0 - 66.33.200.255).
66.33.200.141 would allow the host 66.33.200.141

As an added precaution, you may want to only grant this user access to perform operations it needs to.


#7

Works perfectly, thanks! This is a great alternative to the powerful yet lightly clunky PHP MyAdmin tool, especially for casual users who are used to MS Access (like the folks I’m developing a site for :slight_smile: )

Thanks again,
Jim


#8

I do this with my own Solaris Box and local install of MySQL. It would be really cool to ODBC all the way to DH. But do you think I can do it through a NAT firewall? I don’t remember what my IP gets converted to. Isn’t there some web sites that report back to me what my apparent IP is?

A good reference on myODBC is at http://www.nusphere.com/products/library/devshed_mysql_odbc.htm


#9

If you login to your shell account, just use “finger” to see where you’re logged into from (this will show the hostname; in most cases, you’ll be able to do a reverse lookup on this by typing “host [hostname]” to determine the IP address.

If “last” works on your machine, you can do “last -ai [username]”.


#10

You just need to authorize the router’s IP address, because that’s all the outside world sees anyhow. If you go to the MySQL panel and edit a user, it will prefill in your router’s IP address in the “Add Host” box.

Your internal IP is probably between 192.168.0.0 and 192.168.255.255, but only your router knows that.

This worked for me on my home wireless network, but if that doesn’t work for you, follow will’s instructions in the other reply to your post.


#11

host command gives me two IPs for me, even after I hung up on the ftp connection and the samba connection. ??


#12

Holy Toledo! It works! This changes my whole project. I 'm trying to share some MS Access tables with a small group of biologists in different agencies around the state. I thought I was going to have to do some Perl programing with MySql to give them a simplified version of phpMyAdmin to let them select data from tables and download it to their PC’s so they can play with it in MS Access. Now I can just distribute MyODBC and the host connection parameters, and we’re connected! I just hope I can figure out how to authorize connections from some of these fed agency’s firewalls.

I just signed up for a test drive of Dreamhost and now I’m convinced I want to roll my whole site over to you guys.


#13

[quote]I just signed up for a test drive of Dreamhost and now I’m
convinced I want to roll my whole site over to you guys.

[/quote]

Woo-hoo! :>

Glad to hear it, MajorGeek.

  • Jeff @ DreamHost
  • DH Discussion Forum Admin

#14

Just using % should allow all, if you are really having problems, like I am, correct? How long does it take for the database user to update? I keep getting “Can’t connect to MySQL server on ‘servername’ (10060)” errors.


#15

MySQL users should update pretty much immediately.

  • Dallas
  • DreamHost Head Honcho/Founder

#16

Yeah, it was behind our firewall at work that I had the problem. When I tried it at home, it worked fine. That’s a pain… :frowning: