Microsoft Office Access 2003: professional results

401 Pages·2003·8.218 MB·English

by  Jerke, Noel

Color profile: Generic CMYK printer profile Win&Mac/ Microsoft Office Access 2003 Professional Results / Jerke / 222965-9 / Front Matter
Composite Default screen blind folio1
Professional Results
Microsoft Office
®
Access 2003
P:\010Comp\Win&Mac\965-9\fm.vp
Thursday, August 07, 2003 10:56:47 AM
This page intentionally left blank
Color profile: Generic CMYK printer profile Win&Mac/ Microsoft Office Access 2003 Professional Results / Jerke / 222965-9 / Front Matter
Composite Default screen blind folioiii
Professional Results
Microsoft Office
®
Access 2003
McGraw-Hill/Osborne
Noel Jerke New York Chicago San Francisco
Lisbon London Madrid Mexico City
Milan New Delhi San Juan
Seoul Singapore Sydney Toronto
P:\010Comp\Win&Mac\965-9\fm.vp
Thursday, August 07, 2003 10:56:48 AM
Color profile: Generic CMYK printer profile Win&Mac/ Microsoft Office Access 2003 Professional Results / Jerke / 222965-9 / Front Matter
Composite Default screen Blind FolioFM:iv
McGraw-Hill/Osborne
2100 Powell Street, 10thFloor
Emeryville, California 94608
U.S.A.
To arrange bulk purchase discounts for sales promotions, premiums, or fund-raisers, please
contactMcGraw-Hill/Osborne at the above address. For information on translations or
book distributors outside the U.S.A., please see the International Contact Information page
immediately following the index of this book.
Microsoft® Office Access 2003 Professional Results
Copyright © 2003 by The McGraw-Hill Companies. All rights reserved. Printed in the
United States of America. Except as permitted under the Copyright Act of 1976, no part of
this publication may be reproduced or distributed in any form or by any means, or stored in
a database or retrieval system, without the prior written permission of publisher, with the
exception that the program listings may be entered, stored, and executed in a computer
system, but they may not be reproduced for publication.
1234567890 FGR FGR 019876543
ISBN 0-07-222965-9
Publisher Brandon A. Nordin
Vice President &
Associate Publisher Scott Rogers
Acquisitions Editor Nancy Maragioglio
Project Editor Mark Karmendy
Acquisitions Coordinator Athena Honore
Technical Editor Greg Guntle
Indexer Claire Splan
Composition Carie Abrew, Tara A. Davis
Illustrators Kathleen Fay Edwards, Melinda Moore Lytle, Lyssa Wald
Series Design Mickey Galicia
Cover Design Jeff Weeks
This book was composed with Corel VENTURA™ Publisher.
Information has been obtained byMcGraw-Hill/Osborne from sources believed to be reliable. However, because of the
possibility of human or mechanical error by our sources,McGraw-Hill/Osborne, or others,McGraw-Hill/Osborne does not
guarantee the accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the
results obtained from the use of such information.
P:\010Comp\Win&Mac\965-9\fm.vp
Thursday, August 07, 2003 10:56:48 AM
Color profile: Generic CMYK printer profile Win&Mac/ Microsoft Office Access 2003 Professional Results / Jerke / 222965-9 / Index
Composite Default screen blind foliov
This book is dedicated to all our new friends we have made
since we have moved to Dallas.
You all have become very dear to us—God Bless!
P:\010Comp\Win&Mac\965-9\fm.vp
Thursday, August 07, 2003 10:56:48 AM
Color profile: Generic CMYK printer profile Win&Mac/ Microsoft Office Access 2003 Professional Results / Jerke / 222965-9 / Front Matter
Composite Default screen Blind FolioFM:vi
About the Author
Noel Jerkeis an independent consultant living in the Dallas–Fort Worth area.
He is a 12-year veteran of the technology arena. Past clients include the Air Force,
Mary Kay, the American Diabetes Association, and Martha Stewart. Noel’s
particular area of expertise is in technology operations management and building
enterprise-level database applications. Noel is married to a wonderful wife and has
three very special children, and can be reached at [email protected].
P:\010Comp\Win&Mac\965-9\fm.vp
Thursday, August 07, 2003 10:56:48 AM
Color profile: Generic CMYK printer profile Win&Mac/ Microsoft Office Access 2003 Professional Results / Jerke / 222965-9 /Front Matter
Composite Default screen
Contents at a Glance
1 Introducing Office 2003 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2 Designing and Prototyping Enterprise Databases
with Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3 Building Advanced Queries with the Query Wizard . . . . . . . . . . . . . 43
4 Building Advanced Queries with the Query Design View . . . . . . . . 69
5 Programming Microsoft Access with Web Matrix . . . . . . . . . . . . . . 89
6 Programming Microsoft Access with .NET . . . . . . . . . . . . . . . . . . . 123
7 Importing and Exporting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
8 Working with Access XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
9 Introducing Access Data Projects . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
10 Building an Access Data Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
11 Managing Access Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
12 Visual Basic for Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
A Access 2003 XML Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
B PivotTable and PivotChart Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
C Data Access Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
vii
P:\010Comp\Win&Mac\965-9\fm.vp
Thursday, August 07, 2003 10:56:48 AM
This page intentionally left blank
Color profile: Generic CMYK printer profile Win&Mac/ Microsoft Office Access 2003 Professional Results / Jerke / 222965-9 /Front Matter
Composite Default screen
Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
CHAPTER 1 Introducing Office 2003 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Office 2003 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Access 2003 Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Smart Tags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Miscellaneous Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
CHAPTER 2 Designing and Prototyping Enterprise Databases
with Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Database Design Refresher . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Database Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Data Types in Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Querying and Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Prototyping for Enterprise Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Easy Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
SQL Server Upsizing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Rapid Interface Prototyping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Sample Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Designing the Data Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Implementing a Database Design in Access . . . . . . . . . . . . . . . . . 25
Upsizing to SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
CHAPTER 3 Building Advanced Queries with the Query Wizard . . . . . . . . . . . . . 43
The Access Query Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Reviewing the Data Sample . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Introducing the Simple Query Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Quickly Create Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Building Complex Queries and PivotTable Views . . . . . . . . . . . . . . . . . . 50
The Query Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
The PivotTable View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
ix
P:\010Comp\Win&Mac\965-9\fm.vp
Thursday, August 07, 2003 10:56:48 AM