Access Solutions Tips, Tricks, and Secrets from Microsoft Access MVPs

578 Pages·2012·7.56 MB·English

access® Solutions
Tips, Tricks, and Secrets from
® ®
Microsoft Access MVPs
Arvin Meyer
Douglas J. Steele
Access® Solutions: Tips, Tricks, and Secrets from Microsoft®Access® MVPs
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2010 by Arvin Meyer and Douglas J. Steele
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-59168-0
978-0-470-94662-6 (ebk)
978-0-470-94754-8 (ebk)
978-0-470-94756-2 (ebk)
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by
any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under
Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the
Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center,
222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permis-
sion should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ
07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or war-
ranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all
warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be
created or extended by sales or promotional materials. The advice and strategies contained herein may not
be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in
rendering legal, accounting, or other professional services. If professional assistance is required, the services
of a competent professional person should be sought. Neither the publisher nor the author shall be liable for
damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation
and/or a potential source of further information does not mean that the author or the publisher endorses the
information the organization or Web site may provide or recommendations it may make. Further, readers
should be aware that Internet Web sites listed in this work may have changed or disappeared between when
this work was written and when it is read.
For general information on our other products and services please contact our Customer Care Department
within the United States at [phone], outside the United States at [phone] or fax [phone].
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not
be available in electronic books.
Library of Congress Control Number: 2010932451
Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc.
and/or its affiliates, in the United States and other countries, and may not be used without written permis-
sion. Microsoft and Access are registered trademarks of Microsoft Corporation in the United States and/or
other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc. is not
associated with any product or vendor mentioned in this book.
To my girlfriend of over 30 years, who also happens to
be my wife, Susan, and to my five children, Tiffany, Daryn,
Laird, Zachary, and Aaron (in order of their birth), who have
made life worth living, and thus this book.
—Arvin
To my lovely and intelligent wife, Louise. Thanks for putting up
with me while I wrote this (and all the other times too!)
—Doug
Credits
Executive Editor Vice President and
Robert Elliott Executive Publisher
Barry Pruett
Senior Project Editor
Adaobi Obi Tulton Associate Publisher
Jim Minatel
Production Editor
Kathleen Wisor Project Coordinator, Cover
Lynsey Stanford
Copy Editor
Catherine Caffrey Compositor
Maureen Forys,
Editorial Director
Happenstance Type-O-Rama
Robyn B. Siesky
Proofreader
Editorial Manager
Louise Watson,
Mary Beth Wakefield
Word One New York
Freelancer Editorial Manager
Indexer
Rosemarie Graham
Johnna VanHoose Dinse
Marketing Manager
Cover Image
Ashley Zurcher
© Fuse/GettyImages
Production Manager
Cover Designer
Tim Tate
Ryan Sneed
Vice President and Executive
Group Publisher
Richard Swadley
iv
About the Authors
Arvin Meyer, although armed with a degree in business administration,
with a major in accounting, decided instead to go into the family cabinet-
making business. He got his first computer in 1981, and used computers
extensively in bidding and construction takeoffs. In 1992, after discovering
Windows 3.1, he began writing a computer column for a trade magazine.
When Microsoft Access came to the scene in the fall of that year, he was hooked. He
returned to college, studied programming and networking, and in 1994 wrote his first
successful commercial database. In 1996, Arvin became a full-time database developer.
Today he is married and the father of five children. Arvin is active in the Microsoft news-
groups and maintains the “Access Web” at www.mvps.org/access, an Access download
site on his domain www.datastrat.com, several client websites, and the MVP website at
www.accessmvp.com. A Microsoft Certified Professional and a Microsoft MVP for more
than 10 years, Arvin writes freelance technology articles, works as a business consultant,
and heads a successful database development company in Orlando, Florida, specializing
in Microsoft database technologies and mobile computing applications. Arvin can be
reached at [email protected].
Douglas J. Steele has been working with computers, both mainframe
and PC, for almost 40 years. (Yes, he did use punch cards in the begin-
ning!) For more than 30 years, Doug has worked for a large international
oil company. Databases and data modeling have been a focus for most
of that time, although recently he has been working on a desktop project
that will roll Windows 7 out to about 100,000 computers worldwide. Doug has authored
numerous articles on Access and has been recognized by Microsoft as an MVP (Most
Valuable Professional) for more than 10 years. Doug holds a Master’s degree in sys-
tem design engineering from the University of Waterloo (Ontario, Canada), where his
research centered on designing user interfaces for non-traditional computer users. (Of
course, this was in the late ’70s, so few people were traditional computer users at the
time!) This research stemmed from his background in music (he holds an Associateship
in Piano Performance from the Royal Conservatory of Music, Toronto). Doug is married
and the father of two daughters. He lives with his lovely wife in Toronto, Ontario. Doug
can be reached at [email protected].
v
About the Contributors
Alex Dybenko has been an Access developer since version 1.0. He is the founder of
Moscow MS Access User Group, www.arimsoft.ru/msaccess, working as Access/
VB/SQL/ASP.NET consultant at www.PointLtd.com. Alex collects Access tips, tricks,
and useful links on his blog at http://Accessblog.net and shares his knowledge
in public forums and social networks.
George Hepworth began his Access career by creating tools to track sales for the self-
study materials that he and his former colleagues created and sold, and to manage the
results of CE exams administered to licensees. Realizing that Access was more inter-
esting and rewarding than writing training materials, he moved to full-time Access
development. George founded and operated Grover Park Consulting, specializing in
Access databases for small and medium-sized organizations. He is currently a database
developer for Data and Domains, a development organization near Seattle, Washington.
George holds an MA in TESL and a BS in English from Utah State University. His
daughter and his money go to Washington State University.
Duane Hookom has a parks and recreation administration degree from the University
of Minnesota. He began writing database programs for the Eau Claire (Wisconsin) Parks
and Recreation Department back in the early 1980s. This led to a career change during a
mid-life crisis, resulting in full-time consulting. He now divides his work life between
Access, SQL Server, and Web solutions. Duane participates in several Access-related
news groups, forums, and list servers. He also has several popular Access demos at
various websites. His website is “Hook’D on Access” at www.access.hookom.net.
Albert D. Kallal is the owner of HCS consulting group and has been professionally
developing software for more than 20 years. His first major project started while study-
ing computer science at the University of Alberta in Edmonton, Canada. That project
was the basis for Omni-sim, the first successful commercial authoring system that
allowed educators to create applications without having to write code. His software is
currently used in many countries around the world, on platforms ranging from PDAs
to mainframes. Always seeking to stay on the leading edge, Albert was an early beta
tester for the new Access 2010 Web Services.
vi
Acknowledgments
In some ways, writing the book was the easy part. The hard part is making sure not to
forget to thank everyone who helped in its development!
We had been talking about writing a book together for years, but there was always
some reason why we didn’t get around to it. Finally, the opportunity coalesced and we
couldn’t find a reason to put it off any longer.
First, we thank Adaobi Obi Tulton, our project editor, for doing a superb job of pull-
ing everything together, as well as for putting up with us when we’d get off on a long
esoteric debate (usually initiated by Doug) over some technical point that really didn’t
matter in the grander scheme of things.
A special thanks to the others who helped put this book together: Alex Dybenko, MVP
from Moscow, Russia, was instrumental in helping with Tip 20; Duane Hookom, MVP
from Minneapolis, Minnesota, contributed Tips 21 and 22; Albert D. Kallal, MVP from
Edmonton, Canada, contributed Tips 1, 36, and 37; and George Hepworth, MVP from
Seattle, Washington, contributed Tip 35.
We also must thank MVP Armen Stein of J-Street Technology in Seattle and Larry
Strange of AccessHosting.com for providing support and Web hosting for SharePoint
services.
We’d be remiss if we didn’t mention our other fellow Access MVPs, both current and
previous. While we only solicited contributions from a limited number, we’ve learned
many things over the years from all of them. Although we’re always told to be careful
of connecting with people we’ve only met over the Internet, that’s how all of us met in
the first place, and the Access MVPs really have become family to us.
Finally, we thank former Access MVP extraordinaire Stephen Lebans for all the help
he’s provided over the years. Not that we want to rub it in, but see, we got published
before you. (Who are we kidding: Of course, we’re rubbing it in!)
vii