Alison Balter's Mastering Access 97 Development, Premier Edition, Second Edition

1159 Pages·1997·16.82 MB·English

by  Alison Balter

CONTENTS
What’s New in This Edition
This edition updates Alison Balter’s Mastering Access 95 Development, Premier Edition to in-
clude the new features available in Access 97 (Office Developer’s Edition).
New topics and examples address
• Making your Access 97 applications Internet-aware (the new HyperLink field type;
saving forms and reports as HTML; importing, linking to, and exporting HTML
tables; adding a hyperlink to a report; and addinghyperlinks to labels, images, and
command buttons)
• Integration between Access and Office 97
• Enhancements to the development environment, including right-click enhance-
ments such as Quick Info, Parameter Info, Complete Word, List Properties and
Methods, List Constants, Definition, and Last Position
• Language enhancements such as class modules
• Changes to the object model
• Changes surrounding menu bars and toolbars
• The addition of a built-in tab control
• Changes and enhancements to the Jet Engine
• ODBC Direct
• SourceSafe integration
• Changes to the Debugger
• Removing source code from databases
This edition also includes expanded coverage of
• Parameter queries
• Collections
• The generic error handler
Above all, we hope that Alison Balter’s Mastering Access 97 Development, Second Edition will
be your definitive guide for your Access 97 development efforts.
i
Untitled-2 1 07.08.2002, 11:09
P2/VA/swg2 AB Mastering Access 97 DG 30999-8 Lenser 12.5.96 FM LP2
ALISON BALTER'S MASTERING ACCESS 97 DEVELOPMENT
Alison Balter’s Mastering
Access 97 Development,
Second Edition
Alison Balter
800 East 96th Street
Indianapolis, Indiana 46240
ii
Untitled-2 2 07.08.2002, 11:09
P2/VA/swg2 AB Mastering Access 97 DG 30999-8 Lenser 12.5.96 FM LP2
CONTENTS
I dedicate this book to my husband, Dan; my daughter, Alexis; my parents, Charlotte and Bob; and
to my real father, Herman. Dan, you are my partner in life and the wind beneath my wings. Your
support of my endeavors is relentless. Alexis, you are the sweet little girl that I always dreamed for.
You are everything that I could have ever wanted and so very much more. Mom and Dad, without
all that you do to help out with life’s chores, the completion of this book would never have been pos-
sible.
To my real father, Herman, I credit my ability to soar in such a technical field to you. I hope that I
inherited just a small part of your intelligence, wit, and fortitude. I am sorry that you did not live to
see this accomplishment. I hope that you can see my work and that you are proud of it. More than
anyone else, I dedicate this book to you.
iii
Untitled-2 3 07.08.2002, 11:09
P2/VA/swg2 AB Mastering Access 97 DG 30999-8 Lenser 12.5.96 FM LP2

Copyright 1997 by Sams Publishing Acquisitions Editor
ALISON BALTER'S MASTERING ACCESS 97 DEVELOPMENT
Grace M. Buechlein
SECOND EDITION
Development Editor
All rights reserved. No part of this book shall be reproduced, stored in a Kristi Asher
retrieval system, or transmitted by any means, electronic, mechanical, Software Development Specialist
photocopying, recording, or otherwise, without written permission from the John Warriner
publisher. No patent liability is assumed with respect to the use of the
Production Editors
information contained herein. Although every precaution has been taken in
Fran Blauw
the preparation of this book, the publisher and author assume no responsibil-
Lisa M. Lord
ity for errors or omissions. Neither is any liability assumed for damages
resulting from the use of the information contained herein. For information, Indexer
address Sams Publishing, 201 W. 103rd St., Indianapolis, IN 46290. Tom Dinse
International Standard Book Number: 0-672-30999-8 Technical Reviewers
Karen Jaskolka
Library of Congress Catalog Card Number: 96-72230
John Nelson
2000 99 98 4 3 Paul Cassel
Nick Malik
Interpretation of the printing code: the leftmost number is the year of the Chris Rothberg
book’s printing; the rightmost single digit the number of the book’s printing. David Shank
For example, a printing code of 97-1 shows that the first printing of the book
Editorial Coordinator
occurred in 1997.
Katie Wise
Composed in AGaramond, Optima, and MCPdigital by Macmillan Computer
Technical Edit Coordinator
Publishing
Lorraine Schaffer
Printed in the United States of America
Resource Coordinator
Deborah Frisby
Trademarks
Editorial Assistants
All terms mentioned in this book that are known to be trademarks or service Carol Ackerman
marks have been appropriately capitalized. Sams Publishing cannot attest to Andi Richter
the accuracy of this information. Use of a term in this book should not be Rhonda Tinch-Mize
regarded as affecting the validity of any trademark or service mark. Access is a
Cover Designer
registered trademark of Microsoft Corporation.
Tim Amrhein
Book Designer
Publisher and President: Richard K. Swadley Alyssa Yesh
Publishing Manager: Rosemarie Graham Copy Writer
Director of Editorial Services: Cindy Morrow Peter Fuller
Assistant Marketing Managers: Kristina Perry, Rachel Wolfe Production Team Supervisors
Brad Chinn
Charlotte Clapp
Production
Rick Bond
Carol Bowers
Brad Lenser
Chris Livengood
Carl Pierce
iv
Untitled-2 4 07.08.2002, 11:09
P2/VA/swg2 AB Mastering Access 97 DG 30999-8 Lenser 12.5.96 FM LP2
Overview
CONTENTS
Introduction xlv
Part I Building a Foundation for Your Access Applications 1
1 Introduction to Access Development 3
2 A Strategy to Developing Access Applications 25
3 What Every Developer Needs to Know About Tables 39
4 Relationships: Your Key to Data Integrity 69
5 What Every Developer Needs to Know About Query Basics 85
6 What Every Developer Needs to Know About Form Basics 117
7 What Every Developer Needs to Know About Report Basics 165
8 VBA 101: The Basics of VBA 207
9 Advanced VBA Techniques 243
10 The Real Scoop on Objects, Properties, and Events 279
11 What Are Macros and When Do You Need Them? 305
12 Advanced Query Concepts 331
13 Let’s Get More Intimate with Forms: Advanced Techniques 373
14 Let’s Get More Intimate with Reports: Advanced Techniques 425
15 What Are Data Access Objects and Why Are They Important? 453
Part II What To Do When Things Don’t Go As Planned 491
16 Debugging: Your Key to Successful Development 493
17 Handling Those Dreaded Runtime Errors 515
Part III Preparing Your Applications for a Multiuser
Environment 539
18 Developing for a Multiuser Environment 541
19 Using External Data 567
20 Client/Server Techniques 599
21 Client/Server Strategies 629
22 Transaction Processing 639
23 Optimizing Your Application 655
24 Replication Made Easy 677
Part IV Extending the Power of Access 711
25 Automation: Communicating with Other Applications 713
26 Using ActiveX Controls 739
27 Access and the Internet 765
28 Managing Application Development with Visual SourceSafe 781
29 Leveraging Your Application: Creating Your Own Libraries 795
30 Using Builders, Wizards, and Menu Add-Ins 811
v
Untitled-2 5 07.08.2002, 11:09
P2/VA/swg2 AB Mastering Access 97 DG 30999-8 Lenser 12.5.96 FM LP2
ALISON BALTER'S MASTERING ACCESS 97 DEVELOPMENT
Part V Putting the Final Polish on Your Application 835
31 Using External Functions: The Windows API 837
32 Database Security Made Easy 859
33 Complex Security Issues 885
34 Documenting Your System 909
35 Database Maintenance Techniques 923
36 Developing a Help File 931
37 Distributing Your Application with ODE 957
Part VI Appendixes 991
A Table Structures 993
B Naming Conventions 1045
Index 1051
vi
Untitled-2 6 07.08.2002, 11:09
P2/VA/swg2 AB Mastering Access 97 DG 30999-8 Lenser 12.5.96 FM LP2
Contents
CONTENTS
Part I Building a Foundation for Your Access Applications 1
1 Introduction to Access Development 3
What Types of Applications Can You Develop in Access?........... 4
Access as a Development Platform for Personal Applications .. 4
Access as a Development Platform for Small-Business
Applications.......................................................................... 4
Access as a Development Platform for Departmental
Applications.......................................................................... 5
Access as a Development Platform for Corporation-Wide
Applications.......................................................................... 5
Access as a Development Platform for Enterprise-Wide
Client/Server Applications.................................................... 6
Access as a Scalable Product.................................................... 6
What Exactly Is a Database?........................................................ 7
Getting to Know the Database Objects....................................... 7
Tables: A Repository for Your Data........................................ 8
Queries: Stored Questions or Actions to be Applied to Your
Data ................................................................................... 10
Forms: A Means of Displaying, Modifying, and Adding
Data ................................................................................... 11
Reports: Turning Data Into Information.............................. 12
Macros: A Means of Automating Your System...................... 13
Modules: The Foundation to the Application Development
Process................................................................................ 14
Object Naming Conventions .................................................... 15
Hardware Requirements............................................................ 16
What Hardware Does Your System Require?........................ 16
What Hardware Does the User’s System Require? ................ 17
How Do I Get Started Developing an Access Application?........ 17
Task Analysis........................................................................ 17
Data Analysis and Design ..................................................... 17
Prototyping........................................................................... 20
Testing ................................................................................. 20
Implementation.................................................................... 21
Maintenance......................................................................... 21
A Practical Example of Application Design: A Computer
Consulting Firm................................................................. 21
Summary................................................................................... 23
vii
Untitled-2 7 07.08.2002, 11:09
P2/VA/swg2 AB Mastering Access 97 DG 30999-8 Lenser 12.5.96 FM LP2
ALISON BALTER'S MASTERING ACCESS 97 DEVELOPMENT
2 A Strategy for Developing Access Applications 25
Why Strategize?......................................................................... 26
Splitting Tables and Other Objects........................................... 26
Basing Forms and Reports on Queries....................................... 28
Understanding the Access Runtime Engine............................... 28
Features of the ODE............................................................. 29
Differences Between the Standard and Runtime Versions of
Access................................................................................. 30
Preparing an Application for Distribution............................. 31
RESOURCE............................................................................. 34
The Access Runtime Engine: Summing It Up....................... 34
EXE Versus Access Database: What It Means to You................ 34
The Importance of Securing Your Database.............................. 34
Using Access as a Front-End ..................................................... 35
Things You Need to Worry About in Converting to
Client/Server ...................................................................... 36
Benefits and Costs of Client/Server Technology ................... 36
Your Options When Using Access as a Front-End................ 37
What All This Means to You Right Now.............................. 38
Applying the Strategy to the Computer Consulting Firm
Application ............................................................................. 38
Summary................................................................................... 38
3 What Every Developer Needs to Know About Tables 39
Building a New Table ............................................................... 40
Building a Table with a Wizard ............................................ 41
Designing a Table from Scratch............................................ 43
Building a Table from a Datasheet........................................ 44
Selecting the Appropriate Field Type for Your Data.................. 46
Text Fields: The Most Common Field Type......................... 49
Memo Fields: For Those Long Notes and Comments........... 49
Number Fields: When You Need to Calculate...................... 49
Date/Time Fields: Tracking When Things Happened.......... 49
Currency Fields: Storing Money........................................... 50
AutoNumber Fields: For Unique Record Identifiers............. 50
Yes/No Fields: When One of Two Answers Is Correct.......... 50
OLE Object Fields: The Place to Store Just About
Anything ............................................................................ 50
Hyperlink Fields: Your Link to the Internet.......................... 51
Working with Field Properties .................................................. 51
Field Size: Limiting What’s Entered into a Field................... 51
viii
Untitled-2 8 07.08.2002, 11:09
P2/VA/swg2 AB Mastering Access 97 DG 30999-8 Lenser 12.5.96 FM LP2
CONTENTS
TRY IT..................................................................................... 51
Format: Determining How Data Is Displayed...................... 52
TRY IT..................................................................................... 52
Input Mask: Determining What Data Goes into a Field....... 52
TRY IT..................................................................................... 53
Caption: A Great Timesaver................................................. 54
Default Value: Saving Data-Entry Time ............................... 54
TRY IT..................................................................................... 54
Validation Rule: Controlling What’s Entered in a Field........ 55
TRY IT..................................................................................... 55
Validation Text: Providing Error Messages to the User......... 56
TRY IT..................................................................................... 56
Required: Make the User Enter a Value .................................... 57
TRY IT..................................................................................... 57
Allow Zero Length: Accommodate Situations with
Nonexistent Data ............................................................... 57
TRY IT..................................................................................... 57
Indexed: Speeding Up Searches................................................. 58
TRY IT..................................................................................... 58
The All-Important Primary Key................................................ 59
Working with the Lookup Feature............................................ 59
Working with Table Properties................................................. 61
Using Indexes to Improve Performance..................................... 62
Access Tables and the Internet .................................................. 62
The Hyperlink Field Type.................................................... 62
Saving Table Data as HTML................................................ 63
Practical Examples: Designing the Tables Needed for the
Computer Consulting Firm’s Time and Billing Application.... 65
Summary................................................................................... 68
4 Relationships: Your Key to Data Integrity 69
Understanding Relationships..................................................... 70
Examining the Types of Relationships....................................... 70
One-to-Many ....................................................................... 70
One-to-One.......................................................................... 71
Many-to-Many..................................................................... 72
Establishing Relationships......................................................... 72
Establishing a Relationship Between Two Tables.................. 73
Looking At Guidelines for Establishing Relationships........... 73
TRY IT..................................................................................... 74
Modifying an Existing Relationship...................................... 75
ix
Untitled-2 9 07.08.2002, 11:09
P2/VA/swg2 AB Mastering Access 97 DG 30999-8 Lenser 12.5.96 FM LP2