Access Applications


            Check out my Access Download Page. January 27, 2007

Table of Contents

Access is a very powerful tool.Even beginners can develop rudimentary databases. I would like to caution those beginners and let them know that there is more to building a database than knowing the software. It is extremely important to understand good database design. This includes knowing about primary keys, relationships and normalization, for example.

Download one of my databases for examples of what can be done using Access. And keep coming back. I have quite a few examples and plan to continue updating and adding to these examples. Click here for the Download page.

There are lots of places to get help with Access databases. All of the chain bookstores have lots and lots of books about Access. There are all kinds of online sources. Your local user group might have an Access guru. The Central New York PC Users Group has a Special Interest Group (SIG) that meets once every two months.

The CNYPCUG’s Access guru is Art Yates, an independent consultant. (Visit his page.) Art has given me (and many others) invaluable assistance in the last five years. Go to the SIG meeting with your question or problem on a disk and leave with the solution on that same disk.

Compacting Databases

What is meant by compacting a database? Why compact databases? Click here to find out and to download two batch files that will help you with the process.

Opening Forms at Startup

Click here for directions on how to create a splash screen and an AUTOEXEC macro.

Primary keys

Semantic or nonsemantic, the debate rages on. The purpose of a primary key is to ensure uniqueness of a record. Should it contain data or should it be just a number with no meaning? I have yet to see a textbook or reference manual that does not use semantic keys. There might be some books out there that use an autocounter for primary keys; I have never seen one.
Click here is one man’s view. He rails against semantic keys.

Do you have an opinion about this topic? If so, email me and I will post appropriate responses.

Standards

Develop standards and use them. Use the same standards within your department or organization. For example, when naming objects use the prefixes shown.
tables tblObjectName or tObjectName
queries qryObjectName or qObjectName
forms frmObjectName or fObjectName
reports rptObjectName or rObjectName
macros mcrObjectName or mObjectName
modules modObjectName or oObjectName
Also, use mixed case as shown above or use all capitals. I prefer mixed case because I think it is more readable. And do not use spaces in your names. Spaces sometimes confuses Access and the program will not automatically know that “tblObject Name” is a field name. It will usually recognize “tblObjectName” more readily. Spaces in field names are annoying when programming. Each space has to be replaced with and underscore (_). So “tblObject Name” has to be entered as “tblObject_Name” in a module.
“Don’t worry about that, Deborah. I will never be programming,” you think to yourself. You never know how far into Access your adventures will lead you. I never thought I would ever be able to write even a line of useful code. That has changed and I have written quite a few subroutines to use in my Access applications.

Splitting a Database—Why Two Files for an Application?

Almost every database application that I have created has two files. One file holds the tables; the other, the queries, forms, reports, macros and modules (if any). This makes updating an application easier. Let’s use a scenario to explain.
You are using the registration application. You have all 350 courses your school offers, as well as 2,500 students, entered. The developer has just finished the changes to the forms and reports that you requested and has sent the new database. If you have only one database file, you have to import the queries, forms, etc., that have changed; otherwise, you run the risk of overwriting your tables with those that have old data.
If you have two files, you can replace the file that does not hold the tables. Refresh the links and you are ready to work with the new (and hopefully improved) file.

Referential Integrity

A well-designed database will have fields broken into tables and relationships established between the tables. Setting referential integrity is another step in keeping your data accurate. Let’s say that you have a table with city, state and zip codes. In a second table you have customers’s addresses and zip codes. The two tables are related on the zip code fields. If referential integrity is set, you will not be able to add a zip code in the customer table unless it is already in the zip code table.
Check the Cascade Update Related Fields checkbox and when you make a change to a zip code in the Zip Code table, all the records in the Customer table will reflect the change. Check the Cascade Delete Related Records and if you delete a zip code, all of the records in the Customer table with that zip code will also be deleted.
In this example, you should enforce referential integrity. You may or may not want to check Cascade Update Related Fields and probably do not want to check Cascade Delete Related Records. Each case will be different. The important thing to remember is to do what is best to ensure the accuracy of the data.

Transparent Images

I like images on some of the forms in the database applications I develop, especially on the splash screen and opening menu. And I know other people like them also, because I have been asked how to make the backgrounds of images transparent on Access forms. Images in the .GIF format can have transparent backgrounds. Now why not have transparent backgrounds on the forms. It avoids that “blocky” look. I struggled with this one for several weeks and finally got images with transparent backgrounds on my forms. It involved using Picture It! 2002 (I could not get it to work with version 2000) and dragging the image from from Picture It! to the form in Access. It worked fine for me but not for those users/developers who did not have the software.
The following was posted on an Access newsgroup and I figure if I post it here, I will never lose the directions :). This is so easy; I wish I had thought of it.
  1. Be sure to use a .GIF with a transparent background. If you need to make the background transparent, use any one of a number of programs to do so or go to GifWorks to transparify it.
  2. In Microsoft Word click on the Menu selection Insert, Picture, From File and select your image.
  3. When the image appears, click on it once to select it.
  4. Click on Copy on the shortcut menu, the Edit menu or the toolbar.
  5. Open your Access form in Design View and select Paste from the shortcut menu, the Edit menu or the toolbar. The image is pastd as an unbound OLE frame.
  6. Click on the Menu selection Format, Change To, Image control.
  7. Change the newly converted Image control's Back Style property to Transparent.
For more excellent ideas and databases, visit the author of this posting, Stephen Lebans at his web site.