In order to get the most value from your computer technology, and to prevent its misuse, you should create reusable systems and published standards. Everyone knows this fact, but few apply it. In the Access environment, some developers think a systematic approach to development is to use the form wizards so all their forms look similar. They neglect to take the next step, however, to ensure that their forms include help or status bar text for users, validation rules to protect the data, and event procedures to trap errors and changes in status. The difference between the two steps is the difference between using Access well and using Access wisely. I wrote this book to help you use Access wisely.

Why Doesn't My Computer Understand Me?

Computers and software get faster and more powerful with each passing release. But are these tools really getting smarter? My father has watched me upgrade my personal computers each year for the past 14 years and notes with each revision that he will buy a computer as soon as computers can understand his speech.

I think many of us dream-even expect-that computers will be able to converse with us in the not-too-distant future. But even at that point, will they really be any smarter? Will they understand only what Dad says, or what he actually means? The difference is important, especially with respect to data stored in a database. Imagine the difference between these requests: "Hal, add a record to the contact table noting that I saw fourteen people today" and "Hal, add a record to the contact table noting that I saw four teen people today."

Ultimately, it is up to us as users to continue to adhere to the maxim that has pervaded the computer world since its beginning: garbage in, garbage out. If you ask a flawed question of the computer, you can expect a flawed answer. If you enter bad data into a database, you will make a bad decision based on the inaccurate report that comes out. This probably will be true in the future, when you are speaking to your machine, as it is today. Consequently, users and application developers-and not the computers themselves-are responsible for the quality of information that is stored in databases.

Deciding if This Book is for You

What standardized approaches do you use to develop Access applications? If you are like most Access users and developers, you've not had the time or energy to even consider creating standard approaches, development tools, or reusable objects to make your time in Access more productive-simply learning to use Access well keeps you plenty busy. This book was written with your situation in mind.

Access is a product with multiple personalities. On the surface, a quick tour of Access leads you to believe that it was created primarily for database neophytes. Like a friendly personal assistant, Access helps you organize and store your information by using such features as the following:

However, below the surface lies a completely different persona-that of a workhorse just waiting to pull your heavy load with the following features:

In Part I, "The Leszynski Development Framework," this book assumes you are comfortable working with the personal assistant side of Access. You should know your way around the Database window and understand the various Access objects. In Part II, "Expert Solutions Techniques," a slightly higher skill level is expected. You should be able to create basic tables, queries, forms and reports, and to apply the fundamentals of writing a VBA function or event procedure. With such a foundation in place, you are now ready to use this book to tame the workhorse persona of Access, or if you are using it already, to make it work even harder for you.

You will find this book valuable if you fit any of the following Access user profiles:

These profiles match the majority of user types that are serious about Access as a productivity tool, or would like to be. You'll note that the list above does not include the casual home user or the user trying to create their first Access table or form. If you are trying to figure out how to create your first table, macro, or form, this book is less useful to you than step-by-step feature books like Que's Easy Access for Windows 95 by Barker or Using Access 97 by Jennings.

What Is an Expert Solution?

The term expert solutions in this book's title does not mean solutions created by experts. This book does not assume you are an expert nor that you want to be. Instead, the title is meant to imply that you'll learn about solutions created using the processes employed by experts. This book helps you learn how Access application experts work-their standards, philosophies, tools, and techniques-and how to apply such knowledge to your development.

What You Will Learn

Most software books are feature-focused. They walk you through the features of a tool with each chapter devoted to a specific technique or technology. They demonstrate features using a sample application that may or may not be relevant to your needs in an attempt to cover most of a product's features along the way.

This book takes a different approach. Access 97 Expert Solutions is process-focused instead of feature-focused. It recognizes that application development is a process with the following attributes:

In this book, you will spend time with both facets of the Access application development process: theory and technique. I personally don't believe that you can be successful unless you apply and understand both equally well. If you understand the theory of application development (the attributes listed above), and do not apply them to your forms, reports, Visual Basic coding, and so forth, you will still not create solid applications. On the other hand, if you have outstanding programming skills but do not understand how an application is constructed or how the development process flows, you will deliver incomplete or flawed solutions.

Consider a real-life analogy. First, picture an accountant named Mike. He has been to eight years of college and reads accounting books voraciously. He can spout tax law in a heartbeat, always knows the prime interest rate and the Dow Jones average, and teaches evening accounting classes at the local university. Mike is really good with theory.

However, the work he actually produces tells a different story. When you put him in charge of your taxes, he takes all your receipts and staples them together alphabetically instead of by category. He totals them in his head and regularly adds the numbers wrong. When filling out your tax forms he scratches out mistakes instead of erasing them. And he leaves his filing cabinet and office containing your personal financial records unlocked, even when he goes home. While competent in the theory of accounting, Mike is not good with technique.

Now, picture a second accountant, Mary. You send her your financial information and she produces a balance sheet for you in only two days. The report has been entered into a computer and has headers, footers, and perfect math. She makes several copies-for you, your spouse, and your banker-and sends them to you by registered mail. Mary is obviously a whiz at technique.

However, on reading your balance sheet, you notice that she hasn't adjusted your real estate asset holdings by subtracting the mortgage balances. She also placed liabilities before assets, in violation of published Generally Accepted Accounting Principles, and forgot to project your impending tax liability. While Mary is great at implementation, she is not good at theory. She understands how to produce results, but not usable, accurate results.

Access 97 Expert Solutions recognizes that there are many Access users, developers, and project managers that are like Mike or Mary, but not as many that have the best attributes of both. The following are the three primary reasons why few developers have mastered both the theory and technique domains within Access development:

This book is the first ever to comprehensively document the theoretical side of Access development. It has two main parts (Part I and Part II), the first of which is primarily devoted to application development theory. In Part I, "The Leszynski Development Framework," you learn how to do the following:

Part II, "Expert Solutions Techniques," concentrates on the Part I pulls these application elements together into the Leszynski Development Framework (LDF), which reflects the belief stated in the first list in this topic: if it can be standardized, written down, taught, and applied consistently, it can be mastered. You can use the LDF blueprint over and over to guide your development process. Whether the application is large or small, LDF provides the rules to minimize your design time, management effort, and development problems on the project.

technical side of development. Everyone loves tips and tricks, and you will find plenty there. But this book goes beyond the norm and shows how its tips and tricks map back to the theoretical half of the book. Thus, the tips and tricks provide practical examples of how to implement one or more of the theoretical points detailed in Part I. Taken together, the parts of this book combine to present the blending of theory and technique required to produce truly expert solutions.

Foundations for Expert Solutions

The information in this book is based on actual events and the lessons learned from them. I have collected real-life experiences from my work and from that of my development staff in shaping the content. Among us, we have created hundreds of PC applications in the last dozen years, about one hundred of them in Access. I wanted the examples in this book-even in the theoretical chapters-to reflect real things that happen to real users and developers. I hope you find some small touchstone in each example that feels familiar and helps you see its applicability to your own situation or experiences.

Collecting Stories

Some of the more interesting examples and short stories collected for this book are used in the chapter introductions where they are most relevant. Many of these stories were submitted by people in the Access development and marketing groups at Microsoft, and others solicited from prominent developers in the Access community. Where a credit is not noted, the example is mine or is by one of my employees.

Most of the Access applications developed by my company have development time budgets of between 200 and 500 hours, which means that they are user interface intensive, to the following extent:

These listed attributes are part of what I consider to be the minimum requirements for delivering a quality Access application to users. This book is intended to show you why each of these areas is important, and how to implement each in your development work.

I also applied a lot of personal experiences to the writing of this book. As the owner of a consulting firm, I have a payroll to meet and clients to satisfy. Without repeatable techniques, documented conventions, reusable objects, coding standards, and similar methodologies, I could not be organized, successful, and profitable while meeting all these objectives. I believe earnestly in the necessity of the information in this book, not from some abstract viewpoint but rather because my company uses every technique detailed in it.

Applying This Book to Access 2 and Access 95

If you've not migrated to Access 97 from Access 2, about 75 percent of this book is still useful to you. Almost all of the theoretical discussions in Part I, "The Leszynski Development Framework," apply to Access 2 development exactly as they are worded or with very minor modifications. Roughly half of the technique topics in Part II, "Expert Solutions Techniques," will still apply to Access 2 with the remainder specific to Access 97's enhancements.

Access 95 users will find that many features were left unchanged between the 95 and 97 versions, and thus the majority of topics in this book are applicable with no modification.

If you'd like to upgrade from Access 2 or Access 95 before using this book, use the guidelines provided in Appendix D, "Converting Existing Access Applications."

Applying This Book to Other Microsoft Products

Because this book strikes a balance between the theory of development and the technique, much of its content can be extrapolated from Access into other environments. Indeed, I know very few Access developers that are not Office users, Visual Basic users, or FoxPro users-sometimes using all of these platforms together. Once you've read and understand the methodologies in Part I of the book, you will be able to apply them from Access to your other development tools with little to no modification. For example, the discussion of application testing in Chapter 9, "Completing and Deploying a Solution," is relevant to any applications that use forms to collect information. Whether you are deploying your solution in Access, Excel, Visual Basic, or FoxPro, the approach detailed in that chapter will provide you with value.

If you use or mix both Access and Visual Basic, you will find this book especially useful because both of its major segments are appropriate to both tools. Because these two products have so many features in common (the use of forms, the Jet Database Engine, the Basic language, and so on), the development theory elements that Part I describes apply to your Visual Basic projects in an almost word-for-word fashion.

However, the real bonus comes in Part II. The tips and techniques for creating Jet databases, for coding conventions, for form data validation, for multi-user applications, and for code libraries can be applied to Visual Basic verbatim or with very few syntax or structure changes. Other topics in Part II, such as working with forms and client/server data, are implemented differently in Visual Basic than in Access. Nevertheless, with some modifications, the techniques in those topics can also be made applicable to Visual Basic.

Defining the Terms Used in This Book

In addition, I also use the following terms and abbreviations throughout the book:

When I use the term "client" in my speaking engagements, listeners often come up to me afterward and say, "I work in Information Technology, so my users are technically not my clients. Your terminology made me feel like the speech was oriented toward outside consultants rather than in-house developers."

In fact, many Information Technology groups that we work with have standardized the term "client" to describe the application users within their company that they develop for. Describing users with this term adds a tone of seriousness and professionalism to the relationship between developers and users, even within the same firm.

Understanding Other Conventions Used in This Book

This book uses standardized conventions in the following areas:

Dim intWork As Integer

Call subname (argument1, argument2, ...)

From Here...

This introduction describes the organization of Access 97 Expert Solutions, the value you receive from it, and how to apply it beyond only Access 97. Because the flow from theory to technique as described here is much akin to crawling before walking, you should read Part I of this book, "The Leszynski Development Framework," if you can before reading Part II, "Expert Solutions Techniques."

1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.