Print-friendlySimple MySQL Magic with ODBC
A colleague and I were talking recently about a website that used a batch-type program to upload an Access file to the webserver each night. It allowed the organization to provide the latest, most accurate information in near real-time. This discussion got me thinking about more effective ways and what to do with MySQL and the LAMP (Linux Apache MySql PHP) platform.
My colleague's website was developed on the Microsoft ASP platform. This made it super simple for a little batch script to FTP a new Access .MDB file each night. With the ASP scripts already in place, the new DB file just replaced the old one. Nothing really changed. Extremely simple move and effective.
All of this got me thinking about how I could do this type of thing with my LAMP platform. Since I utilize MySQL as the DB engine and not Access, doing this type of task is more cumbersome. There is no way to interface with Access's JETDB engrine with LAMP (that I know of). To manage this process, what I had been doing was relying on this little Acess-to-MYSQL tool. The tool creates an SQL dump file that I had been subsequently loading into MySQL via PHPMYAdmin. This process took a couple of extra steps, but worked.
But a client recently needed a more streamlined solution. I thought of writing the application to be completely web-based. That created some issues relative to reports. I was resigned to hack together some solution with the tool I had been using, even if it wasn't that pretty.
Then I began to play with ODBC. Suddenly, batch scripts and SQL dumps became a thing of the past. ODBC stands for 'Open Data Base Connectivity' and is a standard that lets most databases talk to each other. Somehow, MS Word files and Excell spreadsheets can also use it.
Here's what I discovered:
Enter MySql ODBC - Installing the Driver
I went to mysql.org and downloaded their Win ODBC Driver 3.51 and installed on my Win XP Pro box. On my box, I went to Control Panel/Administrative Tools/Datasources and added a User Data Source that pointed to my MySQL database on the webserver. There are a couple configuration pararemeters within the driver that I picked-up on from my readings at: http://dev.mysql.com/doc/refman/5.1/en/msaccess.html. I implemented those. Very simple.
Control Panel Access Hosts
One of the things that stumped me in setting this up was logging into the MySQL server remotely. This was addressed with a simple adjustment made through the control panel. Within the MySQL Databases page, simply added an Access Host (%) solved the problem. Now you can log into MySql from anywhere.
Using MS Access
With the driver now in place, any MS Access table can be directly (and tranparently) exported into MySQL (File/Export) by selecting ODBC. It'll automatically upload the complete table to the server. (Of course there are a couple to things to keep in mind about this; see previous link).
But the real power is now Access has the ability to use the tables that reside on the webserver just like they were its own. You simply use 'Link Tables' within Access, choose your ODBC driver and a list of tables appears. Select your desired table. It's then added to Access's table list. Opening it shows the data as it stands on the webserver. Any updates you do to it (add/update/delete) are updated on the server in real-time. Keep in mind, too, that the tables can also be imported as well, but then they become localized only.
In theory, ODBC can be used to create a nice MS Access front-end to a web-enabled database. No more '2 databases' - only one. Instantaneous updating. Instanteous maintenance. Sophisticated reporting from web data.
Certainly there are a number of tweaks and performance concerns related to this methodology that will need to be addressed. The databases I maintain a super simple, so hopefully issues will be at a minimum. However, Microsoft has published a comprehensive paper on working with Jet via ODBC that I'll probably review (http://support.microsoft.com/kb/128385/EN-US/)
