databaseLessons

I have finally resumed my series about using Access on a PC to feed a MySQL database on the web! Read part 2 here. This part describes the purpose of the database and describes 2 of the websites that get fed by this little homegrown system.

Remember that this method was created for websites that exist on servers that do NOT allow remote MySQL connections. Some servers do allow the remote connections, which simplifies the whole process.

While designing a relational database, it is a good idea to distribute the information in multiple tables. It is not advisable to store all the information in a single table, although it is easier to design. When your database grows in size, the efficiency decreases accordingly.

To read the rest of this article, Relational Database Design Guidelines - an introduction.

Have you ever had an Access database crash, and then not open again?

A database is said to be “corrupted” when a part of its data or functionality is lost. When an ACCESS database is corrupted, you may get several error messages while doing the common operations in ACCESS. For example, while opening and closing the database, while running forms or queries, while updating and saving records, or while scrolling through records. This is the indication that a part of your database is corrupted.

There are many reasons why this data or functionality corruption may occur.

(click here to read the rest of this article)

Although my favourite “filtering” method is the MS Access Query tool, I do use the Access Filtering method regularly as well. Here is an introductory discussion of Filtering in MS Access.

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 am sure that I have found them all now.

Seems that when I moved from AccessDatabaseTips.com over to DatabaseLessons.com I moved some files and never added them to the index pages. Most were discovered a month or so ago. Found 2 more today.

Need to use a query to change UPPER CASE values in a field to Mixed Case? Try this.

The documentation tool that comes with Access 2000 (under Tools, Analyze, Documentor) is VERY slow compared to the Access 97 version. I do not understand why. I use this feature frequently to print out the field names of a table. It is so slow in MS Access 2000 that I designed my own. Here is its story!

While I was at it, the info on “dao” was linked to from dozens of articles, but it was never added to an index page. It is now on the “Modules” page.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

I hope your databases are being good to you!