[Interview] Part 6: Teresa Hennig, International MS Access Authority

This is the next blog in the continuing series of interviews with leading professionals. In this series of blogs, I have an exclusive interview with Teresa Hennig. Teresa is an international authority on MS Access, a top user group leader, a best selling author and a recognized and profiled MVP.

Enjoy!

Stephen Ibaraki, FCIPS, I.S.P., MVP

Top 10 Microsoft Access Tips

Stephen : You are a known world authority in Access VBA and the lead author for the VBA Programmer's Reference series. Please share your top 10 most useful tips-- those special gems that would not be widely known.

Teresa: Wow, I'm already working on my third book and it still surprises me to hear such claims. I can't imagine that I have 10 secrets that aren't widely known. After all, my focus is on helping others and sharing knowledge. So, how about ten of the things that I think are most important. Of course, my list is subject to change based on new experiences. Yes, you can read into that ・no one is exempt from getting exasperated, at least I'm sure not.

So, let's start with the basics.

1. Normalize! I can't tell you how important this is. The more complex the project or the more programs that it will interact with, the more burdensome poor table structure can become. The critical thing here is to have only one subject in a table and to avoid repeating data. There are excellent, easy to read, reference books on normalization. Mike Hernandez wrote, "Database Design for Mere Mortals."

2. Use a Naming Convention. There are established conventions that can be used as is or modified. I like to use short words or abbreviations and include a table identifier in the field names. Again, the more complex the solution the more helpful it is to have clear and concise names - not just on tables and fields, but on all the objects, including the controls on forms.

3. Avoid reserved words and special characters - this includes those from your add-ins and programs that you will be integrating with. Using reserved words and special characters can create conflicts when running code or even an action query. It is far easier to strictly adhere to the rule of not using these than it is to trouble shoot and create work-arounds.

4. Use error handling. And be nice about it. Create custom messages that advise the user of their options and indicate where the error occurred. When appropriate, trap the error and write it to a log so that you can trouble shoot and resolve the problem. 

5. Document your code. Don't just rewrite the function in English; most people who look at the code could figure that much out. Give the business rule or other nuances about why a function has to perform the way it does. If there are precautions or other issues, state those too. Include your initials and the date if you are modifying a legacy application or may be leaving this for someone else. In fact, you can almost always figure that you're leaving it for someone else.

6. Don't reinvent the wheel. There are many functions and routines that you will use repeatedly within an application and throughout many applications. Create your own procedure library of functions and routines and then reuse them. This will not only save you time, but it minimizes the potential for errors. Additionally, there many third party tools and add-ins that are can really speed your development process and help with administrative tasks. Many developers like MZTools and FMS products, but there are dozens of options. A word of caution about using other people's tools: they may conflict with your solutions and they may not always work as anticipated. Be prepared to compromise. However, if you consider your time is money, then you're often ahead when you purchase these tools. After all, you're also benefiting from the experience and feedback of thousands of other users.

7. Create templates for common reusable forms, reports, or even basic applications. A perfect candidate would be a form with a search function that builds a long string of criteria before building the record set. Other standard forms might be the splash screen, a main menu, a database relinking tool, a form to add new records, and even a form with the selection criteria for custom reports. There are likely standard reports; depending on the industry, one could have dozens of report templates ready to be customized.

8. Don't overcomplicate things. Look for the simple, straightforward solutions. They don't have to be elegant, they just have to work and be efficient. One thing that is helpful to remember is to use worktables. Sometimes, there just doesn't seem to be a way to create the necessary recordset from a series of queries. Well, quit hitting the wall and create a work table. The thing is, you might want to create a separate back end file for work tables and be sure to include a clean-up routine to compact them.

9. Design for the User not for you. Time spent listening and observing may be the most valuable time on the project. It takes a special talent to really do a good job with the needs analysis and then to transform that into concise specifications. You need to continue listening to the users as you go through the stages of development and deployment. Environments, needs and options change, so be prepared to be flexible and accommodating.
I can not overemphasize how important it is that the UI, or user interface, be comfortable for the users. The application and forms need to be clean, intuitive, easy to read and easy to navigate - they need to leverage data validation. In addition to saving time, the application should eliminate the potential for errors wherever feasible. But these areas are complete topics on their own.

10. Network. Don't feel as though you have to go it alone. User groups will welcome you and help find a solution to your questions. Go online and research through some of the newsgroups, articles and MVP sites. You'll likely find someone who solved a similar situation. If you don't find the solution, then you have the opportunity to post your problem and learn from others.
________________________
Look for more with Teresa in the next blog.
I also encourage you to share your thoughts here on these interviews or send me an e-mail at sibaraki@cips.ca.
________________________