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/)

Freakin' cool....

The tool is a little free piece of softwore call Access-to-MySql. These sorts of utilities have been around for awhile, but always seem problematic and buggy. Not this one.

The price was certainly right - $0 - and it worked like a charm on an XP SP2 box. The only hurdle was having to install the ODBC drivers (available at mysql.com) Once I did that everything worked.

The software gives you two options - connecting live and uploading or writing to an SQL dump file. I like working with the dump files., but I will experiment with the software's other features.

Available for free at www.bullzip.com

The belief is that if the proper model could be created, customers would be able to take advantage of a wider range of products and services. In doing so,
Grabers could offer thos

Unfortunately, lack to professional training in relational database design
made this task daunting and time consuming.

What follows is what we have come up with. This article will describe the various parts of the database.

The Products

The Vendors

The Customers

The Orders

The Order Process

To set the stage, I've been trying to model the web content of the Academy's website. This has been an on-and-off little puzzle since I redesigned their website initially.

Because there was such a varied amount of content, with the pages containing varied sections, all of which vary, I had trouble abstracting a solution out in my mind.

Finally, however I believe I have come up with something. I believe the model below can help get there. Now I just need to figure out the SQL to get the data I want.

One of the resources that I found to be extremely useful in my quest for better understanding of data modeling was from Tony Marston, a UK-based programmer and blogger extraordinaire. He presents a great discussion on The Relational Data Model, Normalisation and effective Database Design that gives a great summary on the normal forms, functional dependencies. Other resources on his site are way over my head, but I' ll be sure to review them again.

This is one of the goals of this blog - to inventory resources/sites and information I obtain along the way.

I'm attempting to build a database application for a print procurement system and am tackling it as a glorified learning lab.