Pitching SQL Spitballs

Some time ago, my company was maintaining information in an Access database that we also wanted to use as Web content. We were on a Windows host at the time, so we just FTPed the database up to the server every day. A little kludgy, but it worked well.

Then we switched to a Linux host behind a strict firewall (so no replication or remote access). What to do?

What I ended up doing was creating a macro (written in VBA) in Access that would “throw” a record from any table at a page on the Web server. That page was a “catcher” that would process what was thrown at it, and write it to the same table in a MySQL database.

It’s generalized – so long as a duplicate table exists in MySQL, it will transfer data from any Access table. It also allows for data-scrubbing: you can run user-defined functions on field values coming in from Access before putting them in MySQL (handy for date conversions).

What was handy about this is that you got all the client-side functionality of Access (I built some great user forms to manage the content), but all the data made its way out to the Web server to be used on the Web site. It was kinda the best of both worlds.

I’ve moved on to something else now, but this code was really handy and worked really, really well. It never failed once, as a matter of fact. I don’t want this to go to waste, so I’m posting it here. Here are:

(Postscript: The code has been lost to time, and probably wouldn’t have worked anymore anyway…)

I’ve commented them quite a bit, so you should be able to figure them out. But if you have questions, drop me a line. Looking back over it, I realize that I never wrote it for anyone else to look at it, so there may be crap in there that doesn’t make sense.