Database Lessons

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.

I noticed a posting on a forum the other day. The writer wanted to know how to get a simple list of all the tables in their database.

My initial research resulted in this query.

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>“~”) AND ((Left$([Name],4))<>“Msys”) AND
(((MsysObjects.Type)=1)))
ORDER BY MsysObjects.Name;

The writer was grateful but then re-posted a bit later to say that this did not list all the tables. A quick bit of research showed me that linked tables have a MsysObjects.Type=6, so …

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>“~”) AND ((Left$([Name],4))<>“Msys”) AND
(((MsysObjects.Type)=1) OR ((MsysObjects.Type)=6)))
ORDER BY MsysObjects.Name;

It is easy to loop through objects on a form that seem to be unrelated, and are not even connected to a recordset. We can just use the Tag property.

See the article at Looping Through Form Objects Using the Tag Property.

An age old question. How can we use a combo box to add new values to its list, “on-the-fly”?

This question has been answered thousands of times, and people keep asking it. Well, I am adding my voice to the side of the answers crowd in the hopes that at least 1 person will not have to ask.

To read my version of the answer, visit Not-in-List.

p.s. This one comes with a sample database!

I was reminded today why I avoid activeX components. Someone was having trouble installing a small Access database on to their computer. (I did NOT create the database) We quickly traced the problem to an older version of an OCX file on their computer. After 3 attempts to unregister/re-install/re-register the ocx file we finally got it working. Fortunately, this computer was walking distance from my desk. What if it had been in an office in another city?

What was the offending activeX control? A calendar. If you have not already done so, see my series about a calendar control that is pure Access (no activeX) starting at this web page.