Argee's Access Blog

I was quite surprised yesterday to see reference to an article I wrote several years ago, Data Modeling for the Access Newcomer is an internet newsletter. I am not going to name the newsletter here because I don’t want to give it any free publicity. You won’t find it by links that I provide.

The particular newsletter follows the questionable practice of posting teasers about articles with links that lead to a sign-up page for their paid service. Once you have signed up, chances are all you will find is yet another link, this time to another site where the piece was available to you free of charge all the time. So you have just paid for, or at least signed up for a trial of, a service that frequently simply provides you links to pages and sites that a freely available without ever going near their service. If it was a free trial, you can bet they will then be soliciting you for a paid subscription. Definitely don’t buy the subscription unless you feel you are getting real value for your money.

In the case of my article, the full text of the article is freely available in Garry Robinson’s excellent VB123 blog. Garry recently purchased rights to the best years of Smart Access magazine (the years when Peter Vogel was the editor.) You can purchase electronic versions of sets of the magazine from Garry’s vb123 site if you are interested in a set of excellent articles that spanned the years from October 1996 to April 2006. That is almost 10 years of monthly issues with articles by hundreds of Access experts.

If you are just interested in my article, this link Data Modeling for the Access Newcomer, will take you to the full text of the article. Garry is very generously publishing the full text of individual Smart Access articles in his blog. He has a convenient index to the Smart Access articles.

There is an old expression that says, “Why buy the cow, when you can get the milk free?” While the expression can have a somewhat off-colour meaning, depending on its context, I think it deserves a paraphrase here, “Why pay for a link when you can get to the real article free?” If someone is trying to sell you links, use your favourite search engine to find your own links. It won’t cost you a cent.

In the last installment we explored the many to many relationships that exist between our various table. At that point we had not yet created the tables. This article will deal with the nuts and bolts of actually creating a table.

A word about names

The names you use when you create your database objects are important and should follow certain guidelines. Access reserves certain words for its own use. Logically these words are known as reserved words. Unfortunately some of these words are the first that comes to mind when you are trying to name fields for example. In this article I discussed at a conceptual lever, the tables we need in the Ice Cream Parlour database. If you look back to that article you will see names like ‘Name”, “Description”, and “Type”. These words all have special meaning within Access and should not be used to name Access objects.

So you will notice that the field names in the relationship diagrams in my last article are different from those I used in the conceptual diagrams. So, for example I have a field ‘strSundaeName’ rather than simply ‘Name’. What about the ‘str.’ That part of the name comes from a convention I follow to add a prefix to the name that indicates the datatype of the field. Many Access developers do not use prefixes for field names. So SundaeName would be perfectly acceptable and, in fact, preferred by many developers.

On the other hand, many developers do follow a naming convention that prefixes the names of Access objects like tables, queries, forms and reports, with three characters to signify the type of object to which the name pertains. So, for example, the Sundaes table is actually called ‘tblSundaes’.

Notice also that tables are usually plural. That is because the table contains records where each record is one example (or instance) of the subject of the table. So, tblSundaes has fields with names like strSundaeName (or SundaeName, if you prefer.)

Creating a table

There are several ways to create a table in Access. Although I don’t recommend it, you can design a table by entering data into a datasheet. Access will determine the data type for each field based on the data you enter. And unless you are careful to name each field, Access will assign such helpful field names as Field1, Field2, etc.

In Access versions prior to 2007 there are table creation wizards and in 2007 there are table templates to help you out. Unfortunately, tables created by wizards or from Access 2007 templates have field names that do not follow accepted naming standards. They include spaces in the field names. Now, while this may seem perfectly natural to you if you are an Access beginner, those spaces will come back to haunt you when you decide to enhance your work using advanced features like Visual Basic for Applications (VBA).

I prefer to create all tables using the Table Design View. Before we go there, however, we should map out the fields we will need and determine what their respective data types should be. For the sake of illustration I am going to deal with creating one of the tables we have decided we need. If you are following along and creating your own ice cream parlour database, the best way for you to learn will be for you to create the other tables on your own. I will be providing a link to my version of the database with all tables created.

Before we look at the table designer, let’s summarize the information we will need for each table. I am indicating two possible names for each field. The first name follows the naming convention I prefer to use. The second is a valid name in a format that omits the data type prefix. Whichever style you choose, it is important to be consistent throughout the database.

Sundaes - tblSundaes

Dishes - tblDishes

Sizes - tblSizes

Flavors - tblFlavors

Ingredients - tblIngredients

Those are the basic definitions of our five central table. As already noted, we will need additional tables in the database to allow us to connect these central tables together so that the database reflects the one to many relationships between them. We will also need a lookup table for types the type descriptions used by tblDishes, tblSizes, and tblIngredients.

Coming next: Using the table designer.