Access 2013: The Missing Manual

868 Pages·2013·31.86 MB·English

Access 2013

The book that should have been in the box®
Matthew MacDonald
Beijing | Cambridge | Farnham | Köln | Sebastopol | Tokyo
Access 2013: The Missing Manual
by Matthew MacDonald
Copyright © 2013 Matthew MacDonald. 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 (http://my.safaribooksonline.com).
For more information, contact our corporate/institutional sales department: (800)
998-9938 or [email protected].
April 2013: First Edition.
See http://http://oreilly.com/catalog/errata.csp?isbn=0636920028406 for release
details.
The Missing Manual is a registered trademark of O’Reilly Media, Inc. The Missing
Manual logo, and “The book that should have been in the box” 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 is aware of a trademark claim, the
designations are capitalized.
While every precaution has been taken in the preparation of this book, the publisher
assumes no responsibility for errors or omissions, or for damages resulting from the
use of the information contained in it.
ISBN-13: 978-1-449-35741-2
[LSI]
Contents
The Missing Credits ........................................ ix
Introduction ................................................ 1
What You Can Do with Access ........................................ 1
The Access Ribbon ..................................................7
The Changes in Access 2013 ......................................... 12
About This Book ...................................................14
About the Online Resources .........................................18
Safari® Books Online ...............................................19
Part One: Storing information
in Tables
CHAPTER 1: Creating Your First Database ............................. 23
Understanding Access Databases ....................................24
Starting a Database ................................................25
Building Your First Table ............................................30
Saving Databases ................................................. 40
Opening Databases ................................................44
The Navigation Pane ...............................................49
CHAPTER 2: Building Smarter Tables .................................. 55
Understanding Data Types ..........................................55
Design View .......................................................56
Access Data Types .................................................61
The Primary Key ...................................................88
Six Principles of Database Design ....................................89
CHAPTER 3: Mastering the Datasheet: Sorting, Searching, and
Filtering. ............................................... 97
Datasheet Customization ...........................................98
Datasheet Navigation .............................................104
Advanced Editing ..................................................115
Printing the Datasheet .............................................124
iii
CHAPTER 4: Blocking Bad Data ....................................... 129
Data Integrity Basics ..............................................130
Input Masks ......................................................138
Validation Rules ..................................................146
Lookups .........................................................155
CHAPTER 5: Linking Tables with Relationships. ........................ 161
Relationship Basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Using a Relationship ...............................................166
More Exotic Relationships ..........................................184
Relationship Practice ..............................................190
Part two: Manipulating Data
with Queries
CHAPTER 6: Queries That Select Records. ............................ 201
Query Basics .....................................................202
Creating Queries ..................................................203
Queries and Related Tables ........................................225
CHAPTER 7: Essential Query Tricks ................................... 237
Calculated Fields ..................................................237
Query Functions ................................................. 244
Query Parameters ............................................... 260
CHAPTER 8: Queries That Summarize Data ........................... 263
Totals Queries ....................................................263
Crosstab Queries .................................................270
CHAPTER 9: Queries That Change Tables ............................. 283
Understanding Action Queries .....................................283
Update Queries ...................................................285
Append Queries ..................................................291
Delete Queries ....................................................297
Tutorial: Flagging Out-of-Stock Orders ..............................301
Part three: Printing Reports
CHAPTER 10: Creating Reports. ........................................ 307
Report Basics ................................................... 309
Printing, Previewing, and Exporting a Report ........................321
Formatting a Report ..............................................328
Filtering and Sorting a Report ......................................342
iv COntents
CHAPTER 11: Designing Advanced Reports. ........................... 345
Improving Reports in Design View ..................................345
The Report Wizard ................................................357
The Label Wizard ................................................ 360
Fine-Tuning Reports with Properties ................................365
Expressions ......................................................369
Grouping ........................................................371
Part Four: Building a User interface
with Forms
CHAPTER 12: Creating Simple Forms. .................................. 383
Creating Forms .................................................. 384
Using Forms ......................................................391
Sorting and Filtering in a Form .....................................396
Creating Fancy Forms .............................................401
The Form Wizard .................................................416
CHAPTER 13: Designing Advanced Forms. ............................. 419
Customizing Forms in Design View ..................................419
Taking Control of Controls .........................................438
Forms and Linked Tables ..........................................453
CHAPTER 14: Building a Navigation System. ........................... 459
Mastering the Navigation Pane .................................... 460
Building Forms with Navigation Smarts ..............................470
Navigation Forms .................................................479
Linking to Related Data ........................................... 485
Part Five: Programming Access
CHAPTER 15: Automating Tasks with Macros ........................... 495
Macro Essentials ................................................. 496
Macros and Security ...............................................510
Three Macro Recipes ..............................................519
Managing Macros .................................................523
CHAPTER 16: Connecting Macros to Forms and Tables. ................ 529
Macro-Powered Forms ............................................529
Conditional Macros ................................................536
Data Macros ..................................................... 544
COntents v
CHAPTER 17: Automating Tasks with Visual Basic. ..................... 561
The Visual Basic Editor ............................................562
Putting Code in a Form ........................................... 566
Understanding Objects ............................................573
Using Objects ....................................................581
CHAPTER 18: Writing Smarter Code. ................................... 591
Exploring the VB Language ........................................591
Dealing with Trouble ............................................. 605
Deeper into Objects ...............................................612
Using VB to Run a Better Business ..................................618
Part six: Going Large: Access
Databases for Many Users
CHAPTER 19: Sharing a Database on Your Network .................... 639
Opening Up Your Database to the World ............................639
Preparing Your Database ..........................................642
Playing Well with Others ...........................................658
Data Corruption ................................................. 666
Securing Your Database .......................................... 668
CHAPTER 20: Building an Access Web App. ............................ 675
Assessing Web Apps ..............................................676
Preparing for Web Apps ...........................................677
Building a Simple Web App ........................................678
Running Your Web App ........................................... 690
Customizing Views ............................................... 698
Creating More Advanced Views .................................... 706
Tutorial: Creating a Web App from an Access Database ...............717
CHAPTER 21: Linking Access to SQL Server ............................ 727
How Access and SQL Server Can Work Together .....................728
Getting Started with SQL Server Express ............................734
Building a SQL Server Database ....................................742
Editing SQL Server Tables in Access .................................752
Migrating an Access Database to SQL Server ........................758
CHAPTER 22: Linking Access to SharePoint ............................ 763
Understanding SharePoint .........................................764
SharePoint and Access ............................................771
vi COntents
CHAPTER 23: Importing and Exporting Data. .......................... 789
Case for Importing and Exporting ..................................789
Using the Clipboard ...............................................791
Import and Export Operations ......................................796
Access and XML ..................................................810
Part seven: Appendix
APPENDIX A: Customizing Access. ..................................... 823
Adding Your Favorites to the QAT ..................................824
Personalizing the Ribbon ..........................................829
Index..................................................... 835
COntents vii