Microsoft® Access 2010 ® Roger Jennings 800 East 96th Street Indianapolis, Indiana 46240 MICROSOFT® ACCESS® 2010 IN DEPTH Associate Publisher Greg Wiegand Copyright © 2011 by Pearson Education, Inc. Acquisitions Editor All rights reserved. No part of this book shall be reproduced, stored in a Loretta Yates retrieval system, or transmitted by any means, electronic, mechanical, photo- copying, recording, or otherwise, without written permission from the publisher. Development Editor No patent liability is assumed with respect to the use of the information con- Charlotte Kughen tained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omis- Technical Editor sions. Nor is any liability assumed for damages resulting from the use of the Scott Diamond information contained herein. Library of Congress Cataloging-in-Publication Data Managing Editor Jennings, Roger. Sandra Schroeder Microsoft Access 2010 in depth / Roger Jennings. -- 1st ed. Project Editor p. cm. Includes index. Mandie Frank ISBN 978-0-7897-4307-7 Copy Editor 1. Database management. 2. Microsoft Access. I. Title. QA76.9.D3J4683 2011 Barbara Hacha 005.75’65--dc22 Indexer 2010048411 Tim Wright ISBN-13: 978-0-7897-4307-7 ISBN-10: 0-7897-4307-8 Production Printed in the United States of America Bronkella Publishing LLC First Printing: December 2010 Designer Trademarks Anne Jones All terms mentioned in this book that are known to be trademarks or service Editorial Coordinator marks have been appropriately capitalized. Que Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded Cindy Teeters as affecting the validity of any trademark or service mark. Warning and Disclaimer Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an “as is” basis. The author and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages aris- ing from the information contained in this book. Bulk Sales Que Publishing offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales. For more information, please contact U.S. Corporate and Government Sales 1-800-382-3419 [email protected] For sales outside of the U.S., please contact International Sales [email protected] CONTENTS AT A GLANCE Introduction I Getting Acquainted with Access 2010 1 Access 2010 for Access 2007 Users: What’s New 17 2 Building Simple Tracking Applications 45 3 Navigating the Fluent User Interface 95 II Learning the Fundamentals of Access Databases 4 Exploring Relational Database Theory and Practice 155 5 Working with Access Databases and Tables 179 6 Entering, Editing, and Validating Access Table Data 245 7 Sorting, Finding, and Filtering Data 267 8 Linking, Importing, and Exporting Data 297 III Transforming Data with Queries and PivotTables 9 Designing Queries for Access Databases 347 10 Understanding Access Query Operators and Expressions 381 11 Creating Multitable and Crosstab Queries 421 12 Working with PivotTable and PivotChart Views 505 13 Creating and Updating Access Tables with Action Queries 535 IV Designing Forms and Reports 14 Creating and Using Access Forms 565 15 Designing Custom Multitable Forms 607 16 Working with Simple Reports and Mailing Labels 683 17 Preparing Advanced Reports 729 18 Adding Graphs, PivotCharts, and PivotTables 755 V Programming Databases with Macros 19 Automating Access Applications with Macros and Procedures 797 20 Emulating Table Triggers with Access Data Macros 821 VI Collaborating with Access Data 21 Linking Access Front Ends to Access and Client/Server Tables 859 22 Collaborating with Windows SharePoint Foundation 2010 917 23 Sharing Web Databases with SharePoint Server 2010 975 VII Working with HTML and XML Documents 24 Importing and Exporting Web Pages 1007 (Online) 25 Integrating with XML and InfoPath 2010 1053 (Online) VIII Creating Access Front Ends to SQL Server Databases 26 Exploring Access Data Projects and SQL Server 2008 1089 (Online) 27 Moving from Access Queries to Transact-SQL 1137 (Online) 28 Upsizing Access Applications to Access Data Projects and SQL Azure 1171 (Online) IX Programming and Converting Access Applications 29 Learning Visual Basic for Applications 1239 (Online) 30 Handling Events with VBA and Macros 1279 (Online) 31 Programming Combo and List Boxes 1317 (Online) 32 Understanding Data Access Objects, OLE DB, and ADO 1349 (Online) 33 Upgrading Access 2003 and Earlier Applications to Access 2010 1403 (Online) X Appendix A What Was New in Access 2007 for Users of Access 2003 and Earlier 1423 (Online) B Glossary 1475 (Online) Index 1505 CONTENTS Introduction The Quick Access Toolbar 26 The File Tab’s Backstage View 26 Who Should Read This Book 2 Security, Trusted Locations, Packages, and How This Book Is Organized 4 Certificates 29 Printed Parts and Chapters 5 Specifying Trusted Locations 31 Online-Only Parts and Chapters 8 Packaging and Code-Signing Part IX: Programming and Converting Databases 31 Access Applications 10 Enabling Non-trusted Application Appendixes 10 Automation with Macros 31 The Downloadable Sample Files 11 Access Web Databases and SharePoint How This Book Is Designed 11 Lists 32 Typographic Conventions Used in This Application Development by Templates 32 Book 12 Key Combinations, Menu Choices, and Access Macros Redux 33 Filenames 12 SQL Server 2008 [R2] Express Edition SQL Statements and Keywords in Other Setup 35 Languages 12 Downloading and Installing SSX 35 Typographic Conventions Used for Managing SSX 40 VBA 13 Typographic Conventions Used for 2 Building Simple Tracking VBScript 14 Applications 45 System Requirements for Access 2010 14 Creating Access Applications from Downloaded Templates 46 Other Sources of Information for Access 15 Using the Getting Started Window to Books 15 Download Templates 46 Internet 16 Changing the Default Folder for New Databases and Projects 48 I Getting Acquainted with Access 2010 Creating a Database from a Getting Started Window Template 48 1 Access 2010 for Access 2007 Users: Designating the Default Database Folder What’s New 17 as a Trusted Location 53 Creating a Database from Any Access What’s New in Microsoft Office Access Template on Microsoft Office Online 55 2010: An Overview 18 Changing Forms from Overlapping Windows to Tabbed Documents 58 Changes to the Office 2007 Ribbon User Interface 22 Touring the Modified Tasks Access 2007’s Main Ribbons 22 Application 59 Contextual Ribbons for Access Understanding the Role of the Databases and Projects 24 Navigation Pane 59 vi MICROSOFT® ACCESS® 2010 IN DEPTH Opening Forms and Reports with Function-Key Assignments and Shortcut Macros 66 Keys for Fields, Grids, and Text Boxes 121 Importing and Exporting Data from and to Outlook 2010 70 Setting Default Options 122 Importing Contacts from an Outlook The General Page 123 Contacts Folder 70 The Current Database Page 124 Exporting Contacts to Outlook 72 The Datasheet Page 126 Using Outlook to Email Lists and Detail The Object Designers Page 126 Items 74 The Proofing Page 128 Collecting Data by Email with Outlook The Language Page 129 HTML Forms 78 The Client Settings Page 129 The Customize Ribbon Page 132 Integrating Objects from Other Template The Quick Access Toolbar Page 132 Databases 79 The Add-Ins Page 134 Import Missing Objects 80 The Trust Center Pages 134 Alter Inappropriate Field Names 83 Conform MVLF Combo Boxes of List and Creating a Customized Template File 137 Details Forms 86 Add Approved By and Assigned To Using Access Online Help 138 Employee Lookup Fields 90 Searching for a Phrase 139 Add Approved By and Assigned To Searching Other Sources 141 Combo Boxes to Lists and Details Spelunking the Database Utilities 141 Forms 93 Compacting and Repairing Databases 142 3 Navigating the Fluent User Converting Earlier Database Formats to Interface 95 Access 2007 Format 143 Defining Access Functions 95 Compile Errors in the Convert Database Process 143 Defining Access Operating Modes 98 Creating .accde Files 143 Creating .accdr Runtime Files 144 Opening the Northwind.accdb Sample Database 100 Packaging, Signing, and Distributing an Access 2007 Database 144 Understanding Access’s Table Generate and Install a Self-Certified Windows 102 Digital Certificate 145 Navigating the Home and Create Creating a Signed Package File Ribbons 106 (*.accdc) 148 The Home Ribbon 106 Testing a Signed Package File Context-Specific Table Tools (*.accdc) 148 Ribbons 110 Creating and Testing a Packaged The Create Ribbon 118 Solution Installer File (*.msi) 150 Using the Function Keys 121 Global Function Keys 121 Contents vii II Learning the Fundamentals of Access Access Record-Locking Information Databases Files 180 Access Add-In (Library) Databases 181 4 Exploring Relational Database Theory Creating a New Access Database from and Practice 155 Scratch 181 Moving from Spreadsheets to Exploring the Properties of Tables and Databases 155 Fields 183 Table Properties for Subdatasheets 185 Reliving Database History 156 Field Properties 186 The Early History of Databases 156 The Relational Database Model 157 Choosing Field Data Types, Sizes, and Client/Server and Desktop RDBMSs 158 Formats 191 Choosing Field Sizes for Numeric and Defining the Structure of Relational Text Data 193 Databases 159 Selecting a Display Format 196 Removing Data Redundancy with Using Input Masks 202 Relationships 160 Preparing to Add a Table Related to the Conforming to Table Design Rules 164 Employees Table 204 First Normal Form 164 Determining What Information the Table Second Normal Form 166 Should Include 206 Third Normal Form 167 Assigning Information to Fields 207 Fourth Normal Form 168 Creating the HRActions Table in Design Fifth Normal Form 169 View 208 Choosing Primary Key Codes 169 Creating Tables from Templates 212 Creating a Table Directly in Datasheet Maintaining Data Integrity and View 213 Accuracy 170 Referential Integrity 171 Setting Default Values of Fields 213 Entity Integrity and Indexes 172 Working with Relations, Key Fields, and Data Validation Rules and Check Indexes 215 Constraints 172 Establishing Relationships Between Transactions 173 Tables 215 Displaying Data with Queries and Cascading Updates and Deletions 220 Views 174 Selecting a Primary Key 221 Adding Indexes to Tables 222 5 Working with Access Databases and Tables 179 Altering Fields and Relationships 224 Rearranging the Sequence of Fields in a Understanding Access Database Files 179 Table 225 Upgrading from Jet .mdb to Access Changing Field Data Types and .accdb Database Files 179 Sizes 226 Migrating from Access Applications to Changing Relationships Between SQL Server Data Projects 180 Tables 227 viii MICROSOFT® ACCESS® 2010 IN DEPTH Working with Object Dependencies and 7 Sorting, Finding, and Filtering Access Smart Tags 228 Data 267 Enabling and Viewing Object Dependencies 228 Sorting Table Data 267 Activating the Access Property Options Freezing Display of a Table Field 268 Smart Tag 229 Sorting Data on a Single Field 268 Adding an Internet-Based Smart Tag to a Sorting Data on Multiple Fields 269 Field 231 Removing a Table Sort Order and Thawing Columns 270 Copying and Pasting Tables 232 Finding Matching Records in a Table 271 Using the Table Analyzer Wizard 235 Replacing Matched Field Values Generating a Data Dictionary with the Automatically 274 Database Documenter 240 Filtering Table Data 274 6 Entering, Editing, and Validating Filtering by Selection 275 Access Table Data 245 Using the Text Filters Option 277 Filtering by Form 278 Using Keyboard Operations for Entering and Applying Menu-based Filters and Sort Editing Data 246 Orders 284 Creating a Working Copy of Northwind. accdb 246 Applying Advanced Filters and Sort Setting Data Entry Options 246 Orders 285 Using Data Entry and Editing Keys 248 Adding Multifield Sort and Compound Using Key Combinations for Windows Filter Criteria 286 Clipboard Operations 250 Using Composite Criteria 288 Using Shortcut Keys for Fields and Text Saving Your Filter as a Query and Boxes 252 Loading a Filter 291 Applying a Saved Query as a Filter 292 Adding Records to a Table 252 Customizing Table Datasheet View 292 Selecting, Appending, Replacing, and Deleting Table Records 254 Copying, Exporting, and Mailing Sorted and Filtered Data 295 Validating Data Entry 254 Adding Field-Level Validation Rules 255 8 Linking, Importing, and Exporting Adding Table-Level Validation Rules Data 297 with the Expression Builder 257 Adding a Simple Lookup List to the Importing and Linking Spreadsheet ActionType Field 258 Files 298 Creating a Table by Importing an Excel Adding Records to the HRActions Worksheet 299 Table 261 Linking Excel Worksheets 307 Testing Field and Table Validation Rules 264 Contents ix Working with Microsoft Outlook and Using the Query Design Window 355 Exchange Folders 308 Selecting Fields for Your Query 356 Importing and Exporting Access Tables Selecting Records by Criteria and Sorting with Outlook 2010 308 the Display 360 Linking with the Exchange/Outlook Preventing Updates to the Query Wizard 311 Resultset 362 Creating More Complex Criteria 364 Importing Text Files 313 Changing the Names of Query Column Using the Import Text Wizard 314 Headers 365 Setting the Import Text Wizard’s Printing Your Query as a Report 367 Advanced Options 318 Testing the Other Query Wizards 369 Working with Tables in Other Database File Finding Duplicate Values in a Field 371 Formats 321 Finding Values in One Table with No Dealing with PC Database Files 324 Matching Values in a Related Table 372 Linking and Importing External ISAM Tables 324 Creating Other Types of Queries 373 Dealing with Images in External Creating and Using a Simple Make-Table Database Files 326 Action Query 374 Converting Field Data Types to Access Adding a Parameter to Your Make-Table Data Types 328 Query 375 Using the Linked Table Manager Add-in to 10 Understanding Access Query Relink Tables 330 Operators and Expressions 381 Using the Access Mail Merge Wizard 331 Understanding the Elements of Creating and Previewing a New Form Expressions 382 Letter 332 Using an Existing Mail Merge Document Operators 384 with a New Data Source 339 Arithmetic Operators 385 Assignment and Comparison Exporting Tables to Word, Excel, PDF, and Operators 386 XPS Files 341 Logical Operators 386 Exporting Table Data as Text Files 344 Concatenation Operators 387 Identifier Operators 387 Exporting Data in Other File Formats 345 Other Operators 388 Literals 389 III Transforming Data with Queries and PivotTables Identifiers 390 Functions 391 9 Designing Queries for Access Using the Immediate Window 391 Databases 347 Getting Help as You Write Queries 392 The Variant Data Type in VBA 394 Trying the Simple Query Wizard 348 The TempVars Collection 396 Creating a Simple SELECT Query 348 Functions for Date and Time 396