OfficeTipsAndMethods

Archive for the ‘Access’ Category

Clint Covington posted  Access 2010 deprecated features and components in the Access Product Team Blog yesterday that certain features have been deprecated in Access 2010:

• Calendar Control
• Snapshot Format
• Data Access Pages
• Paradox, Lotus, and Red2 ISAM’s
• Replication conflict viewer

If you have applications developed in older Access versions that you want to port to Access 2010, read Clint’s article to see what effect these changes will have on your application and some suggested workarounds.You should also keep these deprecations in mind for any new applications you may develop in pre-2010 Access versions.

Quote of the Day Not only is the universe stranger than we imagine, it is stranger than we can imagine. -Sir Arthur Eddington

Using forms to control routine data entry should be standard operating procedure for any non-trivial production Access database. In plain language, don’t give users (even yourself when you acting as a user and not the developer) access to the raw tables. Provide instead forms for entering, viewing, and editing records. In this article we will look at how to use two simple form event handlers to automate recording of when a record is created and when each record was most recently updated.

Event handlers are the key to adding custom functionality to applications. In this case, we want to keep track of when a record is created and that date it was last edited so we will need to work with the Before Insert and Before Update events. First, a little background.

When you create and edit a new record, the record does not exist in a table until you do something to cause the record to be saved. You are actually editing a memory image of what will become the new record when it is saved. Actions like moving to a different record, or pressing Shift-Enter initiate the save (or insert) process. As its name implies, the Before Insert fires before the record is inserted into the table.

To be completely accurate, the Before Insert event fires when the user types the first character of a new record (see msdn article). At this point the record does not yet exist. This give you, the developer, the opportunity to take some additional actions by adding appropriate code to the Before Insert  event handler. When the Before Insert handler finishes, the saving process continues. Along the way, the Before Update event fires, offering another opportunity to do something in addition to simply saving the record.

The chain of events is quite similar for existing records which are being edited. The only real difference is that the Before Insert event doesn’t fire because we are updating an existing record. The record is already in the table so the memory image of the record doesn’t have to be inserted into the table; the table just has to be updated to reflect the changes you have made to the record’s data. So when you do something to put the save process in effect, the Before Update  event will fire before that table data is actually modified.

If you want only a date stamp (without time) the function Date() would do the trick. However if you want the date and time of additions and modifications to be recorded, the Now() function will return a serial date value which it has read from the system clock. The two event handlers are so simple as to appear almost trivial:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.DateCreated = Now
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.DateModified = Now
End Sub

That’s all it takes. With these two form events in place, every time a new record is created using the form, the record’s DateCreated value will be set to the date and time the user started typing data. Similarly, when the form is used to edit data the, the record’s DateModified value will be set to the time that the changes are saved. An interesting side effect of using this approach is that new records have values assigned to both fields. The difference between the DateCreated and DateModified values indicates how long it has take the user to enter data into the new record.

Keep in mind that, while this approach might appear to be the basis of an audit trail solution, it only keeps track of record creation and last modification date and time. If you want to maintain modification history data you should use a separate, related table with a new record appended to it for each record creation/modification/deletion action.

Download the sample database from here. This is a zip compressed file contained the example in both Access 2000 and 2007 format.

I recently came across a site that offers free reference guides for Office and each of the Office applications. The guides are packed with shortcuts and other quick reference information. They are available for download in pdf format from FREE Quick References

Check out this Access Team Blog article in which Wouter Steenbergen describes new navigation features in Access 2010 in detail

Ryan McMinn and Clint Covington demonstrate the most significant new Access feature being introduced in the 2010 version View the Office Team Blog Article and click the link to their video .

>

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.

>

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.

>

While an actual release date is still to be announced, the next version of Office and Access are looking pretty exciting. For an overview of what’s coming in Access, have a look a the Access team blog.

>

Thanks to grovelli who prodded my about the next installment in the Ice Cream Parlour tutorial, here it is.

When we last looked  at the ice cream parlour database, we had identified the main tables of the database:

• sundaes
• dishes
• sizes
• flavors
• ingredients

For now, let’s focus on how to connect these tables together rather than on the contents of each field. You may recall that we identified the relationships between most of the pairs of tables turned out to be many to many. That is, for example, one sundae may use many ingredients and each ingredient can be used in more than one sundae.

To keep it simple I am going to focus on just this one pair of tables, sundaes and ingredients. Let’s say, for the sake of argument, that we have come up with a recipe for what we are going to call Chocoholic’s Mega Gooey Sundae Delight. Because we have chosen to use autonumber primary keys when we enter this recipe title in the Sundaes table access assigns 621 as the primary key. Now, if we need to refer to our Mega Gooey Sundae Delight outside of the table all we (or Access) need to know is that this particular Sunday is number 621 in our table.

Actually the end user does not need to know the value of the primary key at all. There are ways of getting the value and causing it to be entered in other tables without actually having to actually know the number.

So far, so good. Now the Mega Gooey Sundae Delight recipe calls for these ingredients

• Chocolate Ice Cream (832)
• Chocolate Sauce (365)
• Chocolate Sprinkles (798)
• Marshmallow Sauce (922)
• Toasted Almond Slivers (305)

After each ingredient name, I have shown for the sake of this illustration, the primary key that Access assigned to each of these ingredients when they were added to the ingredients table. The actual number doesn’t matter. What is important is that no two ingredients share the same number. To put it in Access’s terms, the values are unique. That means that we can be confident that whenever we refer it item 922 in the ingredients table, we are talking about Marshmallow Sauce.

Here is the relationship diagram for these two tables:

The junction table I have been describing is tblSundaeIngredients. The infinity symbol indicates that that side of the relationship is ‘many’, meaning that each sundae in the tblSundaes can have many corresponding records in the junction table. Similarly, each ingredient can have many corresponding records in the junction table as well.

I will go through the process of actually creating the tables and relationships in later installments of this series of articles. For now, I just want to focus on what things look like when we bring together a sundae with its ingredients using a junction table.

To keep things simple, I have included in the diagram only the fields and records necessary to illustrate the point. Notice how the junction table includes only numbers and those numbers are the same as the primary keys of each of the ingredients in tblIngredients. This is typical of tables that are related. The fields lngIngredient and lngSundae are known as foreign keys. In other words the values in those fields tell us what values we are talking about in the other (or primary) table.

Next time, we will take a look at actually creating these Access tables. Following that, we will look at the process for creating relationships.