Grover Park George On Access: Unleash the Power of Access

397 Pages·2004·21.9 MB·English

by  George Hepworth

< Day Day Up >
Back Cover
This book concentrates on Access versions 2000, 2002/XP, and 2003; the examples in it were created in Access
2002. I have seen estimates that Access ’97 still makes up somewhere between one-fourth and one-third of existing
installations. I also suspect it probably accounts for an even larger percentage of working Access databases. Still,
after some deliberation, I elected to focus on the newer versions of Access in this book. For one thing, this book is
primarily intended for Access users just starting out, so I assume most of you will be using one of the newer versions
(2000/2002/2003). For another, this book is mostly about using Access to create reliable, flexible, and scalable
relational databases; the concepts underlying that goal generally do not depend on which version of Access you use.
As you incorporate more advanced features into your databases, of course, the differences between versions become
more important.
About the Author
George Hepworth first discovered relational databases in 1994 when he received a copy of Access 2.0 as a reward for
participating in a Microsoft usability test. A teacher and instructional designer by training and experience, at first he
was hopelessly confused by the strange new way the accompanying Access 2.0 Users’ Manual threw around terms
like “form”, “record”, “query” and “table”. Figuring it all out required a combination of persistence, patience, trial and
error, dumb luck and, as a last resort, formal classes and books. Eventually, Mr. Hepworth felt comfortable enough
with his hard-won knowledge of databases in general, and Access in particular, to try making a living at it.
< Day Day Up >
< Day Day Up >
Grover Park George on Access
Copyright © 2003 by George Hepworth
All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage retrieval system
without written permission from the publisher.
Written by:
George Hepworth a/k/a “Grover Park George”
Edited by:
Linda H. DeLonais
Cover Design:
IRubin Consulting
Published by:
Holy Macro! Books
13386 Judy Avenue Northwest
Uniontown, Ohio, USA 44685
Distributed by:
Holy Macro! Books
First printing:
April 2004
Printed in Hong Kong
Library of Congress Data:
Hepworth, George
Grover Park George on Access / George Hepworth
LCCN: 2004101505
ISBN:
0-9724258-9-6
Trademarks:
All brand names and product names used in this book are trade names, service marks, trademarks, or
registered trademarks of their respective owners. Holy Macro! Books is not associated with any product or
vendor mentioned in this book.
About the Author
George first discovered relational databases in 1994 when he received a copy of Access 2.0 as a reward for
participating in a Microsoft usability test. A teacher and instructional designer by training and experience, at
first he was hopelessly confused by the strange new way the accompanying Access 2.0 Users’ Manual threw
around terms like “form”, “record”, “query” and “table”. Figuring it all out required a combination of persistence,
patience, trial and error, dumb luck and, as a last resort, formal classes and books. Eventually, Mr. Hepworth
felt comfortable enough with his hard-won knowledge of databases in general, and Access in particular, to try
making a living at it. He still does, offering his services at www.gpcdata.com. He also takes advantage of
every opportunity to learn something new, which is pretty much a daily occurrence.
Mr. Hepworth and his wife, Yolanda, live in Mountlake Terrace, Washington with their daughter, Lyndsey.
Acknowledgements
I would like to thank the following persons for their contributions to this book.
First, thanks to all the regulars at Utter Access, the best Access help forum on the Internet. Your professional
influence has made me a better developer in many ways. I couldn’t possibly name you all, but I have to
mention Gord, who keeps it all running, Jerry who finally made me understand normalization, Dana, whose
early encouragement and humor helped keep me going, and Anthony, whose enthusiasm and energy are a
constant inspiration to me to get better at what I do.
UA rocks!
Very special thanks to a couple of long-time friends and mentors from early on in my career, Rex Moyer and
Traci Dysart. Their patience and willingness to share their knowledge and experience paved the way for me in
many ways. Thanks guys.
Free tech support sites I frequent regularly:
Utter Access at http://www.utteraccess.com/forums
The Office Experts at http://www.theofficeexperts.com/forum
Experts Exchange at http://www.experts-exchange.com
< Day Day Up >
< Day Day Up >
Foreword
Make databases simple and easy to use? You gotta be kidding me. Ain’t no way, no how.
My friends have been deluded into thinking I am something of a computer expert. The truth of the matter is
that I made so many computer related mistakes over the years that eventually I was bound to get something
right! The trick is that if something does go right then make sure someone is there to see it happen. Combine
the two events, finally get it right with someone being there to see it and voila, “Computer Expert.”
Being recognized as an “Expert” is easy until someone comes along asking you for help. You have a couple of
choices at this point: 1. You can tell the person that you would love to help but you have such a back load of
other projects that it will be sometime before you will be able to help. If you are lucky, the person will go to
someone else or forget about it, leaving your reputation intact. 2. Try to understand the nature of the problem
and call someone who knows. I use this one all of the time. or 3. Well there probably is a three but I’ve been
so successful with one and two that I have not needed a three.
The secret of my success, “Say, wouldn’t that be a great title for a book or movie?” is the author of this book,
Mr. George Hepworth. George and I have been friends for more years than I can remember. It has only been
the last few years when I discovered the untapped treasure of knowledge; he has about designing,
developing, and improving databases.
For years, I had struggled with DBase III and IV (I told you it had been a lot of years), Borland’s Paradox (now
Corel), Lotus’ Approach and the industry giant, MS Access. Each of the databases has many nifty templates
and Wizards but when it came right down to it there were none that met my specific needs. Attempts to edit
the database resulted in some rather creative applications that simply did not work. I would move from one
software program to another looking for “The One” that was “User Friendly”, translated -- a program I could
use without reading the instructions. Form view in Access allows one to re-arrange form fields to create
wonderful looking reports. I have some of the best looking reports around, now if they would only work.
From reading, I knew that databases offered much more flexibility than spreadsheets. Trying a variety of
databases convinced me that it must be possible to create a database that would do all that I wanted. The
problem lay in trying to create a working database, in this millennium.
After many failed attempts and a newfound expertise in adapting spreadsheets, George mentioned that he
designed and developed databases. I was determined to see into which of the “Expert” categories above he
fit. George is the person from whom one asks for help.
Over the years, I’ve abused our friendship by asking for help on a number of projects. I’m constantly amazed
at how quickly he grasps the intent of my quest despite the imprecision in which it is stated. Not only is the
concept readily grasped but it is quickly converted to an actual working database, with enhancements! Oh, joy!
Oh, rapture!
George also has the ability to quickly and concisely explain what needs to be done and most importantly, why
it needs to be done. Not only do you get a solution to your database problem but you get an education as well
- An education that helps you to develop new skills and understanding. The best part, for me, is that I have yet
to receive a bill for the many hours of labor he has performed on my behalf. Thanks Amigo.
Note: George has a family and needs to pay his bills. Please do not expect to get his undivided attention for
free. You can benefit from his advice by visiting http://www.utteraccess.com/forums where he is an active
participant and a genuine expert. You can also visit his website http://www.gpcdata.com for free sample
databases or to request a custom database solution.
You’ve taken the first step in becoming a database expert by selecting this book.
Nope, I was not paid to write this foreword it is my distinct pleasure. Well, maybe it is a little payback for the
countless hours spent on my behalf.
Bottom-line, if I can understand how to develop databases from reading this book, anyone can.
~ Bruce Garrett
Jacksonville, Oregon
August 2003
< Day Day Up >
< Day Day Up >
Chapter 1: What This Book Will Do for You
On more than one occasion, I’ve worked with programmers and database developers who dismissed Access
as a “toy database”. I don’t hold it against them and I certainly can’t argue against the advantages of Oracle or
SQL Server for managing corporate Data Warehouses and Customer Relationship Management systems. The
truth is many, many Fortune 100 corporations, small mom-and-pop businesses, schools and other non-profit
groups, and ordinary individuals use Access to store and report on a huge variety of data in an amazing
number of environments. Not one of them considers Access a toy because it does the job they need it to do.
That’s basically what this book is all about; through a combination of examples, explanations and do-it-yourself
exercises, I will help you learn how to use Access to control and manage the information that matters to you.
Why You Need this Book
I assume most people who have bought this book or are reading it for the first time have a specific project in
front of them. Therefore, I wrote the book with that in mind. It follows the steps I would follow in evaluating a
new database project, designing, building, implementing, and evaluating the database for a client or myself. If
you’re looking for specific tips on creating queries, for example, you won’t find a query chapter; instead you’ll
find discussions of queries in several places: creating interfaces for entering new data, providing for lookup of
existing data, and creating reports. Use the Index at the back of this book to pursue specific topics.
A Professional Approach
In leading you through the development process, I will share with you my insights into the most productive and
professional ways to accomplish those tasks. In other words, if you just want to hack out a quick database and
you don’t really care much about how you go about it, this book is not for you.
I tried to select examples for the book that would be familiar to you, maybe even useful if you complete them
yourself. I also tried to select examples simple enough to complete in a reasonably short period of time. This
doesn’t mean, however, that what you will learn is simplistic in any way. On the contrary, keeping the subject
matter simple allows us to concentrate on the underlying principles so that you can really understand both
what you need to do to create an Access database and why you need to do it.
< Day Day Up >
< Day Day Up >
What’s in this Book
This book concentrates on Access versions 2000, 2002/XP, and 2003; the examples in it were created in
Access 2002. I have seen estimates that Access ’97 still makes up somewhere between one-fourth and one-
third of existing installations. I also suspect it probably accounts for an even larger percentage of working
Access databases. Still, after some deliberation, I elected to focus on the newer versions of Access in this
book. For one thing, this book is primarily intended for Access users just starting out, so I assume most of you
will be using one of the newer versions (2000/2002/2003). For another, this book is mostly about using Access
to create reliable, flexible, and scalable relational databases; the concepts underlying that goal generally do
not depend on which version of Access you use. As you incorporate more advanced features into your
databases, of course, the differences between versions become more important. Therefore, as we get further
into the book, we’ll make clear any differences between versions where appropriate.
Finally, I’ve been working with Access 2003 for some months. Although there are some interesting new
features, I haven’t found anything compelling enough to suggest you run out and upgrade if you haven’t
already. Smart Tags, for example, which have been available in other members of the MS Office family for
some time, are an interesting addition to Access. Still, they tend to fall into the “so what” category in my
opinion. On the other hand, there are some enhancements of interest to more advanced developers, but
frankly, most of them are beyond the scope of this book anyway. So if you have Access ’97, 2000, or 2002,
you’ll do just fine with this book.
< Day Day Up >
< Day Day Up >
Get Normal
Officially, this is a book on using Access. Much of the book is devoted to defining, building, and using the
objects in an Access database. However, I feel quite strongly that it would be entirely unprofessional for me
not to make a concerted effort to help you understand some fundamental concepts about relational databases
to prepare you for that effort. The first and most important, concept is that the tables in your database must be
normalized. You may not know what that means yet, but you will after you finish reading this book!
I can’t make this point strongly enough.
To be useful, your database has to be properly normalized.
No combination of clever interface design, sophisticated SQL queries or complex VBA code can ever
compensate for an un-normalized database. If you learn nothing else from this book, learn what it means to
normalize your databases and implement that knowledge in your databases.
< Day Day Up >
< Day Day Up >
How to Use this Book
If you wish, you can use this book like a self-study course, working your way through the sections and
chapters, one at a time, building your skills as you go. If you prefer to skip around, looking for tips on dealing
with issues such as naming conventions, table design, sub-queries, VBA code or whatever, please feel free to
do so. In any case, the purpose of this book is to help you accomplish one thing: building Access tools that
work for you.
You are welcome, even encouraged, to email me your comments or suggestions about this book. I will do my
best to answer each and every email. And if I really like your suggestion, I’ll see that it gets “recycled” into the
next edition.
[email protected]
< Day Day Up >