databaseLessons

There are times when I would love to enter data into an MS Access database on my local machine, and then pass that data to a MySQL database on a web site. However, firewall rules normally prevent accessing those databases from outside the network where the respective server machines are located.

How can I get around this?

In researching this question, one reasonably simple answer is to have a small slave program on the website that hosts the MySQL database. Your MS Access database then passes the data to this slave, which then adds the data to the database on the internet.

Specifically, the approach I took is this.

• enter data into MS Access database on local machine
• Access exports data to a text file (or you could get fancy and do XML output)
• Access initiates an FTP command which transfer the text file to the website
• Access initiates the slave program on the website
• slave program reads the uploaded text file and inserts data into the MySQL database
• text file is deleted from the website

The slave program is created using PHP.

Next posting will start a series that will give more details. In the meantime, you may want to play with this idea on your own and then compare with my solution later.

This posting also exists as an article on the main site. See it here.

This entry was posted on Monday, October 8th, 2007 at 7:30 am and is filed under Articles. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.