Microsoft Office Access 2003 Inside Out

1289 Pages·2004·29.339 MB·English

by  John L. Viescas

PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2004 by John L. Viescas
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form
or by any means without the written permission of the publisher.
Library of Congress Cataloging-in-Publication Data
Viescas, John, 1947-
Microsoft Office Access 2003 Inside Out / John L. Viescas.
p. cm.
Includes index.
ISBN 0-7356-1513-6
1. Database management. 2. Microsoft Access. I. Title.
QA76.9.D3V545 2003
005.75'65--dc21 2003056160
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWT 8 7 6 5 4 3
Distributed in Canada by H.B. Fenn and Company Ltd.
A CIP catalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide. For further informa-
tion about international editions, contact your local Microsoft Corporation office or contact Microsoft
Press International directly at fax [phone]. Visit our Web site at www.microsoft.com/mspress.
Send comments to [email protected].
ActiveX, FoxPro, FrontPage, InfoPath, Microsoft, Microsoft Internet Explorer (logo only), Microsoft
Press, MSDN, MS-DOS, MSN, the Office logo, OpenType, Outlook, PivotChart, PivotTable, PowerPoint,
SharePoint, Visual Basic, Visual C# , Visual C++ , Visual FoxPro, Visual J#, Visual Studio, the
Microsoft Visual Tools eMbedded logo, Windows, and Windows NT are either registered trademarks or
trademarks of Microsoft Corporation in the United States and/or other countries. Other product and
company names mentioned herein may be the trademarks of their respective owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places,
and events depicted herein are fictitious. No association with any real company, organization, product,
domain name, e-mail address, logo, person, place, or event is intended or should be inferred.
Acquisitions Editor: Alex Blanton
Project Editor: Sandra Haynes
Series Editor: Sandra Haynes
Technical Editor: Curt Philips
Body Part No. X09-71422
For my bestest pal in the whole wide world. Without your love and
support, we wouldn’t have made this project happen.
And for mom. I finished this book just in time for your 90th birthday. I
hope I’ve made you proud.

Contents at a Glance
Part 1 Part 3
Understanding Microsoft Creating Forms and Reports
Access in a Desktop Application
Chapter 1 Chapter 10
What Is Microsoft Access?. . . . . . . 3 Using Forms . . . . . . . . . . . . . . . 365
Chapter 2 Chapter 11
The Many Faces of Building a Form . . . . . . . . . . . . . 397
Microsoft Access. . . . . . . . . . . . . 17
Chapter 12
Chapter 3 Customizing a Form. . . . . . . . . . 433
Designing Your Database
Application. . . . . . . . . . . . . . . . . . 53 Chapter 13
Advanced Form Design. . . . . . . . 481
Part 2
Building a Microsoft Access Chapter 14
Using Reports . . . . . . . . . . . . . . 523
Desktop Application
Chapter 15
Chapter 4 Constructing a Report . . . . . . . . 537
Creating Your Database
and Tables . . . . . . . . . . . . . . . . . . 85 Chapter 16
Advanced Report Design . . . . . . 555
Chapter 5
Modifying Your Table Design. . . . 139 Part 4
Designing an
Chapter 6
Access Project
Importing and Linking Data. . . . . 179
Chapter 7 Chapter 17
Creating and Working Building Tables in an
with Simple Queries . . . . . . . . . . 219 Access Project . . . . . . . . . . . . . 599
Chapter 8 Chapter 18
Building Complex Queries. . . . . . 277 Building Queries in an
Access Project . . . . . . . . . . . . . 637
Chapter 9
Modifying Data with Chapter 19
Action Queries . . . . . . . . . . . . . . 337 Designing Forms in an
Access Project . . . . . . . . . . . . . 685
Chapter 20
Building Reports in an
Access Project . . . . . . . . . . . . . 701
v
Contents at a Glance
Part 5 Part 7
Automating an Access After Completing Your
Application Application
Chapter 21 Chapter 29
Understanding Event Upsizing a Desktop
Processing. . . . . . . . . . . . . . . . . 713 Application to a Project . . . . . . 1133
Chapter 22 Chapter 30
Understanding Visual Securing Your Database. . . . . . 1163
Basic Fundamentals. . . . . . . . . . 757
Chapter 31
Chapter 23 Distributing Your Application . . 1203
Automating Your Application
with Visual Basic . . . . . . . . . . . . 857
Part 8
Appendix
Chapter 24
The Finishing Touches . . . . . . . . 927
Appendix
Installing Microsoft Office . . . . 1223
Part 6
Linking Access and
Part 9
the Web
Articles
Chapter 25
Article 1
Publishing Data on the Web . . . . 961
Understanding SQL . . . . . . . . . . . A1
Chapter 26
Article 2
Creating Static and
Exporting Data . . . . . . . . . . . . . A45
Dynamic Web Pages. . . . . . . . . . 985
Article 3
Chapter 27
Visual Basic Function
Building Data Access Pages. . . 1015
Reference . . . . . . . . . . . . . . . . . A49
Chapter 28
Article 4
Working with XML
Internet Explorer Web Page
and SharePoint. . . . . . . . . . . . . 1095
Color Names . . . . . . . . . . . . . . . A59
vi
Table of Contents
Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii
We’d Like to Hear from You!. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
About the CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvii
What’s on the CD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
Sample Applications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxviii
Using the CD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxixi
System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix
Support Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix
Conventions and Features Used in This Book . . . . . . . . . . . . . . . . . . . . . . . .xxxi
Text Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxi
Design Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxi
Syntax Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxiii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxv
Microsoft Access Today . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxvi
About This Book. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvii
Part 1
Understanding Microsoft Access
Chapter 1
What Is Microsoft Access? 3
What Is a Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Relational Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Database Capabilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Microsoft Access as an RDBMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Data Definition and Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Data Manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Data Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Microsoft Access as an Application Development System . . . . . . . . . . . . . 11
Deciding to Move to Database Software. . . . . . . . . . . . . . . . . . . . . . . . . . 13
Chapter 2
The Many Faces of Microsoft Access 17
The Architecture of Microsoft Access. . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Exploring a Desktop Database—Housing Reservations. . . . . . . . . . . . . . . 19
Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Data Access Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
vii
Table of Contents
Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Organizing Your Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Exploring a Project File—LawTrack Contacts. . . . . . . . . . . . . . . . . . . . . . . 48
Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Views, Functions, and Stored Procedures. . . . . . . . . . . . . . . . . . . . . 50
The Many Faces of Microsoft Access. . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Chapter 3
Designing Your Database Application 53
Application Design Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Step 1: Identifying Tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Step 2: Charting Task Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Step 3: Identifying Data Elements. . . . . . . . . . . . . . . . . . . . . . . . . . 55
Step 4: Organizing the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Step 5: Designing a Prototype and a User Interface . . . . . . . . . . . . . 55
Step 6: Constructing the Application . . . . . . . . . . . . . . . . . . . . . . . . 55
Step 7: Testing, Reviewing, and Refining . . . . . . . . . . . . . . . . . . . . . 56
An Application Design Strategy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Analyzing the Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Selecting the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Organizing Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Data Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Choosing the Database Subjects. . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Mapping Subjects to Your Database . . . . . . . . . . . . . . . . . . . . . . . . 66
Database Design Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Waste Is the Problem. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Normalization Is the Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Efficient Relationships Are the Result . . . . . . . . . . . . . . . . . . . . . . . 77
When to Break the Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Improving Performance of Critical Tasks. . . . . . . . . . . . . . . . . . . . . . 79
Capturing Point-In-Time Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Creating Report Snapshot Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Part 2
Building a Microsoft Access Desktop Application
Chapter 4
Creating Your Database and Tables 85
Creating a New Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Using a Database Template to Create a Database . . . . . . . . . . . . . . 86
Creating a New Empty Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Creating Your First Simple Table by Entering Data. . . . . . . . . . . . . . . . . . . 92
Creating a Table Using the Table Wizard. . . . . . . . . . . . . . . . . . . . . . . . . . 94
Creating a Table in Design View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
viii
Table of Contents
Defining Fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Understanding Field Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Setting Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Completing the Fields in the Companies Table . . . . . . . . . . . . . . . . 110
Defining Simple Field Validation Rules. . . . . . . . . . . . . . . . . . . . . . 111
Defining Input Masks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Defining a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Defining a Table Validation Rule. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Understanding Other Table Properties . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Defining Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Defining Your First Relationship. . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Creating a Relationship on Multiple Fields . . . . . . . . . . . . . . . . . . . 128
Adding Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Single Field Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Multiple-Field Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Setting Table Design Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Printing a Table Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Database Limitations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Chapter 5
Modifying Your Table Design 139
Before You Get Started. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Making a Backup Copy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Checking Object Dependencies. . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Deleting Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Renaming Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Changing Field Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Moving Fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Inserting Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Copying Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Deleting Fields. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Changing Data Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Changing Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Changing Data Lengths. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Dealing with Conversion Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Changing Other Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Reversing Changes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Using the Table Analyzer Wizard. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Taking a Look at Lookup Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Changing the Primary Key. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Compacting Your Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Chapter 6
Importing and Linking Data 179
A Word About Open Database Connectivity (ODBC). . . . . . . . . . . . . . . . . 179
Importing vs. Linking Database Files. . . . . . . . . . . . . . . . . . . . . . . . . . . 181
ix
Table of Contents
Importing Data and Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Importing dBASE Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Importing Paradox Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Importing SQL Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
Importing Access Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Importing Spreadsheet Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Preparing a Spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Importing a Spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Fixing Errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Importing Text Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Preparing a Text File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Importing a Text File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
Fixing Errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Modifying Imported Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Linking Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Security Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Performance Considerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Linking Access Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Linking dBASE and Paradox Files. . . . . . . . . . . . . . . . . . . . . . . . . . 213
Linking Text and Spreadsheet Files . . . . . . . . . . . . . . . . . . . . . . . . 213
Linking SQL Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Modifying Linked Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Unlinking Linked Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Using the Linked Table Manager . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Chapter 7
Creating and Working with Simple Queries 219
Selecting Data from a Single Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Specifying Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Setting Field Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Entering Selection Criteria. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Using Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Using the Expression Builder. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Specifying Field Names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Sorting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Testing Validation Rule Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Checking a New Field Validation Rule. . . . . . . . . . . . . . . . . . . . . . . 249
Checking a New Table Validation Rule . . . . . . . . . . . . . . . . . . . . . . 250
Working in Query Datasheet View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Moving Around and Using Keyboard Shortcuts . . . . . . . . . . . . . . . . 252
Working with Subdatasheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Changing Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Working with Hyperlinks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
Sorting and Searching for Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
x