OfficeTipsAndMethods

Posts Tagged ‘Access Programming’

Microsoft MVP Walter Niesz has put together a nice FAQ (Frequently Asked Question) article over at UtterAccess.com to introduce new Access programmers to de-bugging VBA Code. The article illustrates three basic debugging techniques that should be in every VBA programmer’s toolkit. If you are new to VBA or Access have a look at Debugging Techniques. While you are at UtterAccess.com have a look around at the thousands of opportunities to learn from other’s questions and to find answers to your own questions. (disclaimer: as a forum administrator at UtterAccess I enthusiastically support the forum which is, “without doubt is the only source for all of your Microsoft Access help needs…and more! ")

>

At the end of August, a discussion at my home away from home, UtterAccess.com piqued my interest in the topic of how experienced Access developers viewed the importance of commenting their VBA Code. I decided to conduct a brief survey of two groups, Microsoft MVPs, and UtterAccess VIPs. I specifically notified Access and Excel MVPs as well as the VIP group at UtterAccess.

While there weren’t quite as many opinions as their were people completing the survey, there certainly is a broad range of opinions.

Dennis, an UtterAccess member who was unable to complete the survey because he was away while it was open for response posted a forum message that pretty well sums up my own position on the question. He said in part,

“Commenting code was described by one of my college instructors as one of the most important, but least prioritized steps of the programming process. And I believe he was (and is) 100% correct.
One should never assume that the purpose of a block of code is self-evident. Even if you’ve used the same module over and over, that module should be documented so that someone (even you) in the future will quickly be able to see HOW and WHY a block of code is doing what it does.
Commenting costs nothing to compiled code, and is worth it’s weight in gold to people charged with improving and re-using code. After all, the cliché says "time is money" and the amount of time saved with a few lines of text within a block of code can be enormous.
There, that’s about it, in a nutshell.”

I should mention that I did not submit a response to the survey myself; I wanted to leave the answers to others and let the results fall where they may. The results were not particularly startling. They do reflect a broad range of opinion among the respondents. More than half of the 57 people answering the survey rated commenting as being either very important or absolutely essential.

The numerical results, however, only tell part of the story. The survey included two open-ended questions, ‘Briefly describe how you use commenting in your own work and the depth of detail you include in your comments.’ and ‘What would you describe as the single most important reason for including comments in programming code?’

A common theme for the first set of open-ended responses was, “to help me remember what I did and why.” Some of these comments expanded the theme to include “others who might have to follow-up on my work.”

In response to the second open-ended question, one person put it this way, “1stly To remind me of what I wrote and how the code works…I sometimes don’t view project for long periods of time. 2ndly so that if or when I expire someone else can take up where I left off.”

There were a few problems with the survey, some caused by my oversight when I designed it and some caused by the the survey software itself. Two people reported either being unable to submit their responses on the the survey site or being unable to edit/include comments that were greater than 255 character. I won’t complain too loudly about the survey site, however, since I used the free survey tool that is available if you register on the site.

As to survey design issues, I neglected to include a ‘none’ category in the question about programming languages other than VBA. That cause the ‘other’ response to be somewhat ambiguous.

All in all, however, I’d have to say that devising and implementing the survey and analyzing the results has been an interesting exercise. I have a few other ideas that I may try in the future. The full survey report is available in pdf format here.

I would like to publicly thank the UtterAccess VIPs and my fellow MVPs who took the time to respond to the survey.

>

Just as a real ice cream parlor needs tables where our patrons can sit and enjoy out products, our database model of ice cream sundaes will need tables in which to store information about our products.

In the previous installment of this series, we fleshed out the list of tables database will need. These are our main tables:

• ingredients
• dishes (containers)
• sizes
• sundaes
• flavors

Because we will be combining ingredients in various ways with various container sizes and ingredients actually come in several flavors, we will need some additional tables to help us model the combinations. These tables will include:

• sundae ingredients
• ingredient flavors
• sundae sizes
• container sizes
• sundae containers

Now it is time to determine what fields to include in each table. You may be starting to find this list just a little overwhelming. Try not to panic. It is really not all that bad as long as you tackle things a little bit at a time. So we will move ahead, slowly but confidently, one table at a time. For now, let’s focus on the five central tables mentioned above.

Table definition refers to identifying the fields that are needed to describe the subject (or domain) of each table. So when I think about a table, I start to think about the kinds of information I need to describe the subject of the table.

We will keep these basic guidelines in mind:

• each table describes only a single subject (entity)
• the fields included in each table should describe the subject of the table (attributes).
• data will be subdivided across multiple tables in order to
  • avoid duplication of data, and
  • avoid repeating groups of columns

I find that, in a database as focused on a single business solution as the ice cream sundae database is, a single table emerges as the central table at the heart of the application. Other tables in the database are needed only because they will have information that pertains to the central table.

So, for us, the central table appears to be the sundaes table. Let’s start there. What information do I need to store to describe each sundae? Obviously each sundae needs a name. It will also need ingredients and, since I plan to offer each sundae type in a variety of sizes, it will need size information.

However, storing size and ingredient data in the main sundaes table will lead to immediate design problems. While I might be certain ahead of time exactly how many sizes I will be offering and the maximum number of flavors might be used in a sundae, including that information directly in the sundaes table will break one of our basic guidelines. I would have to have fields like flavor1, flavor2, flavor3, ingredient1, ingredient2, ingredient3. In database terms these would be examples of repeating columns.

So, what’s the problem? Although not apparent at the outset of planning, repeating columns make retrieving useful information from stored data, unnecessarily complex. Even more important, repeating columns put serious limits on the future use of the database. No matter how certain you are ahead of time that you are describing how something is now and will ever remain the same, it is just not that easy to predict the future. If I "know" that I will only ever offer three sizes of sundae, small, medium, and large, the database (if I have included three size fields in the sundaes table) would not be able to handle changes I might have to make in order to remain competitive. Say, for example, that upstart competitor down the street starts stealing my customers because she has added a fourth or fifth size.

I have tried to describe the process by which I have determined what fields belong in the sundaes table. The process for deciding what fields to include each of the other main tables is the same. The following table shows the results of this analysis:

You may find that the list of fields in each table is somewhat short. Each table includes a name field. The sundaes and flavors tables don’t have any other fields. That’s because most of how we describe sundaes, for example, is stored in other tables. Flavor names, on the other hand are pretty much self-descriptive.

The dishes, sizes and ingredients tables have type fields. We will use these fields to categorize dishes, sizes and ingredients, respectively. Dishes may be either edible, disposable (plastic for takeout) or reusable (glass or metal for eat-in.) Sizes may refer, for example to quantity when referring to ingredients, to capacity when referring to dishes, or to the end-product size description (small, medium, large, for example) when referring to sundaes. Ingredients may be principal ingredients, toppings, or garnishes, for example.

Now we have a working definition of our main tables. In the next article we will define the additional tables we will need to flesh out the connections needed between the main tables.