Hack 44. Tunnel into MySQL from Microsoft Access

Microsoft Access is a terrific tool for building desktop systems. MySQL is superb as a platform for web development. Here's how they can talk to each other.

Microsoft Access is hard to beat when it comes to rapid application development. Although it is not free software, it comes bundled with many Microsoft Office packages, and this means you will find that many business users already have it on their desktops.

MySQL is a good choice for database-backed web sites. If you are looking for a hosted web service you will find that a Linux-based MySQL package is one of the least expensive and best options. It would be useful to use the Access interface to manipulate MySQL.

To make this hack work you will need to have an account that allows SSH access.

In this hack, you will create a link in Access to the tables on the remote MySQL platform. Figure 6-7 shows the table list with a mixture of local (Table1) and remote (bbcRemote) tables.

Figure 6-7. Linking to a MySQL table from Access

 

6.4.1. Create a Secure Tunnel

A number of SSH clients run on Windows and allow tunnels to be created. We suggest that you use Plink because it is easy to control with command-line parameters. Plink is available for free from the PuTTY web site (http://www.putty.nl).

Plink will listen on port 3306 of your machine. It diverts traffic through to the remote machine, where it pops up on port 3306 again. Essentially, you can fool other applications into thinking that you have a MySQL server running on your machine, responding, albeit slowly, on the standard MySQL port.

You need to have the tunnel running when you create the linked table and when you access the remote data. The Plink tunnel runs in the background, and you can shut it down from the Windows Task List/Processes panel. In the code shown shortly, you can change the vbHide parameter to vbNormalFocus to allow you to see it in the task bar. You will almost certainly want to make that change while you are debugging.

6.4.2. Obtain the MySQL ODBC Connector

You will also need the MySQL ODBC connector (version 3.51 is hardcoded into this example), which is available for free from http://mysql.com. You do not need to install the MySQL server or any other tools on your desktop machine; in fact, if you end up running your own MySQL server locally, you will need to change this example so that you are tunneling from a different port on your desktop machine (change the first occurrence of 3306 in the following example to the desired port number).

6.4.3. Start the Tunnel Using Visual Basic

You can perform operations, such as starting the Plink connection and creating an ODBC connection, using the Windows control panel. However, in this example, we've established the connections using Visual Basic code from Access. You can set the code shown to run when the database application starts, or when users decide they want to update to or from the remote server.

Creating the table link is a one-time process; once you've created the table link, the .mdb file keeps the details. You should create the table link when the application is installed. With the table link in place, you can read and write to it almost as though it was a normal Access table, but the performance of operations involving remote tables is going to be worse than with a local table.

Here's the code to start the tunnel:

Dim plink As Long

Private Sub StartTunnel_Click( )
'Run plink. Use Linux account details to establish tunnel.
plink = Shell("c:Documents and SettingsandrewDesktopplink " & _
 "-l andrew -pw secret -L 3306:localhost:3306 " & _
 "server3.web-mania.com", _
 vbHide)
End Sub

Private Sub CreateLinkedTable_Click( )
'Test to see if the table already exists
If DCount("*", "MSysObjects", "name='bbcRemote'") = 0 Then
 'Use MySQL account details to link table
 DoCmd.TransferDatabase acLink, "ODBC", _
 "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" & _
 "SERVER=localhost;" & _
 "DATABASE=dbname;" & _
 "USER=user@localhost;" & _
 "PASSWORD=password;", _ 
 acTable, "bbc", "bbcRemote", False, True
Else
 If MsgBox("Table already exists. Do you want to drop it?", _
 vbYesNoCancel, "Drop Existing Table?") = vbYes Then
 DoCmd.RunSQL "DROP TABLE bbcRemote"
 End If
End If
End Sub

Here are some parameters, objects, and functions that bear discussion:

 

user @localhost

You log in to MySQL as user @localhost even though you are not a local user (replace user with your MySQL username). The tunnel hides the fact that you are connecting over a network.

 

DCount

The DCount function is one in a family of domain aggregate functions including DSum and DMax. These functions take three parameters: the column to be aggregated, the table name, and the WHERE clause condition. The performance is terrible, and getting the exact string for the WHERE clause can be fiddly. Nevertheless, DCount is massively useful for returning scalar values from your tables. The DCount expression DCount("*", "MSysObjects", "name='bbcRemote'") is equivalent in SQL to:

SELECT count(*) FROM MSysObjects WHERE name='bbcRemote'

 

It returns 1 if there is a table called bbcRemote and 0 otherwise.

 

Shell

The Shell function launches other applications. It returns the process identity, which is almost enough to kill off the process later.

 

MSysObjects

The MSysObjects system table is normally hidden from view, but it contains details of all of the tables and several other top-level Access objects.

 

DoCmd.RunSQL

You can assemble an SQL command as a string and fire it off. There are several ways to change data without using SQL in Access. Putting together an UPDATE or an INSERT statement and executing it using DoCmd.RunSQL is often the easiest solution.

 

DoCmd.TransferDatabase

This is the command that actually creates the link to the table. You can use it to create a linked table or to copy tables to and from a variety of other systems.

6.4.4. Stop the Tunnel

Once you have started the Plink process, you cannot control it from Visual Basic. The way to shut down the tunnel is to kill the Plink process.

Even if you stop the tunnel, your linked table still exists; it just doesn't work until you restart the tunnel, and it gives an error if you try to access data.

Killing off a Plink process is more complicated than starting it. You need to declare some library functions, and you need to reference the plink global variable that was set by the Shell command:

Private Declare Function TerminateProcess Lib "kernel32" (ByVal hProcess _
 As Long, ByVal uExitCode As Long) As Long
Private Declare Function OpenProcess _
 Lib "kernel32" (ByVal dwDesiredAccess As Long, _
 ByVal bInheritHandle As Long, _
 ByVal dwProcessId As Long) As Long
Const PROCESS_ALL_ACCESS = &H1F0FFF

Private Sub StopTunnel_Click( )
'Kill the plink application. Return value 1 is good, 0 is bad
MsgBox TerminateProcess(OpenProcess(PROCESS_ALL_ACCESS, 0, plink), 0&)
End Sub

 

6.4.5. Test the Connection

You can check whether the tunnel is operating by pointing your web browser at http://localhost:3306. If it is working, you will see a garbled web page. If you get a connection error, the tunnel is not working.

6.4.6. Connecting to Other Databases

You can use a similar technique to tunnel into other database engines. However, you will have to load an ODBC driver to enable the connection. Oracle, for instance, offers this as a free download.

Connecting to SQL Server from Access is normally straightforward. If you have a firewall in the way, this tunneling technique will work if you have an SSH service running inside the firewall.