Special Edition Using Microsoft Office Access 2003

1536 Pages·2003·60.65 MB·English

by  Roger Jennings

00 0789729520 fm 8/22/03 2:31 PM Page i
S P E C I A L E D I T I O N
USING
Microsoft®
Office Access
2003
Roger Jennings
Pearson Education
800 East 96th Street
Indianapolis, Indiana 46240
00 0789729520 fm 8/22/03 2:31 PM Page ii
Contents at a Glance
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1 25 Converting Access Objects to Data Access
Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1065
I Getting Acquainted with Access 2003 26 Integrating with InfoPath and SharePoint
1 Access 2003 for Access 97 and 2000/2002 Users: Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1101
What’s New . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19
VII Programming and Converting Access
2 Building a Simple Desktop and Web
Applications
Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53
3 Navigating the Access User Interface . . . . . . . . . . .83 27 Learning Visual Basic for Applications . . . . . . . .1149
28 Handling Events with VBA 6.0 . . . . . . . . . . . . . .1191
II Learning the Fundamentals of Jet 29 Programming Combo and List Boxes . . . . . . . . .1229
Databases 30 Understanding Universal Data Access,
4 Exploring Relational Database Theory and OLE DB, and ADO . . . . . . . . . . . . . . . . . . . . . .1257
Practice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .123 31 Consuming and Providing XML Web
5 Working with Jet Databases and Tables . . . . . . . .151 Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1333
6 Entering, Editing, and Validating Jet 32 Upgrading Access 97 and 2000/2002
Table Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . .217 Applications to Access 2003 . . . . . . . . . . . . . .1375
7 Sorting, Finding, and Filtering Data . . . . . . . . . . .241
VIII Appendix
8 Linking, Importing, and Exporting Data . . . . . . . .267
A Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1397
III Transforming Data with Queries and
PivotTables Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1435
9 Designing Queries for Jet Databases . . . . . . . . . .327
10 Understanding Jet Operators and
Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . .355
11 Creating Multitable and Crosstab Queries . . . . . .395
12 Working with PivotTable and PivotChart
Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .461
13 Creating and Updating Jet Tables with Action
Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .489
IV Designing Forms and Reports
14 Creating and Using Access Forms . . . . . . . . . . . . .519
15 Designing Custom Multitable Forms . . . . . . . . . .569
16 Working with Simple Reports and Mailing
Labels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .635
17 Preparing Advanced Reports . . . . . . . . . . . . . . . .677
18 Adding Graphs, PivotCharts, and PivotTables . . . .703
V Upgrading to SQL Server Databases
19 Linking Access Front-Ends to Jet and
Client/Server Tables . . . . . . . . . . . . . . . . . . . . .743
20 Exploring Access Data Projects and SQL
Server 2000 . . . . . . . . . . . . . . . . . . . . . . . . . . . .801
21 Moving from Jet Queries to Transact-SQL . . . . . .853
22 Upsizing Jet Applications to Access Data
Projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .887
VI Publishing Data to Intranets and the
Internet
23 Exporting and Importing Data with XML . . . . . . .943
24 Designing and Deploying Data Access Pages . . .997
00 0789729520 fm 8/22/03 2:31 PM Page iii
iii
Special Edition Using Microsoft Office Access Associate Publisher
Michael Stephens
2003
Acquisitions Editor
Copyright 2004 by Que Publishing Loretta Yates
All rights reserved. No part of this book shall be reproduced, Development Editor
stored in a retrieval system, or transmitted by any means, elec- Sean Dixon
tronic, mechanical, photocopying, recording, or otherwise, with-
Managing Editor
out written permission from the publisher. No patent liability is Charlotte Clapp
assumed with respect to the use of the information contained
Project Editor
herein. Although every precaution has been taken in the prepara-
Andy Beaster
tion of this book, the publisher and author assume no responsibility
Indexer
for errors or omissions. Nor is any liability assumed for damages
John Sleeva
resulting from the use of the information contained herein.
Proofreader
International Standard Book Number: 0-7897-2952-0
Suzanne Thomas
Library of Congress Catalog Card Number: 2003106309
Technical Editor
Printed in the United States of America Rebecca Riordan
First Printing: September 2003 Team Coordinator
Cindy Teeters
05 04 03 02 4 3 2 1
Interior Designer
Bulk Sales
Gary Adair
Que Publishing offers excellent discounts on this book when
Cover Designer
ordered in quantity for bulk purchases or special sales. For more
Aren Howell
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
1-317-428-3341
[email protected]
Trademarks
All terms mentioned in this book that are known to be trade-
marks or service 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 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 authors and the
publisher shall have neither liability nor responsibility to any per-
son or entity with respect to any loss or damages arising from the
information contained in this book.
00 0789729520 fm 8/22/03 2:31 PM Page iv
iv Special Edition Using Microsoft Office Access 2003
Contents
Introduction 1
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
I Getting Acquianted with Access 2003
1 Access 2003 for Access 97 and 2000/2002 Users: What’s New 19
. . . . . . . . . . . . . . .
Welcome to Microsoft Office Access 2003 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20
What’s New in Access 2003 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20
Macro Security and Jet “Sandbox” Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21
New Task Panes and Help System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22
Built-in and Custom Smart Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24
Office Web Components for Data Access Pages, PivotTables, and PivotCharts . . . . . . . .25
Improvements to XML Data Export and Import Operations . . . . . . . . . . . . . . . . . . . . . . . . . .25
Support for XML Web Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26
Data Display and Editing with InfoPath . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27
Collaboration with Windows SharePoint Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29
Direct Database Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30
Access 2002 Changes to Basic Access 2000 Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30
Access 2000 and 97 File Compatibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30
Undo and Redo Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31
Choice of Subform and Subreport Design Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31
Added Shortcut Key Combinations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32
PivotTable and PivotChart Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32
Access Data Projects and SQL Server 2000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34
New SQL Server 2000 Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34
SQL Server 2000 Desktop Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35
Changes to ADP Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37
Live Web Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39
Data Access Pages Revisited . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40
Save as Data Access Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40
Office Web Components Licensing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41
New DAP Design View Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42
Programmability Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43
Access 2002 Events for Forms, Controls, and Batch Updates . . . . . . . . . . . . . . . . . . . . . . . . .44
Access 2002 Properties and the Printers Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44
Methods Added by Access 2002 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45
Visual Basic Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46
SQL Server 2000 Desktop Engine Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47
Running the MSDE Setup Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48
Using SQL Service Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50
In the Real World—Still Waiting for “Access .NET” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51
00 0789729520 fm 8/22/03 2:31 PM Page v
Contents v
2 Building a Simple Desktop and Web Application 53
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Understanding Access’s Approach to Application Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54
Creating an Access Application from a Template File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55
Touring the Contact Management Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60
Table Objects in the Database Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60
The Switchboard Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64
Access Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66
Access Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .70
Access Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71
Using the Switchboard Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72
Exploring Form Design View and VBA Class Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .74
Downloading Templates from the Microsoft Office Update Site . . . . . . . . . . . . . . . . . . . . . . . . . .76
Saving a Form as a Data Access Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .78
In the Real World—Putting What You’ve Learned in Perspective . . . . . . . . . . . . . . . . . . . . . . . . .82
3 Navigating the Access User Interface 83
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Understanding Access Functions and Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .84
Defining Access Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .84
Defining Access Operating Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .86
Opening the Northwind.mdb Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .87
Understanding Access’s Table Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .88
The Toolbars in Table Datasheet View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90
Toolbar Customization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .94
Right-Click Shortcut Menus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .96
Using the Function Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97
Global Function Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97
Function-Key Assignments for Fields, Grids, and Text Boxes . . . . . . . . . . . . . . . . . . . . . . . . .98
Setting Default Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99
View Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .100
General Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101
Edit/Find Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .104
Keyboard Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .105
Defaults for Datasheet View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .105
Spelling Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107
Error Checking Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107
Advanced Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107
Using Access Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
The Help Menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
The Access 2003 Help Task Pane and Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .110
00 0789729520 fm 8/22/03 2:31 PM Page vi
vi Special Edition Using Microsoft Office Access 2003
Using the Database Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112
Compacting and Repairing Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .113
Converting Databases to Access 2002 Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115
Converting Databases to Access 97 Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .116
Creating .mde Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .116
Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117
In the Real World—HTML Help or Hindrance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .118
II Learning the Fundamentals of Jet Databases
4 Exploring Relational Database Theory and Practice 123
. . . . . . . . . . . . . . . . . . . . . . . . . .
Moving from Spreadsheets to Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .124
Reliving Database History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .125
The Early History of Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .125
The Relational Database Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .126
Client/Server and Desktop RDBMSs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .127
Defining the Structure of Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .129
Removing Data Redundancy with Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .130
Conforming to Table Design Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .133
First Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .133
Second Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Third Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .136
Fourth Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
Fifth Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
Choosing Primary Key Codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .138
Maintaining Data Integrity and Accuracy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .140
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .140
Entity Integrity and Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .141
Data Validation Rules and Check Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .141
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142
Displaying Data with Queries and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142
Partitioning Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .146
In the Real World—When and Why Learn Relational Theory? . . . . . . . . . . . . . . . . . . . . . . . . . . .147
5 Working with Jet Databases and Tables 151
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Understanding Jet Database Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .152
Jet Workgroup Information Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .153
Jet Record-Locking Information Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .153
Jet Add-In (Library) Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .154
Creating a New Jet Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .154
Exploring the Properties of Tables and Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .156
Table Properties for Subdatasheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .157
Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .159
00 0789729520 fm 8/22/03 2:31 PM Page vii
Contents vii
Choosing Field Data Types, Sizes, and Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .162
Choosing Field Sizes for Numeric and Text Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .164
Selecting a Display Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .167
Using Input Masks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .173
Working with the Northwind Traders Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .174
Using the Table Wizard to Add a New Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .175
Preparing to Add a Table Related to the Employees Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .178
Designing the HRActions Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .179
Determining What Information the Table Should Include . . . . . . . . . . . . . . . . . . . . . . . . . . .180
Assigning Information to Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .181
Creating the HRActions Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .182
Creating a Table Directly in Datasheet View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .186
Setting Default Values of Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .187
Working with Relations, Key Fields, and Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188
Establishing Relationships Between Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .189
Cascading Updates and Deletions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .192
Selecting a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .193
Adding Indexes to Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .194
Altering Fields and Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .196
Rearranging the Sequence of Fields in a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .197
Changing Field Data Types and Sizes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .198
Changing Relationships Between Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .199
Working with Object Dependencies and Access Smart Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . .200
Enabling and Viewing Object Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .200
Activating the Access Property Options Smart Tag . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .202
Adding an Internet-Based Smart Tag to a Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .203
Copying and Pasting Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .206
Using the Table Analyzer Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .207
Generating a Data Dictionary with the Database Documenter . . . . . . . . . . . . . . . . . . . . . . . . . .212
Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215
In the Real World—Database Strategy and Table Tactics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215
6 Entering, Editing, and Validating Jet Table Data 217
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Entering Data in Access Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .218
Using Keyboard Operations for Entering and Editing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .218
Creating a Working Copy of Northwind.mdb . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .219
Setting Data-Entry Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .219
Using Data-Entry and Editing Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .221
Using Key Combinations for Windows Clipboard Operations . . . . . . . . . . . . . . . . . . . . . . .223
Using Shortcut Keys for Fields and Text Boxes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .224
Adding Records to a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .225
00 0789729520 fm 8/22/03 2:31 PM Page viii
viii Special Edition Using Microsoft Office Access 2003
Selecting, Appending, Replacing, and Deleting Table Records . . . . . . . . . . . . . . . . . . . . . . . . . .226
Validating Data Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .227
Adding Field-Level Validation Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .228
Adding Table-Level Validation Rules with the Expression Builder . . . . . . . . . . . . . . . . . . . .230
Adding a Simple Lookup List to the ActionType Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .232
Adding Records to the HRActions Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .233
Testing Field and Table Validation Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .235
Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237
In the Real World—Heads-Down Data Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237
Comparing Heads-Down Keypunch Data Entry with Access’s Datasheet View . . . . . . .238
Replacing the Punched Card Verifying Step . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .238
Where Not to Use Datasheet Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .239
7 Sorting, Finding, and Filtering Data 241
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Understanding the Role of Sorting and Filtering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .242
Sorting Table Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .242
Freezing Display of a Table Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .243
Sorting Data on a Single Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244
Sorting Data on Multiple Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244
Removing a Table Sort Order and Thawing Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .245
Finding Matching Records in a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .245
Replacing Matched Field Values Automatically . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .248
Filtering Table Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .249
Filtering by Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .249
Using the Filter For Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .251
Filtering by Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .251
Applying Advanced Filters and Sort Orders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .255
Adding a Multifield Sort and Compound Filter Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . .256
Using Composite Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .258
Saving Your Filter as a Query and Loading a Filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .259
Applying a Saved Query as a Filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .261
Customizing Table Datasheet View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .261
Copying, Exporting, and Mailing Sorted and Filtered Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . .264
Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .264
In the Real World—Computer-Based Sorting and Searching . . . . . . . . . . . . . . . . . . . . . . . . . . . .265
The Influence of Computer Power on Knuth’s Approach . . . . . . . . . . . . . . . . . . . . . . . . . . .265
Knuth and Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .266
00 0789729520 fm 8/22/03 2:31 PM Page ix
Contents ix
8 Linking, Importing, and Exporting Data 267
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Moving Data from and to Other Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .268
Working with Tables in Other Database File Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .269
Dealing with PC Database Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .270
Linking and Importing External ISAM Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .272
Linking Visual FoxPro Tables with ODBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .274
Dealing with Images in External Database Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .278
Converting Field Data Types to Jet Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .281
Using the Linked Table Manager Add-in to Relink Tables . . . . . . . . . . . . . . . . . . . . . . . . . . .282
Importing Versus Linking Database Files as Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .283
Importing and Linking Spreadsheet Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .284
Creating a Table by Importing an Excel Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .284
Linking Excel Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .292
Working with Microsoft Outlook and Exchange Folders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .293
Importing and Exporting Jet 4.0 Tables with Outlook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .294
Linking with the Exchange/Outlook Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .298
Importing Text Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .300
Using the Import Text Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .301
Setting the Import Text Wizard’s Advanced Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .305
Using the Access Mail Merge Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .308
Creating and Previewing a New Form Letter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .308
Using an Existing Main Merge Document with a New Data Source . . . . . . . . . . . . . . . . . .312
Exporting Tables to Word and Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .315
Exporting Data to Web Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .316
Saving an .htm File to a Web Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .316
Exporting Tables to Active Server Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .318
Exporting Table Data as Text Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .320
Exporting Data in Other File Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .322
Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .322
In the Real World—Microsoft Giveth and Microsoft Taketh Away . . . . . . . . . . . . . . . . . . . . . . .323
III Transforming Data with Queries and PivotTables
9 Designing Queries for Jet Databases 327
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Introducing Jet Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .328
Trying the Simple Query Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .328
Creating a Simple SELECTQuery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .329
Using the Query Design Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .334
Selecting Fields for Your Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .335
Selecting Records by Criteria and Sorting the Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .338
Preventing Updates to the Query Result Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .340
Creating More Complex Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .341
00 0789729520 fm 8/22/03 2:31 PM Page x
x Special Edition Using Microsoft Office Access 2003
Changing the Names of Query Column Headers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .343
Printing Your Query as a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .345
Creating Other Types of Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .346
Creating and Using a Simple Make-Table Action Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . .347
Adding a Parameter to Your Make-Table Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .349
Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .350
In the Real World—Optimizing Query Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .352
The Art of Query Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .352
The Scientific Side of Query Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .352
10 Understanding Jet Operators and Expressions 355
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Writing Expressions for Query Criteria and Data Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . .356
Understanding the Elements of Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .357
Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .358
Arithmetic Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .359
Assignment and Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .360
Logical Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .360
Concatenation Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .361
Identifier Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .362
Other Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .362
Literals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .363
Identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .364
Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .365
Using the Immediate Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .366
Getting Help as You Write Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .367
The VariantData Type in Jet and VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .369
Functions for Date and Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .370
Text-Manipulation Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .373
Numeric, Logical, Date/Time, and String Data-Type Conversion Functions . . . . . . . . . .377
Intrinsic and Named Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .379
Creating Jet Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .379
Expressions for Creating Default Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .379
Expressions for Validating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .380
Expressions for Query Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .381
The SQL Server Version of a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .387
Expressions for Calculating Query Field Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .389
Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .391
In the Real World—The Algebra of Access Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .392