Sams Teach Yourself Microsoft Access 2002 Programming in 24 Hours (Sams Teach Yourself)

574 Pages·2001·11.43 MB·English

by  Paul Kimmel

00 0983 FM 7/23/01 10:40 AM Page i
Paul Kimmel
Teach Yourself
Microsoft Access
2002 Programming
24
in Hours
201 West 103rd St.,Indianapolis,Indiana,46290 USA
00 0983 FM 7/23/01 12:47 PM Page ii
Sams Teach Yourself Microsoft Access EXECUTIVEEDITOR
Rosemarie Graham
2002 Programming in 24 Hours
ACQUISITIONSEDITOR
Copyright © 2002 by Sams Publishing Angela Kozlowski
All rights reserved. No part of this book shall be reproduced,stored in a DEVELOPMENTEDITOR
retrieval system,or transmitted by any means,electronic,mechanical,photo- Kevin Howard
copying,recording,or otherwise,without written permission from the pub- Jason Merrill
lisher. No patent liability is assumed with respect to the use of the information
MANAGINGEDITOR
contained herein. Although every precaution has been taken in the preparation
Charlotte Clapp
of this book,the publisher and author assume no responsibility for errors or
omissions. Nor is any liability assumed for damages resulting from the use of PROJECTEDITOR
the information contained herein. Leah Kirkpatrick
International Standard Book Number:0-672-32098-3 COPYEDITOR
Bart Reed
Library of Congress Catalog Card Number:00-109719
INDEXER
Printed in the United States of America
Erika Millen
First Printing:July 2001
PROOFREADER
04 03 02 01 4 3 2 1 Juli Cook
Trademarks TECHNICALEDITOR
Jason Merrill
All terms mentioned in this book that are known to be trademarks or service
marks have been appropriately capitalized. Name of Imprint cannot attest to TEAMCOORDINATOR
the accuracy of this information. Use of a term in this book should not be Lynne Williams
regarded as affecting the validity of any trademark or service mark. INTERIORDESIGNER
Gary Adair
Warning and Disclaimer
COVERDESIGNER
Every effort has been made to make this book as complete and as accurate as
Aren Howell
possible,but no warranty or fitness is implied. The information provided is on
an “as is”basis. The author and the publisher shall have neither liability nor PAGELAYOUT
responsibility to any person or entity with respect to any loss or damages aris- Stacey Richwine-DeRome
ing from the information contained in this book.
00 0983 FM 7/23/01 10:40 AM Page iii
Contents at a Glance
Introduction 1
PART I Access Programming 101 3
Hour 1 What’s New in Access 2002 5
2 Exploring VBA 21
3 Storing Your Program’s Data 35
4 Controlling Program Flow and Performing Calculations 55
PART II Writing Code to Manage Your Access Data 73
Hour 5 Learning to Write Conditional Code 75
6 Managing Your Database 95
7 Using Advanced Data Types to Manage Data 113
PART III Using Access Resources to Get the Job Done 131
Hour 8 Solving Problems a Piece at a Time 133
9 Using Macros as Learning Aids 153
10 Employing Access Predefined Solutions 175
PART IV Defining Data Types and Using Arrays and Collections 195
Hour 11 Making the Complex Simple:Creating Your Own Data Types 197
12 Managing Varying Amounts of Data 207
13 Storing Information in a Collection 229
PART V Database Programming in Access 241
Hour 14 Managing Your Code 243
15 Data Programming Made Easy with ADODB 257
16 Using Advanced SQL Techniques 283
PART VI Mastering Error Handling 311
Hour 17 Removing Bugs 313
18 Adding Code to Handle Errors 331
00 0983 FM 7/23/01 10:40 AM Page iv
PART VII Creating Access User Interfaces 347
Hour 19 Creating Custom Forms and Reports 349
20 Adding Data to Web Pages 379
PART VIII Object-Oriented Programming in Access 405
Hour 21 Class Programming Basics 407
22 Adding Capabilities to Your Data Types 429
23 Writing Access Add-Ins 445
24 Managing Outlook Contact Information 461
PART IX Appendix 477
A Answers 479
Index 507
00 0983 FM 7/23/01 10:40 AM Page v
Contents
Introduction 1
Part I Access Programming 101 3
Hour 1 What’s New in Access 2002 5
New Side Panes for Easier Navigation....................................................................6
Customizing Menus and Toolbars ..........................................................................6
Using the More Menu Item................................................................................7
Personalizing Menus and Toolbars....................................................................7
Assigning Hyperlinks to Menus and Toolbars ..................................................9
Spell-Checking Data................................................................................................9
Editing Options......................................................................................................11
Multiple Undos and Redos ..............................................................................11
Clippit Office Assistant....................................................................................11
Ask a Question ................................................................................................11
Expanded Clipboard Functionality..................................................................12
Speech Command and Control..............................................................................12
Collaborating Online..............................................................................................12
Setting Up NetMeeting....................................................................................13
So You’re Connected. Now What? ..................................................................13
Network Places......................................................................................................14
Remove Author Information..................................................................................15
Introducing ActiveX Data Objects........................................................................15
Introducing a New Object for Database Security—ADOX ............................16
Support for Stored Procedures ........................................................................16
Programming Objects............................................................................................16
Adding Data to Web Pages....................................................................................17
Access Projects......................................................................................................17
Summary................................................................................................................18
Q&A......................................................................................................................18
Workshop ..............................................................................................................19
Quiz..................................................................................................................19
Exercises ..........................................................................................................19
Hour 2 Exploring VBA 21
Understanding How Access VBA Works..............................................................22
Learning Access VBA Keywords..........................................................................22
Access Operators and Operands............................................................................24
Data:What a Program Knows ..............................................................................26
00 0983 FM 7/23/01 10:40 AM Page vi
vi Sams Teach Yourself Microsoft Access 2002 Programming in 24 Hours
Putting It All Together ..........................................................................................28
Arithmetic Operators........................................................................................28
Comparison Operators......................................................................................29
Logical Operators ............................................................................................30
String Concatenation Operators ......................................................................31
Prelude to Advanced Topics..................................................................................32
Summary................................................................................................................33
Q&A......................................................................................................................33
Workshop ..............................................................................................................34
Quiz..................................................................................................................34
Exercises ..........................................................................................................34
Hour 3 Storing Your Program’s Data 35
How Memory Is Used............................................................................................36
Declaring Variables................................................................................................37
DimVariables....................................................................................................37
ReDimVariables................................................................................................38
ConstVariables................................................................................................38
GlobalVariables..............................................................................................39
Data Assignment and Evaluation..........................................................................40
Be Explicit ............................................................................................................41
VariantData Types ........................................................................................41
Implicit Variable Declaration............................................................................42
Using Pop-up Hints................................................................................................43
Setting Up Test Code........................................................................................43
Debugging and Using Pop-up Hints................................................................45
Using the Locals Window......................................................................................46
Using the Watches Window ..................................................................................46
Adding a Watch Item........................................................................................47
Editing Watches................................................................................................48
Using Quick Watches ......................................................................................49
Running Code in the Immediate Window ............................................................49
Employing the Call Stack......................................................................................51
Summary................................................................................................................52
Q&A......................................................................................................................52
Workshop ..............................................................................................................52
Quiz..................................................................................................................53
Exercises ..........................................................................................................53
Hour 4 Controlling Program Flow and Performing Calculations 55
What Goes into an Equation..................................................................................56
Equation Data ..................................................................................................56
Rules of the Road ............................................................................................59
00 0983 FM 7/23/01 10:40 AM Page vii
Contents vii
Understanding Operator Count..............................................................................60
Using Arithmetic Operators ..................................................................................60
Performing Comparisons ......................................................................................61
The Truth of Logical Operators ............................................................................63
And....................................................................................................................65
Eqv....................................................................................................................65
Imp....................................................................................................................66
Or......................................................................................................................66
Xor....................................................................................................................66
Not....................................................................................................................66
Bitwise Operations ..........................................................................................67
Concatenate It........................................................................................................68
Who Has Precedence Here?..................................................................................69
Special Operators ..................................................................................................70
Using the IsOperator......................................................................................70
Pattern Matching with Like ............................................................................70
AddressOfOperations ....................................................................................71
Summary................................................................................................................71
Q&A......................................................................................................................72
Workshop ..............................................................................................................72
Quiz..................................................................................................................72
Exercises ..........................................................................................................72
Part II Writing Code to Manage Your Access Data 73
Hour 5 Learning to Write Conditional Code 75
A Programmer’s Traffic Cop ................................................................................76
UsingElse ......................................................................................................78
Nesting Conditional Statements ......................................................................79
Housekeeping ..................................................................................................80
Tidying Up Nested Conditional Statements..........................................................80
Want to Go for a Spin?..........................................................................................82
Take It Once Around the Block ............................................................................84
Iterating Through Data..........................................................................................86
Iterating Forward and Backward......................................................................88
UboundandLbound..........................................................................................88
Iterating Collections of Data..................................................................................89
Short-circuiting Code............................................................................................90
Using the SwitchFunction....................................................................................91
Summary................................................................................................................92
Q&A......................................................................................................................92
00 0983 FM 7/23/01 10:40 AM Page viii
viii Sams Teach Yourself Microsoft Access 2002 Programming in 24 Hours
Workshop ..............................................................................................................93
Quiz..................................................................................................................93
Exercises ..........................................................................................................93
Hour 6 Managing Your Database 95
Access 2002 Is Backward Compatible with Access 2000....................................96
New File Format Provides Better Performance ..............................................96
Improved Compact and Repair Feature ..........................................................97
Creating a Database ..............................................................................................97
Defining the Database......................................................................................97
Creating a Table with Access 2002..................................................................98
Creating a Module in the Contacts Database ................................................101
Creating a Table with Code............................................................................101
Managing a Table with Code ..............................................................................104
Database Terms Refresher Course ................................................................104
Accessing Table Information..........................................................................105
Iteratively Inputting User Data............................................................................108
Using Conditional Code to Find Data ................................................................109
Summary..............................................................................................................111
Q&A....................................................................................................................111
Workshop ............................................................................................................112
Quiz................................................................................................................112
Exercises ........................................................................................................112
Hour 7 Using Advanced Data Types to Manage Data 113
Understanding OLE Automation ........................................................................115
Welcome to the World of Objects..................................................................115
Useful Objects................................................................................................116
Comparing ADO to DAO ..............................................................................116
Using the ADODB Object ..................................................................................118
Using a Connection........................................................................................119
Using a Recordset..........................................................................................121
Using the ADOX Object......................................................................................124
Catalog..........................................................................................................124
TheActiveConnectionAttribute..................................................................126
TablesCollection..........................................................................................126
Groups,Users,and Views ............................................................................126
Summary..............................................................................................................127
Q&A....................................................................................................................128
Workshop ............................................................................................................128
Quiz................................................................................................................128
Exercises ........................................................................................................129
00 0983 FM 7/23/01 10:40 AM Page ix
Contents ix
Part III Using Access Resources to Get the Job Done 131
Hour 8 Solving Problems a Piece at a Time 133
The Mechanics of Writing Subroutines..............................................................134
Writing the First and Last Lines of Subroutines............................................135
Naming Subroutines ......................................................................................136
Naming Subroutine Arguments......................................................................136
Defining Argument Types..............................................................................136
The Mechanics of Writing Functions..................................................................140
Programming Rules of Thumb............................................................................141
Rule 1:Make Recurring Code into a Function..............................................142
Rule 2:Keep Functions Short........................................................................142
Rule 3:Strictly Limit the Number of Arguments..........................................142
Rule 4:Use Argument Qualifiers to Inhibit
Variable Misuse............................................................................................142
Rule 5:Use Contract Programming ..............................................................143
Rule 6:Don’t Be Afraid to Comment............................................................143
Tying It All Together............................................................................................144
Creating a Table with Code............................................................................144
Importing a Comma-Delimited Text File ......................................................145
Finding a Record............................................................................................147
Using the Windows Registry..........................................................................148
Summary..............................................................................................................150
Q&A....................................................................................................................150
Workshop ............................................................................................................151
Quiz................................................................................................................151
Exercises ........................................................................................................151
Hour 9 Using Macros As Learning Aids 153
Creating a Macro 101..........................................................................................154
Creating a Table with SQL ............................................................................154
Creating a Macro............................................................................................155
Defining Macro Names and Conditional Code..............................................157
Testing and Debugging a Macro....................................................................159
UsingCopyObject..............................................................................................161
UsingDeleteObject..........................................................................................162
The Keys to the City............................................................................................164
Importing Data ....................................................................................................165
Transferring a Database..................................................................................165
Transferring Text Data....................................................................................166
Using Macro Resources in Code ........................................................................169
To Macro or Not to Macro..................................................................................170