Access Solutions E-Book

Arvin Meyer

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 521

Veröffentlichungsjahr: 2010

Beschreibung

Two Microsoft Access MVPs show how you can become an Access power user * Microsoft Access is the world's leading database system, with millions of users and hundreds of thousands of developers. The best practices, tips, and techniques in this book can turn users into power users. * Millions of eager users make Access the most popular database system in the world These Microsoft MVPs exploit key features in Access, providing advice on techniques for capturing, sharing and reporting Access data. * Each tip provides detailed solutions with clear instructions for implementation, and samples of all can be found on the companion Web site Access 2010 Solutions offers professional advice that enables every Access user to get greater value from the Access database system.

Leseprobe

Table of Contents

Cover

Title Page

Copyright

Dedication

Credits

About the Authors

About the Contributors

Acknowledgments

Introduction

Overview of the Book and Technology

>How This Book Is Organized

Who Should Read This Book

Tools You Will Need

What’s on the Website

Development Standards

Naming Conventions

Requirements Analysis

Summary

Part I: Tables

Chapter 1

Objective

Scenario

Tables

(Web) Queries

(Web) Forms

(Web) Reports

(Web) Macros

Modules

Using This Tip

Additional Information

Chapter 2

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 3

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Part II: Queries

Chapter 4

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 5

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 6

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Part III: Forms

Chapter 7

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 8

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 9

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 10

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 11

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 12

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 13

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 14

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 15

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 16

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 17

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Part IV: Taking Advantage of Report Capabilities

Chapter 18

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 19

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 20

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 21

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 22

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Part V: Using VBA

Chapter 23

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 24

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 25

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 26

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 27

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 28

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 29

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Part VI: Automating Applications and ActiveX Controls

Chapter 30

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 31

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 32

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 33

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 34

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Part VII: Access and the Web

Chapter 35

Objective

Scenario

Working with SharePoint

Tables

Queries

Forms

Reports

Macros

Publishing a SharePoint Database

Modules

Using This Tip

Additional Information

Chapter 36

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 37

Objective

Scenario

Tables

(Web) Queries

(Web) Forms

(Web) Reports

(Web) Macros

Modules

Using This Tip

Additional Information

Chapter 38

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 39

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Part VIII: Utilities

Chapter 40

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 41

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 42

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Chapter 43

Objective

Scenario

Tables

Queries

Forms

Reports

Macros

Modules

Using This Tip

Additional Information

Index

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 permission 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 warranties 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 permission. 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

Robert Elliott

Senior Project Editor

Adaobi Obi Tulton

Production Editor

Kathleen Wisor

Copy Editor

Catherine Caffrey

Editorial Director

Robyn B. Siesky

Editorial Manager

Mary Beth Wakefield

Freelancer Editorial Manager

Rosemarie Graham

Marketing Manager

Ashley Zurcher

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Barry Pruett

Associate Publisher

Jim Minatel

Project Coordinator, Cover

Lynsey Stanford

Compositor

Maureen Forys, Happenstance Type-O-Rama

Proofreader

Louise Watson, Word One New York

Indexer

Johnna VanHoose Dinse

Cover Image

© Fuse/GettyImages

Cover Designer

Ryan Sneed

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 cabinetmaking 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 newsgroups 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 beginning!) 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 system 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].

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 interesting 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 studying 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.

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 pulling 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!)

Introduction

Doug and I have known each other online for many years. We both became Microsoft MVPs about 10 years ago, and have been friends ever since. Both of us had been writing for magazines for years. Although we’d been talking about writing a book together for about five years now, there had always been an impediment, usually work, and it hadn’t happened. Finally Wiley approached us with an offer and we decided that we couldn’t put it off any longer. This book is the result.

Our original list of tips and Secrets expanded as we started writing. We soon realized that we’d never fit everything in a single book, so we pared down the list of tips to a more manageable number. We still have many more tips than we have included, so if you like these, be on the lookout for our next book.

Overview of the Book and Technology

Database developers are much more than programmers. We solve complex business problems and facilitate work flow. Most books on databases concentrate on design or operation. This book doesn’t do that. Books on Access usually concentrate on how Access works, in addition to the aforementioned. We don’t do that either.

What is covered in this book: Solutions to business problems that we’ve solved over the years. Most of these solutions work in every version of Access, although some solutions may require code alteration to work in the earliest versions of Access. Much of the work can be directly imported and used, as is, in your applications.

We’ve also included the latest techniques for using Access with SharePoint. Access2010 was built to work well with SharePoint, and is arguably the easiest and best method to use for working in that area.

How This Book Is Organized

We organized the book into the following parts:

Part I: TablesPart II: QueriesPart III: FormsPart IV: Taking Advantage of Report CapabilitiesPart V: Using VBAPart VI: Automating Applications and Active X ControlsPart VII: Access and the WebPart VIII: Utilities

We did it this way because it seemed like the natural order of Access objects and work flow. The number of tips in each section varies. There is no dependency between the tips, so each tip stands on its own, although some tips relate to others.

Our approach mirrors the way many professionals work, and also makes it easier for non-professionals to find what they are looking for. The Table of Contents is your friend in this book, especially if you decide to use the book as designed and not read it from cover to cover.