ebook img

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

574 Pages·2001·11.43 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

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

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

Description:
Sams Teach Yourself Access 2002 Programming in 24 Hours will considerably improve the quality of the database applications that the reader can create with Microsoft Access. Concise tutorials that quickly bring the reader up to speed will be the goal of each chapter. Having completed this book, the r
See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.