Problem: I want to use R on my windows7 machine, but access data from a linux machine on my network.

In the past (circa 2005-2010, for earlier versions of R, e.g. 2.4, and Windows XP), I had used a Windows-specific library for access to MySQL called RMySQL, that was only available from a specific website, and only worked with certain versions of R. However, that solution was sub-optimal, broke every time I installed a newer version of R, and also seemed unnecessary since I should be able to utilize ODBC to connect to my MySQL databases. Every time I tried to figure it out, it didn't work. But here is a set of steps that finally worked.

15 Mar 2012

1.) Install the RODBC library in R.

2.) Install the ODBC connector/driver on your Windows 7 box (http://www.mysql.com/downloads/connector/odbc/5.1.html)

3.) From your Windows 7 box, open the control panel: Control Panel/All Control Panel Items/Administrative Tools, you'll see and option for Data Sources. Open that option, and select the "Drivers" tab. You should see the MySQL ODBC driver listed there. Go back to the User DSN tab and choose the "Add" option, and you should then have a chance to Add the ODBC data source. A configuration dialogue should appear called "MySQL Connector" in which you can give your data source a "Data Source Name" and a "Description". Under the "TCP/IP" option put the network resolvable name of your system (mine is called pi, so I enter "pi"). Then enter you MySQL username, password, and the name of the database to use when you connect to MySQL. With all this done, you should be able to connect to this database using R.