Hope this helps someone. I used ODBC to MySQL over SSH port forwarding on a Windows client. Here goes:

1) Download and install cygwin (www.cygwin.com) on your windows machine; make sure to include OpenSSH, tcp_wrappers, and zlib. There’s a nice set of instructions here: http://pigtail.net/LRP/printsrv/cygwin-sshd.html. NOTE: You can also use Plink.

2) Download and install MySQL Connector / ODBC 3.51 on your machine, located here: http://dev.mysql.com/downloads/connector/odbc/3.51.html. I downloaded the Windows Zip/Setup.EXE

3) I needed to comment out the line “skip networking” in /etc/my.cnf. I did this by 1) SSH into my server, 2) type cd /etc and press enter, 3) type pico my.cnf and press enter, 4) put a “#” in front of the phrase “skip networking” 5) Save 6) Restart MySQL server.

4) Now you’re ready to set up an SSH tunnel from your local machine to the host. Open Cygwin and use the following command:
ssh -N -f -L 3307:localhost:3306 user@example.com.

5) Setup the ODBC connection. In Vista, goto Control Panel -> Administrative Tools -> Data sources and click Add. Select MySQL ODBC 3.51 Driver, click finish, and fill in the fields like such:
Data source name: You pick!
Description: You pick
Server: localhost
User: the name that you use to login to your server
Password: ******** Note: Your password needs to be 8 characters or less.
Database: the name of the database to connect to.
On the 2nd page of the dialog, enter port 3307
Click Test and you should be able to connect!

6) To connect MS Access to your database(s), goto external data -> import from ODBC, and when it says select data source goto machine data source. You should see your data source name. Click on that and follow the prompts. You’re good to go!

Comments

jparets’s picture

jparets commented

More than that, you can use the Drupal database in Ms Access applications. You can do the following:
- Open an ODBC connection in the Access code and send SQL statemets to the Drupal database
- Create table LINKS in Access and manipulate the tables as Access tables. Is a powerful way of creating forms and manipulate the database.
More info in:
http://drupal.org/node/221388

--------- An example of ODBC connection:

' Create object ODBCDirect Workspace.
public pubDrupalConection as Connection
Set pubWrkodbc = CreateWorkspace("NewODBC", "", "", dbUseODBC)
Set pubDrupalConection = pubWrkodbc.OpenConnection("connection1", dbDriverNoPrompt _
, False, "ODBC;DSN=mysql_drupal_anlave")
pubDrupalConection.QueryTimeout = 10

--------- An example of use:

Public Function DRUPALEjecutaConsultaAccion(strQuery As String)
'Execute action querys.
'strQuery: an String containing the SQL action statement
Dim query1 As QueryDef

Set query1 = pubDrupalConection.CreateQueryDef()
query1.SQL = strQuery
query1.Execute
query1.Close
Set query1 = Nothing
End Function

Diegen’s picture

Diegen commented

How is the progress on this ?

If ms access can query the drupal database, can drupal query ms access db ? Is there any documentation on this ?

Gerald Mengisen’s picture

Gerald Mengisen commented

I needed a similar scenario the other day (I wish I had seen your article first). As always, there are many different routes that lead to the same result. Some possible alternatives:

  • If MS Access is not available, OpenOffice Base is quite a good replacement (for other alternatives, see here, however, there are not many free tools out there that have a visual query builder that can handle relationships between tables).
  • A lighter alternative to Cygwin for SSH is Putty which is one single .exe file - no installation needed. And this article explains how to use port-forwarding with Putty.
  • OpenOffice Base works with JDBC and ODBC; however, the JDBC connection timed out all the time. ODBC was more reliable, since the driver also had an automatic reconnect feature. The following link can be used to download the latest ODBC driver for MySQL: http://dev.mysql.com/downloads/connector/odbc/
jshimota01’s picture

jshimota01 commented

I have installed both 32 bit and 64 bit odbc connectors. I was intending to use MS Access 2010 (pro) to create web forms for my application. During the ODBC table link an out of box connection to all tables would crash Access during the link process.
The first table that crashed was block_node_type. I ran again, skipping this table and crashed on Cache_menu. then both date tables crashed. I stopped and researched. In the MySQL odbc driver 5.1.11 a problem with the length of table name, coupled to the information_schema causes a crash. One developers note also reflects that underscores can add to the problem, however that didn't prove true in my case.
I degraded to version 5.1.10 and was successful in creating this linked table via ODBC to a functional MySQL database.
access to the previous versions of the odbc drivers is found at:

http://www.mirrorservice.org/sites/ftp.mysql.com/Downloads/Connector-ODB...