Access 2007: The Missing Manual

931 Pages·2006·43.618 MB·English

by  Matthew MacDonald

Access 2007: The Missing Manual
By Matthew MacDonald
...............................................
Publisher: O'Reilly
Pub Date: December 01, 2006
ISBN-10: 0-596-52760-8
ISBN-13: 978-0-596-52760-0
Pages: 600

Table of Contents | Index
Compared to industrial-strength database products such as Microsoft's SQL Server, Access is a
breeze to use. It runs on PCs rather than servers and is ideal for small- to mid-sized businesses and
households. But Access is still intimidating to learn. It doesn't help that each new version crammed
in yet another set of features; so many, in fact, that even the pros don't know where to find them
all. Access 2007 breaks this pattern with some of the most dramatic changes users have seen since
Office 95. Most obvious is the thoroughly redesigned user interface, with its tabbed toolbar (or
"Ribbon") that makes features easy to locate and use. The features list also includes several long-
awaited changes. One thing that hasn't improved is Microsoft's documentation. To learn the ins and
outs of all the features in Access 2007, Microsoft merely offers online help.
Access 2007: The Missing Manual was written from the ground up for this redesigned application.
You will learn how to design complete databases, maintain them, search for valuable nuggets of
information, and build attractive forms for quick-and-easy data entry. You'll even delve into the
black art of Access programming (including macros and Visual Basic), and pick up valuable tricks
and techniques to automate common tasks -- even if you've never touched a line of code before.
You will also learn all about the new prebuilt databases you can customize to fit your needs, and
how the new complex data feature will simplify your life. With plenty of downloadable examples, this
objective and witty book will turn an Access neophyte into a true master.
Access 2007: The Missing Manual
By Matthew MacDonald
...............................................
Publisher: O'Reilly
Pub Date: December 01, 2006
ISBN-10: 0-596-52760-8
ISBN-13: 978-0-596-52760-0
Pages: 600

Table of Contents | Index
Copyright
The Missing Credits
Introduction
Part I: Storing Information in Tables
Chapter 1. Creating Your First Database
Section 1.1. Understanding Access Databases
Section 1.2. Getting Started
Section 1.3. Saving and Opening Access Databases
Section 1.4. The Navigation Pane
Chapter 2. Building Smarter Tables
Section 2.1. Understanding Data Types
Section 2.2. Design View
Section 2.3. Access Data Types
Section 2.4. The Primary Key
Section 2.5. Six Principles of Database Design
Chapter 3. Mastering the Datasheet: Sorting, Searching, Filtering, and More
Section 3.1. Datasheet Customization
Section 3.2. Datasheet Navigation
Section 3.3. Advanced Editing
Section 3.4. Printing the Datasheet
Chapter 4. Blocking Bad Data
Section 4.1. Data Integrity Basics
Section 4.2. Input Masks
Section 4.3. Validation Rules
Section 4.4. Lookups
Chapter 5. Linking Tables with Relationships
Section 5.1. Relationship Basics
Section 5.2. Using a Relationship
Section 5.3. More Exotic Relationships
Section 5.4. Relationship Practice
Part II: Manipulating Data with Queries
Chapter 6. Queries That Select Records
Section 6.1. Query Basics
Section 6.2. Creating Queries
Section 6.3. Queries and Related Tables
Chapter 7. Essential Query Tricks
Section 7.1. Calculated Fields
Section 7.2. Query Functions
Section 7.3. Summarizing Data
Section 7.4. Query Parameters
Chapter 8. Queries That Update Records
Section 8.1. Understanding Action Queries
Section 8.2. Update Queries
Section 8.3. Append Queries
Section 8.4. Delete Queries
Section 8.5. Tutorial: Flagging Out-of-Stock Orders
Chapter 9. Analyzing Data with Crosstab Queries and Pivot Tables
Section 9.1. Understanding Crosstab Queries
Section 9.2. Creating Crosstab Queries
Section 9.3. Pivot Tables
Section 9.4. Pivot Charts
Part III: Printing Reports
Chapter 10. Creating Reports
Section 10.1. Report Basics
Section 10.2. Printing, Previewing, and Exporting a Report
Section 10.3. Formatting a Report
Section 10.4. Filtering and Sorting a Report
Chapter 11. Designing Advanced Reports
Section 11.1. Improving Reports in Design View
Section 11.2. The Report Wizard
Section 11.3. The Label Wizard
Section 11.4. Fine-Tuning Reports with Properties
Section 11.5. Expressions
Section 11.6. Grouping
Part IV: Building a User Interface with Forms
Chapter 12. Creating Simple Forms
Section 12.1. Form Basics
Section 12.2. Sorting and Filtering in a Form
Section 12.3. Creating Better Layouts
Section 12.4. The Form Wizard
Chapter 13. Designing Advanced Forms
Section 13.1. Customizing Forms in Design View
Section 13.2. Taking Control of Controls
Section 13.3. Forms and Linked Tables
Chapter 14. Building a Navigation System
Section 14.1. Mastering the Navigation Pane
Section 14.2. Building Forms with Navigation Smarts
Section 14.3. Linking to Related Data
Part V: Programming Access
Chapter 15. Automating Tasks with Macros
Section 15.1. Macro Essentials
Section 15.2. Macros and Security
Section 15.3. Three Macro Recipes
Section 15.4. Managing Macros
Section 15.5. Connecting Macros to Forms
Section 15.6. Conditional Macros
Chapter 16. Automating Tasks with Visual Basic
Section 16.1. The Visual Basic Editor
Section 16.2. Putting Code in a Form
Section 16.3. Understanding Objects
Section 16.4. Using Objects
Chapter 17. Writing Smarter Code
Section 17.1. Exploring the VB Language
Section 17.2. Dealing with Trouble
Section 17.3. Deeper into Objects
Section 17.4. Using VB to Run a Better Business
Part VI: Sharing Access with the Rest of the World
Chapter 18. Sharing a Database with Multiple Users
Section 18.1. Opening Up Your Database to the World
Section 18.2. Preparing Your Database
Section 18.3. Playing Well with Others
Section 18.4. Data Corruption
Section 18.5. Securing Your Database
Chapter 19. Importing and Exporting Data
Section 19.1. Case for Importing and Exporting
Section 19.2. Using the Clipboard
Section 19.3. Import and Export Operations
Section 19.4. Access and XML
Section 19.5. Collecting Info by Email
Chapter 20. Connecting Access to SQL Server
Section 20.1. Should You Switch to SQL Server?
Section 20.2. Getting Started: SQL Server 2005 Express
Section 20.3. Creating a SQL Server Database
Section 20.4. Adding Objects to a SQL Server Database
Chapter 21. Connecting Access to SharePoint
Section 21.1. Understanding SharePoint
Section 21.2. Setting Up SharePoint
Section 21.3. SharePoint and Access
Part VII: Appendix
Appendix A. Customizing the Quick Access Toolbar
Section A.1. The Quick Access Toolbar
Colophon
Index

Access 2007: The Missing Manual
by Matthew McDonald
Copyright © 2007 O'Reilly Media, Inc. All rights reserved.
Printed in the United States of America.
Published by O'Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.
O'Reilly books may be purchased for educational, business, or sales promotional use. Online editions
are also available for most titles (safari.oreilly.com). For more information, contact our
corporate/institutional sales department: [phone] or [email protected].
Table
Printing History:
December 2006: First Edition.
Nutshell Handbook, the Nutshell Handbook logo, the O'Reilly logo, and "The book that should have
been in the box" are registered trademarks of O'Reilly Media, Inc. Access 2007: The Missing Manual,
The Missing Manual logo, Pogue Press, and the Pogue Press logo are trademarks of O'Reilly Media,
Inc.
Many of the designations used by manufacturers and sellers to distinguish their products are claimed
as trademarks. Where those designations appear in this book, and O'Reilly Media, Inc. was aware of
a trademark claim, the designations have been printed in caps or initial caps.
While every precaution has been taken in the preparation of this book, the publisher and author
assume no responsibility for errors or omissions, or for damages resulting from the use of the
information contained herein.
This book uses RepKover™, a durable and flexible lay-flat binding.
ISBN-10: 0-596-52760-8
ISBN-13: 978-0-596-52760-0
[M]
The Missing Credits
About the Author
Matthew MacDonald is an author and programmer extraordinaire. He's the author of
Excel 2007: The Missing Manual, Creating Web Sites: The Missing Manual, and over a dozen books
about programming with the Microsoft .NET Framework. In a dimly remembered past life, he studied
English literature and theoretical physics.
About the Creative Team
Peter Meyers (editor) works as an editor at O'Reilly Media on the Missing Manual series. He lives
with his wife and cats in New York City. Email: [email protected].
Juel Bortolussi (technical reviewer) has worked as an Access database developer for inventory and
asset management systems in the design, beverage, and publishing industries. She thinks this book
would make a great classroom textbook, providing students with database, Visual Basic, SQL, and
SharePoint skills. Email: [email protected]
Michael Schmalz (technical reviewer) works in banking and performs business and technology
consulting in a variety of industries. He has done technical editing for O'Reilly on Microsoft Office
books. Michael has a degree in finance from Penn State. He lives with his wife and daughter in
Pennsylvania.
Sohaila Abdulali (copy editor) is a freelance writer and editor. She has published a novel, several
children's books, and numerous short stories and articles. She recently finished an ethnography of an
aboriginal Indian woman. She lives in New York City with her husband Tom and their small but
larger-than-life daughter, Samara. She can be reached through her Web site at www.sohailaink.com.
Jill Steinberg (copy editor) is a freelance writer and editor based in Seattle, and has produced
content for O'Reilly, Intel, Microsoft, and the University of Washington. Jill was educated at Brandeis
University, Williams College, and Stanford University. Email: [email protected].
Acknowledgements
Writing a book about a program as sprawling and complex as Access is a labor of love (love of pain,
that is). I'm deeply indebted to a whole host of people, including those who helped me track down all
the neat and nifty things you can do with the latest version of Office (including bloggers
extraordinaire Jensen Harris and Erik Rucker), those who kept the book clear, concise, and
technically accurate (Peter Meyers, Sarah Milstein, Brian Sawyer, Juel Bortolussi, and Michael
Schmalz), and those who put up with me while I wrote it (more on that in a moment). I also owe
thanks to many people who worked to get this book formatted, indexed, and printedyou can meet
many of them on the Missing Credits page.
Completing this book required a few sleepless nights (and many sleep-deprived days). I extend my
love and thanks to my daughter Maya, who put up with it without crying most of the time; my dear
wife Faria, who mostly did the same; and our moms and dads (Nora, Razia, Paul, and Hamid), who
contributed hours of babysitting, tasty meals, and general help around the house that kept this book
on track. So thanks everyonewithout you half of the book would still be trapped inside my brain!
The Missing Manual Series
Missing Manuals are witty, superbly written guides to computer products that don't come with printed
manuals (which is just about all of them). Each book features a handcrafted index.
Recent and upcoming titles include:
Access 2003 for Starters: The Missing Manual by Kate Chase and Scott Palmer
Access 2007 for Starters: The Missing Manual by Matthew MacDonald
AppleScript: The Missing Manual by Adam Goldstein
AppleWorks 6: The Missing Manual by Jim Elferdink and David Reynolds
CSS: The Missing Manual by David Sawyer McFarland
Creating Web Sites: The Missing Manual by Matthew MacDonald
Digital Photography: The Missing Manual by Chris Grover and Barbara Brundage
Dreamweaver 8: The Missing Manual by David Sawyer McFarland
eBay: The Missing Manual by Nancy Conner
Excel 2003 for Starters: The Missing Manual by Matthew MacDonald
Excel 2003: The Missing Manual by Matthew MacDonald
Excel 2007 for Starters: The Missing Manual by Matthew MacDonald
Excel 2007: The Missing Manual by Matthew MacDonald
FileMaker Pro 8: The Missing Manual by Geoff Coffey and Susan Prosser
Flash 8: The Missing Manual by E. A. Vander Veer
Front 2003: The Missing Manual by Jessica Mantaro
GarageBand 2: The Missing Manual by David Pogue
Google: The Missing Manual, Second Edition by Sarah Milstein, J.D. Biersdorfer, and Matthew
MacDonald
Home Networking: The Missing Manual by Scott Lowe
iMovie HD 6: The Missing Manual by David Pogue
iPhoto 6: The Missing Manual by David Pogue
iPod: The Missing Manual, Fifth Edition by J.D. Biersdorfer
Mac OS X: The Missing Manual, Tiger Edition by David Pogue
Office 2004 for Macintosh: The Missing Manual by Mark H. Walker and Franklin Tessler
PCs: The Missing Manual by Andy Rathbone
Photoshop Elements 5: The Missing Manual by Barbara Brundage
PowerPoint 2007 for Starters: The Missing Manual by E. A. Vander Veer
PowerPoint 2007: The Missing Manual by E. A. Vander Veer
QuickBooks 2006: The Missing Manual by Bonnie Biafore
Quicken for Starters: The Missing Manual by Bonnie Biafore
Switching to the Mac: The Missing Manual, Tiger Edition by David Pogue and Adam Goldstein
The Internet: The Missing Manual by David Pogue and J.D. Biersdorfer
Windows 2000 Pro: The Missing Manual by Sharon Crawford
Windows XP for Starters: The Missing Manual by David Pogue
Windows XP Home Edition: The Missing Manual, Second Edition by David Pogue
Windows XP Pro: The Missing Manual, Second Edition by David Pogue, Craig Zacker, and Linda
Zacker
Windows Vista: The Missing Manual by David Pogue
Windows Vista for Starters: The Missing Manual by David Pogue
Word 2007 for Starters: The Missing Manual by Chris Grover
Word 2007: The Missing Manual by Chris Grover
Introduction
In the past, people have tried a variety of techniques to organize information. They've used
Rolodexes, punch cards, cardboard boxes, vertical files, Post-it notes, 10,000-page indexes, and
(when all else failed) large piles on top of flat surfaces. But after much suffering, people discovered
that computers were far better at dealing with information, especially when that information's large,
complex, or changes frequently.
That's where Microsoft Access comes into the picture. Access is a tool for managing databases
carefully structured catalogs of information (or data ). Databases can store just about any type of
information, including numbers, pages of text, and pictures. Databases also range wildly in sizethey
can handle everything from your list of family phone numbers to a ginormous product catalog for
Aunt Ethel's Discount Button Boutique.
In this book, you'll learn how to design complete databases, maintain them, search for valuable
nuggets of information, and build attractive forms for quick and easy data entry. You'll even delve
into the black art of Access programming , where you'll pick up valuable tricks and techniques that
you can use to automate common tasks, even if you've never touched a line of code before.
Best of all, this book was written from scratch for Access 2007, the latest and greatest incarnation of
Microsoft's bestselling database software. Access 2007's quite a bit different from previous versions,
with a slick new interface that has computer geeks buzzing. And for once, it's not just a gimmick. As
you'll see in this book, once you master Access's new style, you'll be able to build great databases in
record time.
What You Can Do with Access
The modern world is filled with information. A Web search for a ho-hum topic like "canned carrots"
nets more than a million Web pages. As a result, it's no surprise that people from all walks of life
need great tools to store and manage information.
It's impossible to describe even a fraction of the different databases that Access fans create every
day. But just to get you thinking like a database maven, here are some common types of information
that you can store handily in an Access database:
Catalogs of books, CDs, rare wine vintages, risqué movies, or anything else you want to collect
and keep track of
Mailing lists that let you keep in touch with friends, family, and co-workers
Business information, like customer lists, product catalogs, order records, and invoices
Lists of guests and gifts for weddings and other celebrations