Access 2002 Bible P1

Chia sẻ: Thach Sau | Ngày: | Loại File: PDF | Số trang:40

0
74
lượt xem
9
download

Access 2002 Bible P1

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Welcome to the Access 2002 Bible—your personal guide to a powerful, easyto- use database management system. This book is in its ninth revision and has been totally re-written for Access 2002.

Chủ đề:
Lưu

Nội dung Text: Access 2002 Bible P1

  1. 100% “I recommend this book for anyone who wants a strong foundation in Access.” —Jeff Lenamon, CIBC World Markets ONE HUNDRED PERCENT COMPREHENSIVE AUTHORITATIVE WHAT YOU NEED ONE HUNDRED PERCENT Build well designed tables, queries, forms, and reports Harness the power of functions, expressions, controls, and VBA in Office XP Put Access data on the Web using XML and Data Access Pages International bestseller with over 450,000 copies in print Access 2002 BONUS CD-ROM Source code, all examples, developer tools from Database Cary Prague and Michael Irwin Creations, and more! Microsoft Certified Professionals
  2. Access 2002 Bible Cary N. Prague and Michael R. Irwin Best-Selling Books • Digital Downloads • e-Books • Answer Networks • e-Newsletters • Branded Web Sites • e-Learning New York, NY ✦ Cleveland, OH ✦ Indianapolis, IN
  3. Access 2002 Bible For general information on Hungry Minds’ products Published by and services please contact our Customer Care Hungry Minds, Inc. department within the U.S. at 800-762-2974, outside 909 Third Avenue the U.S. at 317-572-3993 or fax 317-572-4002. New York, NY 10022 For sales inquiries and reseller information, including www.hungryminds.com discounts, premium and bulk quantity sales, and Copyright © 2001 Hungry Minds, Inc. All rights foreign-language translations, please contact our reserved. No part of this book, including interior Customer Care department at 800-434-3422, fax design, cover design, and icons, may be reproduced 317-572-4002 or write to Hungry Minds, Inc., Attn: or transmitted in any form, by any means (electronic, Customer Care Department, 10475 Crosspoint photocopying, recording, or otherwise) without the Boulevard, Indianapolis, IN 46256. prior written permission of the publisher. For information on licensing foreign or domestic Library of Congress Catalog Card No.: rights, please contact our Sub-Rights Customer Care department at 212-884-5000. ISBN: 0-7645-3596-X For information on using Hungry Minds’ products Printed in the United States of America and services in the classroom or for ordering 10 9 8 7 6 5 4 3 2 1 examination copies, please contact our Educational 1B/RZ/QW/QR/IN Sales department at 800-434-2086 or fax 317-572-4005. Distributed in the United States by For press review copies, author interviews, or other Hungry Minds, Inc. publicity information, please contact our Public Distributed by CDG Books Canada Inc. for Canada; Relations department at 650-653-7000 or fax by Transworld Publishers Limited in the United 650-653-7500. Kingdom; by IDG Norge Books for Norway; by IDG For authorization to photocopy items for corporate, Sweden Books for Sweden; by IDG Books Australia personal, or educational use, please contact Publishing Corporation Pty. Ltd. for Australia and Copyright Clearance Center, 222 Rosewood Drive, New Zealand; by TransQuest Publishers Pte Ltd. for Danvers, MA 01923, or fax 978-750-4470. Singapore, Malaysia, Thailand, Indonesia, and Hong Kong; by Gotop Information Inc. for Taiwan; by ICG Muse, Inc. for Japan; by Intersoft for South Africa; by Eyrolles for France; by International Thomson Publishing for Germany, Austria, and Switzerland; by Distribuidora Cuspide for Argentina; by LR International for Brazil; by Galileo Libros for Chile; by Ediciones ZETA S.C.R. Ltda. for Peru; by WS Computer Publishing Corporation, Inc., for the Philippines; by Contemporanea de Ediciones for Venezuela; by Express Computer Distributors for the Caribbean and West Indies; by Micronesia Media Distributor, Inc. for Micronesia; by Chips Computadoras S.A. de C.V. for Mexico; by Editorial Norma de Panama S.A. for Panama; by American Bookshops for Finland. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND AUTHOR HAVE USED THEIR BEST EFFORTS IN PREPARING THIS BOOK. THE PUBLISHER AND AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS BOOK AND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. THERE ARE NO WARRANTIES WHICH EXTEND BEYOND THE DESCRIPTIONS CONTAINED IN THIS PARAGRAPH. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES OR WRITTEN SALES MATERIALS. THE ACCURACY AND COMPLETENESS OF THE INFORMATION PROVIDED HEREIN AND THE OPINIONS STATED HEREIN ARE NOT GUARANTEED OR WARRANTED TO PRODUCE ANY PARTICULAR RESULTS, AND THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY INDIVIDUAL. NEITHER THE PUBLISHER NOR AUTHOR SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR. Trademarks: All trademarks are the property of their respective owners. Hungry Minds, Inc., is not associated with any product or vendor mentioned in this book. is a trademark of Hungry Minds, Inc.
  4. About the Authors Cary N. Prague is an internationally best selling author and lecturer in the database industry. He owns Database Creations, Inc., the world’s largest Microsoft Access add-on company. Their products include a line of financial software; Business! for Microsoft Office, a mid-range accounting system, POSitively Business! Point of Sale software, the Inventory Barcode manager for mobile data collection, and the Check Writer and General Ledger. Database Creations also produces a line of developer tools including the appBuilder, an application generator for Microsoft Access, the EZ Access Developer Tools for building great user interfaces, appWatcher for maintaining code bases among several developers, and Surgical Strike, the only Patch Manager for Microsoft Access. Cary also owns Database Creations Consulting, LLC., a successful consulting firm spe- cializing in Microsoft Access and SQL Server applications. Local and national clients include many Fortune 100 companies including manufacturers, defense contractors, insurance, health-care, and software industry companies. His client list includes Microsoft, United Technologies, ABB, Smith & Wesson Firearms, Pratt and Whitney Aircraft, ProHealth, OfficeMax, Continental Airlines, and other Fortune 500 companies. Formerly, he has held numerous management positions in corporate information sys- tems, including Director of Managed Care Reporting for MetraHealth, Director of Corporate Finance and Software Productivity at Travelers Insurance where he was responsible for software support for 35,000 end users, and Manager of Information Systems support for Northeast Utilities. He is one of the top best-selling authors in the computer database management market having written over forty books which have sold over one million copies on software including Microsoft Access, Borland (Ashton-Tate) dBASE, Paradox, R:Base, Framework, and graphics. Cary’s books include eleven books in the Access Bible series (recently number one on the Ingram Bestselling Database Titles list and in the Amazon.com top 100), Access 97 Secrets, dBASE for Windows Handbook, dBASE IV Programming (winner of the Computer Press Association’s Book of the Year award for Best Software Specific Book), and Everyman’s Database Primer Featuring dBASE IV. He previously completed three books for Access 2000 including Weekend Crash Course in Access 2000 Programming. Cary recently sold a product line named eTools for Microsoft Access to MightyWords, a division of FatBrain.com and Barnes and Noble. Cary is certified in Access as a Microsoft Certified Professional and has passed the MOUS test in Access and Word. He is a frequent speaker at seminars and conferences around the country. He is on the exclusive Microsoft Access Insider Advisory Board and makes frequent trips to Microsoft headquarters in Redmond, WA. He has been voted the best speaker by the attendees of several national conferences. Recently, he was a speaker for Microsoft sponsored conferences in New Orleans, Hawaii, Phoenix, Chicago, Toronto, Palm Springs, Boston, and Orlando. He has also spoken at Borland’s Database Conference, Digital Consulting’s Database World, Microsoft’s Developer Days, Computerland’s Technomics Conference, COMDEX, and COMPAQ Computer’s Innovate. He was a contributing editor to Access Advisor magazine and has written for the Microsoft Office Developer’s journal. Cary holds a master’s degree in computer science from Rensselaer Polytechnic Institute, and an M.B.A. and Bachelor of Accounting from the University of Connecticut. He is also a Certified Data Processor.
  5. Michael R. Irwin is considered one of the leading authorities on automated database and Internet management systems today. He is a noted worldwide lecturer, a winner of national and international awards, best-selling author, and developer of client/server, Internet, Intranet, and PC-based database management systems. Mr. Irwin has extensive database knowledge, gained by working with the Metropolitan Police Department in Washington, D.C. as a developer and analyst for the Information Systems Division for over 20 years and assorted Federal Agencies of the United States Government. Since retiring in June 1992, he runs his own con- sulting firm, named The Irwin Group, and is principal partner in the company - IT in Asia, LLC, specializing in Internet database integration and emphasizing Client/Server and net solutions. With consulting offices in Cincinnati Ohio, Bangkok Thailand, and Manila Philippines, his companies offer Training and Development of Internet and database applications. His company has the distinction of being one of the first Microsoft Solution’s Providers (in 1992). His local, national, and interna- tional clients include many software companies, manufacturers, government agen- cies, and international companies. His range of expertise includes database processing and integration between main- frame, minicomputer, and PC-based database systems, as well as B-2-B and B-2-C integration between back-end databases; he is a leading authority on PC-based databases. He is one of the top best-selling authors in the computer database management market having authored numerous database books, with several of them consis- tently on the best sellers lists. His books, combined, have sold nearly a million copies worldwide. His most recent works include The OOPs Primer (Borland Press, dBASE 5.5 for Windows Programming (Prentice Hall), Microsoft Access 2000 Bible, Microsoft Access 2000 Bible Gold Edition (co-authored), and Working with the Internet. The Access Bible series have recently been number one on the Ingram Best- selling Database Titles list and is always in the Amazon.com top 100. He has also written several books on customs and cultures of the countries of Asia (including China, Japan, Thailand, and India). Two of his books have won international acclaim. His books are published in over 24 languages worldwide. He has been a contributing editor and author to many well-known magazines and journals. He is a frequent speaker at seminars and conferences around the world and has been voted the best speaker by the attendees of several international conferences. Michael has developed and markets several add-on software products for the Internet and productivity related applications. Many of his productivity applica- tions can be obtained from several of his Internet sites or on many common down- load sites. Many of his application and systems are distributed as freeware and careware. He has also developed and distributes several development tools and add-ins for a wide range of developer applications.
  6. Credits Acquisitions Editor Quality Control Technicians Greg Croy Laura Albert, David Faust, John Greenough, Andy Hollandbeck, Project Editors Susan Moritz, Charles Spencer Janet Andrews, Erik Schwab, Pat O’Brien Permissions Editor Technical Editor Carmen Krikorian Maryann Brown Media Development Specialist Copy Editors Megan Decraene Rebekah Mancilla, Michelle Shaw Media Development Coordinator Project Coordinator Marisa Pearman Jennifer Bingham Proofreading and Indexing Graphics and Production Specialists TECHBOOKS Production Services Kristen Pickett, Jill Piscitelli, Kendra Span, Laurie Stevens, Brian Torwelle, Erin Zeltner This book is dedicated to the people closest to me. First and foremost it is dedicated to my family — my wife, Dra. Arni Lim-Irwin, my sons Richard Rocco, Joseph Patrick, and David Joseph. While writing this book, I had to be separated from my family for over three months, who where in another country, to finish on deadline. It is also dedicated to my mother, Aurelia Irwin, 72 years young and as strong as ever — who has continued to be my sounding board and example of strength. Finally, my partner, Richard Mahonski, who is ‘holding down the fort’ in Asia while I write this book under a horrific deadline! — MRI This book is dedicated to the memory of my father who died in 1999. I miss him more every year. My Dad gave me strength, motivation, and pushed me beyond my limits. I can only hope that my sons — David, Jeffrey, and Alex — someday will understand how proud I am of them and how I too will relentlessly push them to do their best and beyond, no matter what. This book is also dedicated to my wife Karen, who puts up with more than she should have to and whom I also hope someday realizes why I push our children so hard. Success is not without a struggle, and no goal is ever achieved unless there is another right behind it. — CNP
  7. Preface W elcome to the Access 2002 Bible — your personal guide to a powerful, easy- to-use database management system. This book is in its ninth revision and has been totally re-written for Access 2002. It examines Access 2002 with more examples than any other Access book ever writ- ten. We think that Microsoft Access is an excellent database manager and the best Windows database on the market today. Our goal with this book is to share what we know about Access and, in the process, to help make your work and your life easier. This book contains everything you need in order to learn Microsoft Access to a mid-advanced level. You’ll find that the book starts off with the basics and builds, chapter by chapter, on topics previously covered. In places where it is essential that you understand previously covered topics, we present the concepts again and review how to perform specific tasks before moving on. Although each chapter is an integral part of the book as a whole, each chapter can also stand on its own. You can read the book in any order you want, skipping from chapter to chapter and from topic to topic. (Note that this book’s index is particularly thorough; you can refer to the index to find the location of a particular topic you’re interested in.) The examples in this book have been well thought out to simulate the types of tables, queries, forms, and reports most people need to create when performing common business activities. There are many notes, tips, and techniques (and even a few secrets) to help you better understand the product. This book can easily substitute for the manuals included with Access. In fact, many users do not get manuals today, often relying on just the online help. This book will guide you through each task you might want to do in Access. We even created appendixes to be used as reference manuals for common Access specifications. This book follows a much more structured approach than the Microsoft Access manuals — going into more depth on almost every topic and showing many differ- ent types of examples. Is This Book for You? We wrote this book for beginning, intermediate, and even advanced users of Microsoft Access 2002. With any product, most users start at the beginning. If, how- ever, you’ve already read through the Microsoft Access manuals and worked with
  8. viii Access 2002 Bible the Northwinds sample files, you may want to start with the later parts of this book. Note, however, that starting at the beginning of a book is usually a good idea so you don’t miss out on the secrets and tips in the early chapters. We think this book covers Microsoft Access in detail better than any other book currently on the market. We hope you will find this book helpful while working with Access, and that you enjoy the innovative style of a Hungry Minds book (formally IDG books). Yes — If you have no database experience If you’re new to the world of database management, this book has everything you need to get started with Microsoft Access. It then offers advanced topics for refer- ence and learning. Yes — If you’ve used other database managers like dBASE or Paradox If you’re abandoning another database (such as dBASE, Paradox, FoxPro, R:Base, or Alpha Four) or even upgrading from Access 2.0 or Access 95 or 97, this book is for you. You’ll have a head start because you’re already familiar with database man- agers and how to use them. With Microsoft Access, you will be able to do all the tasks you’ve always performed with character-based databases — without program- ming or getting lost. This book will take you through each subject step by step. Yes — If you want to learn the basics of Visual Basic Applications Edition (VBA) programming VBA has replaced the Access Basic language. We know that an entire book is needed to properly cover VBA, but we took the time to put together several intro- ductory chapters that build on what you learn in the macros chapters of this book. The VBA programming chapters use the same examples you will be familiar with by the end of the book. Conventions Used in This Book The following conventions are used in this book: ✦ When you are instructed to press a key combination (press and hold down one key while pressing another key), the key combination is separated by a plus sign. Ctrl+Esc, for example, indicates that you must hold down the Ctrl key and press the Esc key; then release both keys.
  9. Preface ix ✦ Point the mouse refers to moving the mouse so that the mouse pointer is on a specific item. Click refers to pressing the left mouse button once and releasing it. Double-click refers to pressing the left mouse button twice in rapid succes- sion and then releasing it. Right-click refers to pressing the right mouse but- ton once and releasing it. Drag refers to pressing and holding down the left mouse button while moving the mouse. ✦ When you are instructed to select a menu, you can use the keyboard or the mouse. To use the keyboard, press and hold down the Alt key (to activate the menu bar) and then press the underlined letter of the menu name; press Alt+E to select the Edit menu, for example. Or you can use the mouse to click on the word Edit on-screen. Then, from the menu that drops down, you can press the underlined letter of the command you want (or click on the command name) to select it. ✦ When you are instructed to select a command from a menu, you will often see the menu and command separated by an arrow symbol. Edit_Paste, for exam- ple, indicates that you need to select the Edit menu and then choose the Paste command from the menu. ✦ Italic type is used for new terms and for emphasis. ✦ Bold type is used for material you need to type directly into the computer. ✦ A special typeface is used for information you see on-screen — error mes- sages, expressions, and formulas, for example. Icons and Alerts You’ll notice special graphic symbols, or icons, used in the margins throughout this book. These icons are intended to alert you to points that are particularly impor- tant or noteworthy. The following icons are used in this book: Note This icon highlights a special point of interest about the topic under discussion. Tip This icon points to a useful hint that may save you time or trouble. Caution This icon alerts you that the operation being described can cause problems if you’re not careful. Cross- This icon points to a more complete discussion in another chapter of the book. Reference
  10. x Access 2002 Bible Sidebars In addition to noticing the icons used throughout this book, you will also notice material placed in gray boxes. This material offers background information, an expanded discussion, or a deeper insight about the topic under discussion. Some sidebars offer nuts-and-bolts technical explanations, and others provide useful anecdotal material. On the This icon highlights information for readers who are following the examples and CD-ROM using the sample files included on the disk accompanying this book. New This icon calls attention to new features of Access 2002. Feature How This Book Is Organized This book contains 36 chapters divided into six main parts. In addition, the book contains five appendixes. Part I: First Things First Part I consists of the first four chapters of the book. In Chapter 1, you receive back- ground information on Microsoft Access and an overview of its features. Chapter 2 covers installation — what you need in terms of hardware and software, as well as how to get Access running properly. You also learn how to start and stop Access, plus several techniques for moving between Access and other applications. Chapter 3 provides a quick hands-on test drive of Access, offering you a quick look at the features of access. Chapter 4 gives an in-depth explanation of database con- cepts for new users of a database product and is a case study of the up-front design that is necessary to properly implement a database system; otherwise, you must go through many false starts and redesigns when creating an application. You will design on paper the tables, forms, queries, reports, and menus necessary for creating the application. Part II: Creating the Objects The next six chapters make up Part II. You learn how to create a database table in Chapter 5, and you also examine how to change a database table, including moving and renaming fields without losing data. You will also learn about the new data types in Access, including the new Internet data types. In Chapter 6 you will learn
  11. Preface xi about creating many tables and relating tables and using the Relationship builder tool in Access. In Chapter 7, you learn how to enter, display, change, and delete data. Chapter 8 teaches the basics of creating data-entry forms and using Wizards to simplify the creation process; using data-entry forms is also discussed. In Chapter 9, you examine the concept of queries; then you create several queries to examine how data can be rearranged and displayed. Chapter 10 covers the basics of report creation and printing. Part III: Using Access in Your Work Part III contains 11 chapters that go into more depth on creating and using forms, queries, and reports. In Chapter 11 you examine the meanings of operators, func- tions, and expressions. This chapter also covers many of the more common func- tions and expressions you will use in Access. You will also learn how to create the expressions and built-in functions that are so important in forms and reports. In Chapter 12, you learn how to create relations and joins in queries. Chapter 13 dis- cusses basic selection queries, using many examples and pictures. In Chapter 14, you examine the concepts of controls and properties, and then learn how to manip- ulate controls in a form. Chapter 15 examines in detail how to create and use data- entry forms. Chapter 16 covers how to use visual effects to create great-looking forms and reports that catch the eye and increase productivity. In Chapter 17, you learn how to add complex data validation to tables and data-entry forms. Chapter 18 explains the use of pictures, graphs, sound, video, and other OLE objects. Chapters 19–21 cover reports — from simple controls to complex calculations, sum- maries, printing, and desktop publishing. Part IV: Advanced Access Database Topics This part contains six chapters that present advanced topics on each of the basic tasks of Access. Chapter 22 examines how to import, export, and attach external files, and how to copy Access objects to other Access databases. Chapter 23 dis- cusses advanced select query topics, including total, cross-tabulation, top-value, and union queries. Chapter 24 covers action queries, which change data rather than simply displaying records. Chapter 25 is a compendium of advanced query topics that will leave you amazed at the power of Access. Creating forms and sub- forms from multiple tables is the subject of Chapter 26; this chapter examines how to create the one-to-many relationship found in many database systems. Part IV ends with Chapter 27, which offers a look at additional types of reports not previ- ously covered, including mail-merge reports and mailing labels.
  12. xii Access 2002 Bible Part V: Access Application Tools This part contains five chapters and looks at Access as an application environment. Chapter 28 covers the concept of event-driven software and how Access uses macros to automate manual processes. This chapter also examines what a macro is, how macros are created, and how to debug them. Chapter 29 demonstrates how to use Macros in Forms and Reports. In Chapter 30 and 31 you work with Visual Basic for Applications (VBA) in Access with focus on using VBA in forms and reports; it explains data manipulation, including posting totals and filling in data- entry fields. In Chapter 32, you learn how to create button menus known as switch- boards, as well as traditional pull-down menus, custom toolbars, and dialog boxes using the new Access Tab Control. In Access 2002, you will also learn about the new command bars used to build menus and toolbars. Part VI: Access and the External World The four chapters of Part VI are the last part of the book and focuses on using Access to work SQL Server, Access projects and Data Access Pages, XML, and the Internet. In Chapter 33, you will learn about client/server topics including installing, starting, connecting to, and building Access Data Projects using the new Microsoft Database Engine. It also focuses on moving from the local ‘Jet Engine’ to the Microsoft Database Engine and SQL Server, focusing on the issues surrounding the two different engines and how to use tables in SQL Server. Chapter 34 shows how to work with Access Projects and the advantage of using them. Chapters 35 and 36 cover the Web and Access. Chapter 35 shows how to create and maintain HTML documents from Access Objects. Chapter 36 is dedicated to Data Access Pages and XML and how they are used for creating live data links to intranet web pages and the other incredible web capabilities of Access 2002. Appendixes and Reference Material Four appendixes are included in this book. Appendix A presents a series of tables listing Access specifications, including maximum and minimum sizes of many of the controls in Access. Appendix B displays a database diagram of the many database tables used in this book so you can create your own system. Appendix C describes the CD-ROM. Appendix D is a complete cross-reference to all of the examples used in the book by object (table, query, form, etc.) and chapter.
  13. Acknowledgments W hen we first saw Access in July of 1992, we were instantly sold on this new- generation database management and access tool. Both of us have spent the last nine years using Access daily. In fact, we eat, breathe, live, and sleep Access! The fact that we can earn a living from our work on principally one product is a tribute to the designers of Microsoft Access. This product has changed the pro- ductivity of corporations and private citizens of the world. More people use this product to run their businesses, manage their affairs, track the most important things in their lives, and manage data in their work and play than any other product ever written. It is indeed a privilege to be part of this worldwide community. We have found readers in every county on the map and a few countries we never heard of. The global Internet age has allowed readers in emerging countries, in the Himalayan mountains, in Siberia, and even in Antarctica to contact us this year. Now we have completely rewritten this book for all the incredible new features in Access 2002. We’ve covered every new feature we could think of for the beginning user and especially enhanced our programming section. Over 450,000 copies of our Access Bibles have been sold for all versions of Microsoft Access; for this we thank all of our loyal readers. Our first acknowledgment is to all the users of Access who have profited and bene- fited beyond everyone’s wildest dreams. There are many people who assisted us in writing this book. We’d like to recognize each of them. To Kim Manzone, an employee of Database Creations, who helped write several of the chapters on forms and reports. To Diana Reid, also an employee of Database Creations, who had assisted in previous versions of this book (on programming and forms chapters) and was the technical editor for several of the previous versions of this book. To her we offer an additional and special thank you. Thanks to Jennifer Reardon, one of Cary’s top developers, for stepping in as always and for writing the Visual Basic for Application chapters. To John S. Dranchak for designing the reports so long ago, used in Chapters 19 and 21, and creating the logo for Mountain Animal Hospital. It still lives!!!!! To all the people who really made this book possible. To Andy Cummings at Hungry Minds, Inc., formerly IDG Books Worldwide, who pushes us beyond our limits. The word NO is not in his vocabulary! He challenges us daily with impossible tasks and deadlines (I don’t care if the software doesn’t work yet — write the book anyway,
  14. xiv Access 2002 Bible use your imagination – we must get this book out first). Cary thinks he wants us to get started on the Access 2004 Bible next month, although the software won’t be in Beta for another 18 months. Special acknowledgements go to our technical editors Janet Andrews and Pat O’Brien. Janet edited the first half of the book and found bugs, problems, mis- spellings, and more that have eluded us and her predecessors for years. She had some very insightful comments throughout the editing process and didn’t mind that she had to edit the entire book twice. She is a seasoned professional and we found her to be one of the best editors we have ever worked with in our now 20+-year career writing books. A special thank you to Pat O’Brien, and all the other unnamed editors, who were brought in at the last minute to edit the second half of the book — they had to do the same amount of work as Janet, but had less than half the time. What a terrific job and dedication — another truly seasoned professional. A special thank you must also go to Kyle Looper, Editorial Manager, also from Hungry Minds. When things started getting crazy with deadlines and trying to make Access work as expected with non-released product, he was the one that was there to bring calm to the trouble. He was more than just a coordinator between the authors, tech editors, and wordsmiths. He was the one person that kept everything on an even playing field and got everyone to work together cohesively. To the best literary agents in the business, Matt Wagner and Bill Gladstone, and all the folks at Waterside Productions for helping us negotiate our fairest advance ever and for keeping the screaming down at the flying pig company (Hungry Minds new logo). We would be remiss if do not thank several people at Microsoft, especially David Gainer, Microsoft Access Product Manager, Rich Dickinson, Microsoft Development Manager and Lucille Pan, the Access Product Manager. These folks were incredibly helpful in supplying us with beta builds and information not publicly available. They kept us informed about last minute changes and sent us new CD’s when our examples didn’t work. Thanks to Microsoft and these wonderful people, we were able to deliver a quality book to our readers. Michael R. Irwin and Cary N. Prague To the people of Database Creations who let me miss all my deadlines while I worked on this book. To Kim, Jerry, Larry, Diana, Julie, Phuc, Nate, Tom, Debbie, Karen, and especially Dick James for handling all my technical support calls. To Vic my golfing partner, thanks for dragging me to Las Vegas after I finished most of the book. Kaching! — CNP
  15. Acknowledgments xv First to all our clients who let me get away and spend four months solely on re-writing this book. Although my telephone bills seem to reflect the opposite — I am so grateful to them for not insisting that I personally appear to solve their ‘critical’ problems. To my family, Arni, Richard, Joseph, and David, who had to put up with the long months away from me while I dedicated myself to this book and organizing it into the best Access Bible yet. Even worse, having to be away, in a country (13,000 miles away), literally on the other side of the world, for three of the four months it took to solely focus on this book. I also want to thank Dave Gainer and Clint Covington of Microsoft who offered many suggestions for the Data Access Pages section of this book. Data Access Pages have been greatly enhanced and things were being added and cut from this section so rapidly it was impossible to figure out what was going to be in the final build. Without Dave and Clint’s help, these sections may have missed some items and put others in that were pulled. — MRI
  16. Contents at a Glance Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Part I: First Things First . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Chapter 1: What Is Access 2002? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Chapter 2: Installing and Running Access 2002 . . . . . . . . . . . . . . . . . . . . 23 Chapter 3: A Hands-on Tour of Access 2002 . . . . . . . . . . . . . . . . . . . . . . 55 Chapter 4: Database Concepts and Design . . . . . . . . . . . . . . . . . . . . . . 81 Part II: Creating the Objects . . . . . . . . . . . . . . . . . . . . . . . . 117 Chapter 5: Creating Database Tables . . . . . . . . . . . . . . . . . . . . . . . . . 119 Chapter 6: Setting Relationships Between Tables . . . . . . . . . . . . . . . . . . 165 Chapter 7: Working with Information in Tables . . . . . . . . . . . . . . . . . . . 211 Chapter 8: Creating and Using Simple Data-Entry Forms . . . . . . . . . . . . . . 249 Chapter 9: Understanding and Creating Simple Queries . . . . . . . . . . . . . . 269 Chapter 10: Creating and Working with Simple Reports . . . . . . . . . . . . . . 295 Part III: Using Access in Your Work . . . . . . . . . . . . . . . . . . . . 315 Chapter 11: Using Operators, Functions, and Expressions . . . . . . . . . . . . . 317 Chapter 12: Creating Relations and Joins in Queries . . . . . . . . . . . . . . . . 343 Chapter 13: Working with Select Queries . . . . . . . . . . . . . . . . . . . . . . . 369 Chapter 14: Understanding Form Controls . . . . . . . . . . . . . . . . . . . . . . 395 Chapter 15: Creating and Customizing Data-Entry Forms . . . . . . . . . . . . . 423 Chapter 16: Creating Great Looking Forms . . . . . . . . . . . . . . . . . . . . . . 471 Chapter 17: Adding Data-Validation Controls to Forms . . . . . . . . . . . . . . . 493 Chapter 18: Using OLE Objects, Graphs, Pivot Tables/Charts, and ActiveX Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519 Chapter 19: Creating and Customizing Reports . . . . . . . . . . . . . . . . . . . 571 Chapter 20: Presentation-Quality Reports and Printing . . . . . . . . . . . . . . 623 Chapter 21: Creating Calculations and Summaries in Reports . . . . . . . . . . . 655 Part IV: Advanced Access Database Topics . . . . . . . . . . . . . . . 689 Chapter 22: Working with External Data . . . . . . . . . . . . . . . . . . . . . . . 691 Chapter 23: Working with Advanced Select Queries . . . . . . . . . . . . . . . . 741 Chapter 24: Working with Action Queries . . . . . . . . . . . . . . . . . . . . . . 773
  17. Contents at a Glance xvii Chapter 25: Advanced Query Topics . . . . . . . . . . . . . . . . . . . . . . . . . 800 Chapter 26: Working with Subforms . . . . . . . . . . . . . . . . . . . . . . . . . . 833 Chapter 27: Creating Mail Merge and Label Reports . . . . . . . . . . . . . . . . 863 Part V: Access Application Tools . . . . . . . . . . . . . . . . . . . . . 891 Chapter 28: Working with Macros and Events . . . . . . . . . . . . . . . . . . . . 893 Chapter 29: Using Macros in Forms and Reports . . . . . . . . . . . . . . . . . . 917 Chapter 30: Working with Visual Basic in Access 2002 . . . . . . . . . . . . . . . 961 Chapter 31: Using Visual Basic in Forms and Reports . . . . . . . . . . . . . . . 993 Chapter 32: Creating Switchboards, Command Bars, Menus, Toolbars, and Dialog Boxes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1049 Part VI: Access and the External World . . . . . . . . . . . . . . . . . 1111 Chapter 33: Moving from Jet to the SQL Server 2000 Desktop Engine . . . . . . 1113 Chapter 34: Working with Access Projects . . . . . . . . . . . . . . . . . . . . . 1151 Chapter 35: Using and Creating Access Objects for Intranets and the Internet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1179 Chapter 36: Building Web Applications, Data Access Pages, and XML . . . . . 1217 Appendix A: Access 2002 Specifications . . . . . . . . . . . . . . . . . . . . . . . 1273 Appendix B: Mountain Animal Hospital Tables . . . . . . . . . . . . . . . . . . . 1279 Appendix C: What’s on the CD-ROM . . . . . . . . . . . . . . . . . . . . . . . . . 1287 Appendix D: Chapter Cross-Reference Guide . . . . . . . . . . . . . . . . . . . . 1297 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1305 Coupons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1343 Hungry Minds, Inc. End-User License Agreement. . . . . . . . . . . . . . . . . . 1348 Installation Instructions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1350
  18. Contents Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Part I: First Things First 1 Chapter 1: What Is Access 2002? . . . . . . . . . . . . . . . . . . . . . . 3 Access Is . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 What Access Offers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 True relational database management . . . . . . . . . . . . . . . . . . . 7 Context-sensitive Help and the Office Assistant . . . . . . . . . . . . . 8 Ease-of-use Wizards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Importing, exporting, and linking external table files . . . . . . . . . . 9 WYSIWYG forms and reports . . . . . . . . . . . . . . . . . . . . . . . 10 Multiple-table queries and relationships . . . . . . . . . . . . . . . . . 11 Business graphs and charts . . . . . . . . . . . . . . . . . . . . . . . . 13 DDE and OLE capabilities . . . . . . . . . . . . . . . . . . . . . . . . . . 14 The Internet is also accessible . . . . . . . . . . . . . . . . . . . . . . . 14 True Client/Server for Everyone . . . . . . . . . . . . . . . . . . . . . . . . . 15 Built-in functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Macros: Programming without programming . . . . . . . . . . . . . . 16 Modules: Visual Basic for Applications — database programming . . 16 Information for Database Users . . . . . . . . . . . . . . . . . . . . . . . . . 17 The Windows environment . . . . . . . . . . . . . . . . . . . . . . . . . 18 GUI environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Event-driven environment . . . . . . . . . . . . . . . . . . . . . . . . . 19 Programming by exception . . . . . . . . . . . . . . . . . . . . . . . . . 19 Information for Spreadsheet Users . . . . . . . . . . . . . . . . . . . . . . . 20 Database manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Chapter 2: Installing and Running Access 2002 . . . . . . . . . . . . . 23 Determining What You Need . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Hardware requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Software requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Upgrading to Access 2002 from Access 2.0, 95, 97, or 2000 . . . . . . . . . . 25 Installing Access 2002 or Office XP . . . . . . . . . . . . . . . . . . . . . . . . 25 Activating Your Product . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Activating by using the Internet . . . . . . . . . . . . . . . . . . . . . . 36 Activating by using the telephone . . . . . . . . . . . . . . . . . . . . . 38
Đồng bộ tài khoản