access 2010 the missing manual

www.it-ebooks.info

www.it-ebooks.info

Access 2010

Beijing  •  Cambridge  •  Farnham  •  Köln  •  Sebastopol  •  Taipei  •  Tokyo

Matthew MacDonald

www.it-ebooks.info

Access 2010: The Missing Manual

by Matthew MacDonald

Copyright © 2010 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 Media 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: 800-998-9938 or [email protected].

Printing History:

June 2010: 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 2010: 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.

ISBN: 9781449382377

[M]

www.it-ebooks.info

iii

Table of Contents

The Missing Credits . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

Part One: Storing Information in Tables

Chapter 1: Creating Your First Database . . . . . . . . . . . . . . . 23

Understanding Access Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

Getting Started with Your First Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Understanding Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Creating a Simple Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

Editing a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Saving Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Making Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Saving a Database with a Different Name or Format . . . . . . . . . . . . . . . . . . 40

Shrinking a Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

Opening Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Designating a Database as Trusted. . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Opening Recent Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

Opening More Than One Database at Once. . . . . . . . . . . . . . . . . . . . . . . 44

Opening a Database Created in an Older Version of Access . . . . . . . . . . . . . . 45

The Navigation Pane . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47

Browsing Tables with the Navigation Pane . . . . . . . . . . . . . . . . . . . . . . . 48

Managing Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

Chapter 2: Building Smarter Tables. . . . . . . . . . . . . . . . . . . 51

Understanding Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

Design View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Organizing and Describing Your Fields . . . . . . . . . . . . . . . . . . . . . . . . . 54

How Updates Work in Design View . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

www.it-ebooks.info

iv table of contents

Access Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

Memo. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

Number. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

Currency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

Date/Time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

Yes/No . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

Hyperlink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

Attachment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

AutoNumber . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

Calculated. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

The Primary Key. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

Creating Your Own Primary Key Field . . . . . . . . . . . . . . . . . . . . . . . . . . 82

Six Principles of Database Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82

1. Choose Good Field Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

2. Break Down Your Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84

3. Include All the Details in One Place . . . . . . . . . . . . . . . . . . . . . . . . . . 85

4. Avoid Duplicating Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

5. Avoid Redundant Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

6. Include an ID Field. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

Chapter 3: Mastering the Datasheet: Sorting, Searching,

Filtering, and More . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Datasheet Customization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

Formatting the Datasheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

Rearranging Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

Resizing Rows and Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92

Hiding Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94

Freezing Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95

Datasheet Navigation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

Sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

Filtering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

Searching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104

Advanced Editing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

The Spell Checker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107

AutoCorrect. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

Special Characters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

Printing the Datasheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

Print Preview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

Fine-Tuning a Printout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117

Chapter 4: Blocking Bad Data . . . . . . . . . . . . . . . . . . . . . 119

Data Integrity Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120

Preventing Blank Fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120

Setting Default Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122

Preventing Duplicate Values with Indexes . . . . . . . . . . . . . . . . . . . . . . . 124

www.it-ebooks.info

table of contents v

Input Masks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

Using a Readymade Mask . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

Creating Your Own Mask . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133

Validation Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

Applying a Field Validation Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

Writing a Field Validation Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138

Creating a Table Validation Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142

Lookups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144

Creating a Simple Lookup with Fixed Values . . . . . . . . . . . . . . . . . . . . . 145

Adding New Values to Your Lookup List . . . . . . . . . . . . . . . . . . . . . . . . 147

Chapter 5: Linking Tables with Relationships. . . . . . . . . . . . 149

Relationship Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150

Redundant Data vs. Related Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 150

Matching Fields: The Relationship Link . . . . . . . . . . . . . . . . . . . . . . . . 152

Linking with the ID Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153

The Parent-Child Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154

Using a Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155

Defining a Relationship. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155

Editing Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160

Referential Integrity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161

Navigating a Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164

Lookups with Related Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167

More Exotic Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171

One-to-One Relationship. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171

Many-to-Many Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172

Relationship Practice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176

The Music School . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177

The Chocolate Store . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

Part Two: Manipulating Data with Queries

Chapter 6: Queries That Select Records . . . . . . . . . . . . . . . 187

Query Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188

Creating Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188

Creating a Query in Design View. . . . . . . . . . . . . . . . . . . . . . . . . . . . 189

Creating a Simple Query with the Query Wizard . . . . . . . . . . . . . . . . . . . 199

Understanding the SQL View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202

Queries and Related Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207

Joining Tables in a Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212

Multiple Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214

www.it-ebooks.info

vi table of contents

Chapter 7: Essential Query Tricks . . . . . . . . . . . . . . . . . . . 217

Calculated Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217

Defining a Calculated Field. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218

Simple Math with Numeric Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . 221

Expressions with Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223

Query Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224

Using a Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224

The Expression Builder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226

Formatting Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230

More Mathematical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230

Text Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232

Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234

Dealing with Blank Values (Nulls) . . . . . . . . . . . . . . . . . . . . . . . . . . . 237

Summarizing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238

Grouping a Totals Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240

Joins in a Totals Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242

Query Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244

Chapter 8: Queries That Update Records . . . . . . . . . . . . . . 247

Understanding Action Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247

Testing Action Queries (Carefully) . . . . . . . . . . . . . . . . . . . . . . . . . . . 248

The Action Query Family . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249

Update Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249

Append Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254

Creating an Append (or Make-Table) Query. . . . . . . . . . . . . . . . . . . . . . 256

Getting AutoNumbers to Start at Values Other Than 1 . . . . . . . . . . . . . . . . 259

Delete Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260

Tutorial: Flagging Out-of-Stock Orders . . . . . . . . . . . . . . . . . . . . . . . . . . . 262

Finding Out-of-Stock Items. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263

Putting the Orders on Hold. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264

Chapter 9: Analyzing Data with Crosstab Queries and

Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . 267

Understanding Crosstab Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268

Creating Crosstab Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270

Creating a Crosstab Query with the Wizard . . . . . . . . . . . . . . . . . . . . . . 272

Creating a Crosstab Query from Scratch. . . . . . . . . . . . . . . . . . . . . . . . 276

Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278

Building a Pivot Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279

Manipulating a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283

Creating a Calculated Field. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285

Hiding and Showing Details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287

Filtering Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288

Pivot Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290

Choosing a Chart Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292

Printing a Pivot Chart. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293

www.it-ebooks.info

table of contents vii

Part Three: Printing Reports

Chapter 10: Creating Reports . . . . . . . . . . . . . . . . . . . . . 297

Report Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298

Creating a One-Click Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299

Building a Report in Layout View . . . . . . . . . . . . . . . . . . . . . . . . . . . 300

The Many Views of a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306

Rearranging a Report. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308

Printing, Previewing, and Exporting a Report. . . . . . . . . . . . . . . . . . . . . . . . 308

Previewing a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309

Export Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311

Exporting a Report to a PDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312

Exporting a Report to a Different Format . . . . . . . . . . . . . . . . . . . . . . . 314

Formatting a Report. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315

Applying a Theme . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315

Formatting Columns and Column Headers . . . . . . . . . . . . . . . . . . . . . . 318

Conditional Formatting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323

Data Bars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326

Filtering and Sorting a Report. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328

Filtering a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329

Sorting a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330

Chapter 11: Designing Advanced Reports . . . . . . . . . . . . . . 331

Improving Reports in Design View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331

The Design View Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332

Understanding Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334

Moving Fields Out of a Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335

Adding More Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338

Creating a Report from Scratch (in Design View) . . . . . . . . . . . . . . . . . . . 340

The Report Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343

The Label Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345

Fine-Tuning Reports with Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350

Modifying Common Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352

Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353

Grouping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354

Report Grouping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356

Fine-Tuning with the “Group, Sort, and Total” Pane . . . . . . . . . . . . . . . . . . 359

Multiple Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362

Part Four: Building a User Interface with Forms

Chapter 12: Creating Simple Forms. . . . . . . . . . . . . . . . . . 367

Creating Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368

Building a Simple Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368

Using AutoNumber Fields in Forms . . . . . . . . . . . . . . . . . . . . . . . . . . 373

Showing Pictures from a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374

www.it-ebooks.info

viii table of contents

Using Forms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375

Finding and Editing a Record. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375

Adding a Record . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377

Deleting a Record. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378

Printing Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378

Sorting and Filtering in a Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379

Sorting a Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380

Filtering a Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380

Using the Filter by Form Feature. . . . . . . . . . . . . . . . . . . . . . . . . . . . 381

Saving Filters for the Future . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383

Creating Fancy Forms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384

Manipulating Layouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385

Liberating Controls from Layouts . . . . . . . . . . . . . . . . . . . . . . . . . . . 390

Using Tabular Layouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390

Showing Multiple Records in any Form . . . . . . . . . . . . . . . . . . . . . . . . 391

Split Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394

More Useful Form Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396

The Form Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397

Chapter 13: Designing Advanced Forms . . . . . . . . . . . . . . . 401

Customizing Forms in Design View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401

Form Sections: The Different Parts of Your Form . . . . . . . . . . . . . . . . . . . 402

Adding Controls to Your Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404

The Control Gallery: A Quick Tour . . . . . . . . . . . . . . . . . . . . . . . . . . . 406

Arranging Controls on Your Form . . . . . . . . . . . . . . . . . . . . . . . . . . . 409

Anchoring: Automatically Resizing Controls. . . . . . . . . . . . . . . . . . . . . . 412

Tab Order: Making Keyboard Navigation Easier. . . . . . . . . . . . . . . . . . . . 417

Taking Control of Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420

Locking Down Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420

Prevent Errors with Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421

Performing Calculations with Expressions . . . . . . . . . . . . . . . . . . . . . . . 422

Organizing with Tab Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423

Going Places with Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425

Showing Web Pages with the Web Browser . . . . . . . . . . . . . . . . . . . . . . 426

Navigating with Lists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428

Performing Actions with Command Buttons. . . . . . . . . . . . . . . . . . . . . . 431

Forms and Linked Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434

Table Relationships and Simple Forms. . . . . . . . . . . . . . . . . . . . . . . . . 434

The Subform Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435

Creating Customized Subforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436

Chapter 14: Building a Navigation System. . . . . . . . . . . . . . 439

Mastering the Navigation Pane . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439

Configuring the Navigation List . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440

Better Filtering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443

Hiding Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445

www.it-ebooks.info

table of contents ix

Using Custom Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446

Searching the Navigation List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448

Building Forms with Navigation Smarts . . . . . . . . . . . . . . . . . . . . . . . . . . 449

Custom Menu Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449

Designating a Startup Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451

Compound Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451

Showing All Your Forms in a List . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453

Navigation Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457

Creating a Single-Level Navigation Form . . . . . . . . . . . . . . . . . . . . . . . 458

Creating a Two-Level Navigation Form . . . . . . . . . . . . . . . . . . . . . . . . 461

Fine-Tuning Your Navigation Buttons . . . . . . . . . . . . . . . . . . . . . . . . . 462

Linking to Related Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463

Showing Linked Records in Separate Forms. . . . . . . . . . . . . . . . . . . . . . 463

Showing More Detailed Reports with Links . . . . . . . . . . . . . . . . . . . . . . 466

Part Five: Programming Access

Chapter 15: Automating Tasks with Macros. . . . . . . . . . . . . 473

Macro Essentials . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475

Creating a Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475

Running a Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478

Editing a Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479

Finding Actions in the Action Catalog . . . . . . . . . . . . . . . . . . . . . . . . . 480

Collapsing, Expanding, and Grouping Macro Actions . . . . . . . . . . . . . . . . . 482

Solving Macro Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485

Macros and Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488

Unsafe Macro Actions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488

Trusted Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489

Temporary Trust . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491

The Trust Center . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491

Setting Up a Trusted Location . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493

Three Macro Recipes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495

Find a Record . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495

Print a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496

Email Your Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497

Managing Macros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500

Submacros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500

Assigning a Macro to a Keystroke . . . . . . . . . . . . . . . . . . . . . . . . . . . 502

Configuring a Startup Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504

Chapter 16: Connecting Macros to Forms and Tables . . . . . . . 505

Macro-Powered Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505

Understanding Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506

Attaching a Macro to an Event . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508

Reading Arguments from a Form . . . . . . . . . . . . . . . . . . . . . . . . . . . 509

Changing Form Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511

www.it-ebooks.info

x table of contents

Conditional Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 512

Building a Condition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513

Validating Data with Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 514

More Complex Conditional Macros . . . . . . . . . . . . . . . . . . . . . . . . . . 517

Data Macros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519

Table Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 520

Validation with Before Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 521

Keeping a Change Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525

Debugging Data Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528

An Advanced Data Macro: Synchronizing Data . . . . . . . . . . . . . . . . . . . . 530

Chapter 17: Automating Tasks with Visual Basic . . . . . . . . . . 535

The Visual Basic Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536

Adding a New Module . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537

Writing the Simplest Possible Code Routine . . . . . . . . . . . . . . . . . . . . . . 538

Putting Code in a Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 540

Responding to a Form Event . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 541

Calling the Code in a Module . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544

Reading and Writing the Fields on a Form. . . . . . . . . . . . . . . . . . . . . . . 546

Understanding Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547

Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548

Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 551

Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553

Using Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554

Indicating That a Record Has Changed . . . . . . . . . . . . . . . . . . . . . . . . 554

Creating a Mouseover Effect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 559

Chapter 18: Writing Smarter Code . . . . . . . . . . . . . . . . . . 563

Exploring the VB Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563

Storing Information in Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564

Making Decisions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 565

Repeating Actions with a Loop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 568

Creating Custom Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570

Putting It All Together: A Function for Testing Credit Cards . . . . . . . . . . . . . . 571

Dealing with Trouble . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 575

Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576

Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 579

Deeper into Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 581

The DoCmd Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583

Converting a Macro to VB Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585

Using VB to Run a Better Business . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587

Keeping a Running Total . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 588

Getting Price Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591

Adding a New Product During an Order. . . . . . . . . . . . . . . . . . . . . . . . 592

Managing Order Fulfillment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595

Updating Stock Numbers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598

www.it-ebooks.info

table of contents xi

Part Six: Sharing Access with the Rest of the World

Chapter 19: Sharing a Database with Multiple Users . . . . . . . 605

Opening Up Your Database to the World. . . . . . . . . . . . . . . . . . . . . . . . . . 606

How Access Sharing Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606

Preparing Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608

Understanding Split Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608

Splitting a Database with the Wizard . . . . . . . . . . . . . . . . . . . . . . . . . 610

How Linked Tables Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613

Manually Splitting a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615

Locking Down Your Front End . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618

Sharing a Database with People Who Don’t Own Access . . . . . . . . . . . . . . . 620

Playing Well with Others . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 621

Seeing Changes As They Happen . . . . . . . . . . . . . . . . . . . . . . . . . . . 622

Dealing with Editing Conflicts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624

Using Locks to Stop Overlapping Edits. . . . . . . . . . . . . . . . . . . . . . . . . 626

Opening a Database in Exclusive Mode . . . . . . . . . . . . . . . . . . . . . . . . 628

Data Corruption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 628

Diagnosing (and Fixing) Corrupt Databases. . . . . . . . . . . . . . . . . . . . . . 629

Preventing Corruption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630

Securing Your Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631

Password-Protecting Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . 632

Passwords and Split Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633

Using Windows File Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635

Chapter 20: Importing and Exporting Data . . . . . . . . . . . . . 639

Case for Importing and Exporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639

Understanding Exports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640

Understanding Imports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640

Using the Clipboard. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642

Copying a Table from Access to Somewhere Else . . . . . . . . . . . . . . . . . . . 642

Copying Cells from Excel into Access . . . . . . . . . . . . . . . . . . . . . . . . . 644

Import and Export Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646

Importable File Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647

Importing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648

Importing from an Excel File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650

Importing from a Text File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653

Exportable File Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 654

Exporting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 655

Reusing Import and Export Settings . . . . . . . . . . . . . . . . . . . . . . . . . . 657

Access and XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659

What Is XML, Really? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 660

Three Rules of XML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 661

XML Files and Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 664

www.it-ebooks.info

xii table of contents

The Access XML Story . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 664

Exporting to an XML File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 666

Importing from an XML File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668

Collecting Info by Email . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670

Creating an Email Message. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 671

Processing Replies Manually . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 677

Processing Replies Automatically . . . . . . . . . . . . . . . . . . . . . . . . . . . 679

Managing Your Email Collection Settings . . . . . . . . . . . . . . . . . . . . . . . 679

Chapter 21: Connecting Access to SQL Server. . . . . . . . . . . . 683

Should You Switch to SQL Server? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683

How SQL Server Works. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 684

A Cheaper SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 686

Getting Started: SQL Server Express . . . . . . . . . . . . . . . . . . . . . . . . . . . . 688

Preparing for SQL Server Express . . . . . . . . . . . . . . . . . . . . . . . . . . . 688

Downloading SQL Server Express . . . . . . . . . . . . . . . . . . . . . . . . . . . 690

Installing SQL Server Express. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692

Putting SQL Server on the Network . . . . . . . . . . . . . . . . . . . . . . . . . . 696

Creating a SQL Server Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697

Upsizing a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697

Managing Your Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 703

Creating a SQL Server Database from Scratch. . . . . . . . . . . . . . . . . . . . . 706

Adding Objects to a SQL Server Database . . . . . . . . . . . . . . . . . . . . . . . . . 707

Creating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 707

Understanding Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 713

Creating a View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 714

Chapter 22: Connecting Access to SharePoint . . . . . . . . . . . 719

Understanding SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 720

What You Can Do in SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721

Setting Up SharePoint. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 724

Creating a Team Site . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 724

Customizing Your Site . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 727

SharePoint and Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 731

Building a List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 732

Exporting a Table to SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . 737

Importing a List in Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 739

Moving a Whole Database to SharePoint . . . . . . . . . . . . . . . . . . . . . . . 742

Editing Your SharePoint Data in Access . . . . . . . . . . . . . . . . . . . . . . . . 745

Making Offline Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 746

Chapter 23: Building a Web Database . . . . . . . . . . . . . . . . 751

Understanding Web Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 752

Your First Web Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 753

Creating a New Web Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 754

Client Objects and Web Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755

www.it-ebooks.info

table of contents xiii

Publishing a Web Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 758

The SharePoint Web Database Page . . . . . . . . . . . . . . . . . . . . . . . . . . 760

Web Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 762

Distributing Your Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 763

Synchronizing Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 765

Working Offline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 767

Delving Deeper into Web Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 768

Web Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 769

Web Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 771

Web Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773

Web Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773

Converting an Existing Database to a Web Database . . . . . . . . . . . . . . . . . . . 776

Checking Web Compatibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 777

Migration Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 778

Part Eight: Appendix

Appendix A: Customizing Access . . . . . . . . . . . . . . . . . . . 783

Adding Your Favorites to the QAT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 784

Adding Buttons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 785

Customizing Specific Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . 787

Personalizing the Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 788

Changing Existing Tabs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 789

Creating Your Own Tab. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 791

Saving and Reusing Your Custom Ribbon . . . . . . . . . . . . . . . . . . . . . . . 793

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 795

www.it-ebooks.info