databaseLessons

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.

A reader wrote to ask me the following.

Thank you very much for your sample databases. I did software for my hotel reservations and now I want to make SW for cash desk in my restaurant. Problem is that I do not know how to make matrix of buttons. Each buttom must have another name and another function. Names I have in my store software as PLU. For example I want to sell coffee then my staff will select category drinks and my SW will prepare matrix of buttons. There will be ice coffee, coffee, etc. How to do it. I have approximately 200 items in assorted categories.

I found this question quite interesting and it grabbed my curiosity. I actually dropped what I was doing to experiment. To see my full answer, check out Buttons: Now you see them, Now you don’t.

p.s. I’ve started including “Digg This” logos. If you appreciate all my hard work on these articles, please “Digg” my work. thanks

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.

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.