Microsoft Access 2010 Inside Out

1910 Pages·2010·89.92 MB·English

by  Jeff Conrad

Microsoft Access 2010
® ®
Inside Out
Jeff Conrad
John Viescas
Published with the authorization of Microsoft Corporation by:
O’Reilly Media, Inc.
1005 Gravenstein Highway North
Sebastopol, California 95472
Copyright © 2010 Jeff Conrad and John Viescas.
Complying with all applicable copyright laws is the responsibility of the user. All rights reserved. Without limiting the
rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or
transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any
purpose, without express written permission of O’Reilly Media, Inc.
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 WCT 5 4 3 2 1 0
Microsoft Press titles may be purchased for educational, business or sales promotional use. Online editions are also
available for most titles (http://my.safaribooksonline.com). For more information, contact our corporate/institutional sales
department: [phone] or [email protected]. Visit our website at microsoftpress.oreilly.com. Send comments
to [email protected].
Microsoft, Microsoft Press, ActiveX, Excel, FrontPage, Internet Explorer, PowerPoint, SharePoint, Webdings, Windows,
and Windows 7 are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or
other countries. Other product and company names mentioned herein may be the trademarks of their respective owners.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people,
places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain
name, e-mail address, logo, person, place, or event is intended or should be inferred.
This book expresses the author’s views and opinions. The information contained in this book is provided without any
express, statutory, or implied warranties. Neither the author, O’Reilly Media, Inc., Microsoft Corporation, nor their
respective resellers or distributors, will be held liable for any damages caused or alleged to be caused either directly or
indirectly by such information.
Acquisitions and Development: Juliana Aldous and Kenyon Brown
Production Editor: Rachel Monaghan
Editorial Production: Custom Editorial, Inc. and Nancy Kotary
Technical Reviewers: Andrew Couch and Jim Bailie
Indexing: Potomac Indexing, LLC
Cover: Karen Montgomery
Compositor: Nellie McKesson
Illustrator: Robert Romano
978-0-735-62685-0
For my family, Cheryl, Amy, Aaron, and Arica, for your love and support.
You are the reason I have written this book and I couldn’t have done it
without you.
—Jeff Conrad
To Jeff, who adopted my book and made it his own.
—John Viescas
Paris, France
June 2010

Contents at a Glance
Part 1: Understanding Access Part 4: Creating Forms
Chapter 1 Chapter 12
What Is Access?. . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Using Forms in an Access Application ......737
Chapter 2 Chapter 13
Exploring the Access 2010 Interface .........21 Building a Form .........................785
Chapter 3 Chapter 14
Access 2010 Overview ....................125 Customizing a Form ......................841
Chapter 15
Part 2: Creating a Database and Tables
Advanced Form Design ...................945
Chapter 4
Designing Client Tables ...................167 Part 5: Working with Reports
Chapter 5 Chapter 16
Modifying Your Table Design .............237 Using Reports ..........................1023
Chapter 6 Chapter 17
Designing Web Tables ....................287 Constructing a Report ...................1045
Chapter 7 Chapter 18
Creating Table Data Macros ...............361 Advanced Report Design ................1107
Chapter 8
Part 6: Automating an Access
Importing and Linking Data ...............445
Application Using Macros
Part 3: Building Queries Chapter 19
Understanding Event Processing .........1167
Chapter 9
Creating and Working with Simple Queries .545 Chapter 20
Automating a Client Application Using
Chapter 10
Macros ................................1191
Building Complex Queries ................621
Chapter 21
Chapter 11
Automating a Web Application Using
Modifying Data with Action Queries .......703
Macros ................................1253
v
vi Contents at a Glance
Part 7: Working with the Web Part 9: After Completing Your
Application
Chapter 22
Using Web Applications in a Browser. . . . . .1287 Chapter 26
The Finishing Touches ...................1665
Chapter 23
Using Business Connectivity Services ......1347 Chapter 27
Distributing Your Application ............1715
Article 1
Appendix Designing Your Database Application .....1743
Installing Your Software .................1375
Article 2
Understanding SQL .....................1773
Index ..................................1389
Article 3
Exporting Data .........................1823
Article 4
Bonus Content on the Companion CD Function Reference .....................1831
Article 5
Part 8: Automating an Access
Color Names and Codes .................1841
Application Using Visual Basic
Article 6
Chapter 24
Macro Actions ..........................1849
Understanding Visual Basic Fundamentals .1451
Chapter 25
Automating Your Application
with Visual Basic ........................1583
Table of Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxv
About the CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvii
What’s on the CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvii
Sample Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvii
System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxviii
Support Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxx
Conventions and Features Used in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxi
Text Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxi
Design Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxi
Syntax Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxv
Getting Familiar with Access 2010 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxvi
About This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxvi
Part 1: Understanding Access
Chapter 1: What Is Access? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
What Is a Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Database Capabilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Access as an RDBMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Data Definition and Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Data Manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Data Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Access as an Application Development System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Deciding to Move to Database Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Extending the Power of Access to the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books and learning
resources for you. To participate in a brief online survey, please visit:
microsoft.com/learning/booksurvey
vii
viii Table of Contents
Chapter 2: Exploring the Access 2010 Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Opening Access for the First Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Getting Started with Access 2010 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Opening an Existing Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Exploring the Microsoft Office Backstage View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Taking Advantage of the Quick Access Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Understanding Content Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Enabling a Database That Is Not Trusted . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Understanding the Trust Center . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Enabling Content by Defining Trusted Locations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Understanding the Office Fluent Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Home Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Create Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
External Data Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Database Tools Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Customizing the Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Understanding the Navigation Pane . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Exploring Navigation Pane Object Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Working with Custom Categories and Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Sorting and Selecting Views in the Navigation Pane . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Manually Sorting Objects in the Navigation Pane . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Searching for Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Using the Single-Document vs . the Multiple-Document Interface . . . . . . . . . . . . . . . . . . . . 107
Modifying Global Settings via the Access Options Dialog Box . . . . . . . . . . . . . . . . . . . . . . . 112
Chapter 3: Access 2010 Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
The Architecture of Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Exploring a Desktop Database—Housing Reservations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
What Happened to Project Files (ADP)? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
The Many Faces of Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Part 2: Creating a Database and Tables
Chapter 4: Designing Client Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Creating a New Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Using a Database Template to Create a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Creating a New Empty Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Creating Your First Simple Table by Entering Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176