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 (425) 936-7329. 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
Description: