Tài liệu Beginning Access 2003 VBA pptx

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

Beginning Access 2003 VBA

i

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

ii

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

Beginning Access 2003 VBA

Denise Gosnell

Wiley Publishing, Inc.

iii

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

Beginning Access 2003 VBA

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright
c 2004 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,

electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108

of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization

through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA

01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal

Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355,

E-Mail: [email protected].

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRE￾SENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS

OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WAR￾RANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY

SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE

SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER

IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFES￾SIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE

SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HERE￾FROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION

AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE

PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOM￾MENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED

IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND

WHEN IT IS READ.

For general information on our other products and services or to obtain technical support, please contact our Customer

Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available

in electronic books.

Library of Congress Cataloging-in-Publication Data

Gosnell, Denise.

Beginning Access 2003 VBA / Denise Gosnell.

p. cm.

Includes index.

ISBN 0-7645-5659-2

1. Microsoft Access. 2. Database management. 3. Microsoft

Visual Basic for applications. I. Title.

QA76.9.D3G68 2004

005.75

65—dc22

2004015762

Trademarks: Wiley, the Wiley Publishing logo, Wrox, the Wrox logo, Programmer to Programmer and related trade

dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and

other countries, and may not be used without written permission. All other trademarks are the property of their

respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

iv

eISBN: 0-7645-79029

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

About the Author

Denise Gosnell is a software attorney with Woodard, Emhardt, Moriarty, McNett & Henry LLP, a

worldwide intellectual property law firm based in Indianapolis, Indiana. Denise has a unique

background in both technology and law, and presently uses her deep technical and legal expertise to

counsel hi-tech clients on intellectual property and technical matters.

Denise has ten years of experience creating software applications, ranging from standalone and

client-server to enterprise-wide applications. Denise has worked for leading software companies such as

Microsoft and EDS, and has earned a worldwide reputation for her technology expertise. She received a

Bachelor of Arts degree in Computer Science – Business (summa cum laude) from Anderson University,

and a Doctor of Jurisprudence degree from Indiana University School of Law in Indianapolis.

Denise has co-authored five software development books to date: Visual Basic .NET and SQL Server 2000:

Building An Effective Data Layer (Wrox Press), Beginning Visual Basic.NET Databases (Wrox Press),

Professional .NET Framework (Wrox Press), Professional SQL Server 2000 (Wrox Press), and MSDE Bible (IDG

Books). Denise was a featured technology speaker at the Microsoft European Professional Developer’s

Conference in December 2001 and has on numerous occasions assisted Microsoft’s Training and

Certification group in creating new exams for their MCSD and MCSE certifications. She herself holds the

MCSD certification.

Denise can be reached at [email protected] or [email protected].

v

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

Credits

Executive Editor

Chris Webb

Editorial Manager

Kathryn A. Malm

Senior Development Editor

Emilie Herman

Production Editor

Gabrielle Nabi

Technical Editor

Wiley-Dreamtech India Pvt Ltd

Copy Editor

Mary Lagu

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Bob Ipsen

Vice President and Publisher

Joseph B. Wikert

Executive Editorial Director

Mary Bednarek

Proofreading and Indexing

TechBooks Production Services

vi

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

This book is dedicated to Professor Chuck Koontz and the Computer Science department at Anderson

University. Thanks for introducing me to Visual Basic over ten years ago and for your support and

encouragement! Although time and distance separates us, the assistance you provided me at the

beginning of my career is always remembered and appreciated.

I would like to offer a special thanks to Emilie Herman and Chris Webb and the other staff at Wiley who

played such a key role in making this book a reality. I could not have done this without you!

vii

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

viii

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

Contents

Introduction xv

Chapter 1: Introduction to Access 2003 VBA 1

What Is Access 2003 VBA? 1

What’s New in Access 2003 VBA? 2

Access 2003 VBA Programming 101 2

Requirements Gathering Phase 2

Design Phase 3

Development Phase 17

Testing Phase 22

Implementation Phase 23

Summary 24

Exercises 25

Chapter 2: The Basics of Writing and Testing VBA Code 27

Creating Modules 27

Standard Modules versus Class Modules 29

Creating and Calling Procedures 33

Sub versus Function Procedure 33

Upgrading Macros to Procedures 46

Using Variables and Constants to Store Values 47

Types of Variables 47

Declaring and Using Variables 49

Constants 52

Scope and Lifetime of Variables and Constants 52

Naming Conventions 53

Controlling Program Flow 54

Decision Making 54

Loops 59

Documenting Your Code 62

Error Debugging 62

Types of Errors 63

Debugging Your Code 65

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

Contents

Error Handling 69

Default Error Messages 70

Handling Errors with an On Error Statement 70

The Err Object 72

Using the Errors Collection 73

Creating a Generic Error Handler 73

Summary 76

Exercises 76

Chapter 3: Programming Applications Using Objects 77

Elements of an Object 77

What Is a Property? 78

What Is a Method? 80

What Is an Event? 81

Viewing Objects Using the Object Browser 89

Ways to Refer to Objects 90

Using Access Collections and Objects 91

The Forms Collection and Form Object 92

The Reports Collection and Report Object 94

The CurrentProject Object 94

The DoCmd Object 95

The Screen Object 95

The Printers Collection and Printer Object 96

Other Objects 97

Summary 98

Exercises 98

Chapter 4: Creating Your Own Objects 99

Using Class Modules to Create Your Own Objects 99

Class Diagrams 100

Creating Properties 103

Creating Methods 109

Creating Events 111

Using the Class 112

Advanced Techniques 118

Create Custom Properties for Existing Objects 121

Using Existing Tag Property 121

Using Public Variables 121

Using Property Let, Property Get, and Property Set Routines 121

Create Custom Methods for Existing Objects 122

Using Public Sub Procedures and Functions 122

x

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

Contents

Summary 123

Exercises 124

Chapter 5: Interacting with Data Using ADO and SQL 125

Introduction to Data Access 125

Using ADODB to Work with Data 126

The ADO Object Model 126

The Connection Object 126

The Command Object 128

The Recordset Object 128

Introduction to SQL 155

Retrieving Data Using SQL Select Statements 155

Inserting Data Using SQL Insert Statements 160

Updating Data Using SQL Statements 160

Deleting Data Using SQL Statements 161

Using ADOX to Manipulate Data Objects 172

Creating a Table with ADOX 173

Managing Security with ADOX 174

Summary 176

Exercises 176

Chapter 6: Building Interactive Forms 177

Form Navigation and Flow 177

Working with Controls 183

Combo Boxes versus List Boxes 184

Tab Controls and Subforms 188

Building User Interaction 189

The MsgBox Function 190

Validating User Input 190

Adding Polish to Your Forms 191

Summary 192

Exercises 192

Chapter 7: Importing, Linking, and Exporting Using External Data Sources 193

Linking, Importing, and Exporting Overview 193

Access and Other Databases 194

Transferring Complete SQL Server Database 197

Spreadsheets 197

Text Files 198

XML Files 201

xi

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

Contents

E-mails and Outlook 202

Other Ways to Export Data 204

Using Data from Web Services 204

Summary 210

Exercises 210

Chapter 8: Creating Reports and Web-Enabled Output 211

Working with Reports from VBA 211

Setting the Report Data Source 211

Creating Reports Programmatically 213

Exporting Access Data to Web Formats 223

Creating Static HTML Pages 223

Creating ASP Pages 225

Creating Data Access Pages 227

Summary 232

Exercises 232

Chapter 9: Building SQL Server Applications with Access Projects 233

Access Projects—Using Access as a Front End to SQL server 233

Setting Up SQL Server 234

SQL Server Versions 235

Obtaining and Installing SQL Server Desktop Engine 236

Creating Access Projects (ADPs) 239

Using an Existing SQL Server Database 239

Using a New SQL Server Database 240

Working with SQL Server Objects from Access 243

SQL Server Tables 243

SQL Server Stored Procedures 246

SQL Server Views 248

SQL Server Functions 252

Migrating an Existing Access Database to an Access Project 254

Changes to an Existing Database 254

Using the Upsizing Wizard 255

Summary 261

Exercises 264

Chapter 10: Advanced Access Programming 265

Using Dynamic Link Libraries (DLLs) 265

Standard DLLs 265

Using External DLLs 269

xii

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

Contents

Automation 272

Working with Automation Objects 272

Controlling Microsoft Excel 273

Controlling Microsoft Word 276

Controlling Microsoft Outlook 279

ActiveX Controls 282

Libraries 285

Add-ins 289

Transactions 289

Summary 290

Exercises 291

Chapter 11: Finishing the Application 293

Multi-User Considerations 293

Record Locking and Update Conflicts 294

Multi-User Architecture Considerations 296

Optimizing Your Applications 299

Improving Actual Performance 300

Improving Perceived Performance 302

Running the Performance Analyzer 303

Securing Your Application 305

Adding a Database Password 305

Adding a Password for VBA Code 306

Adding Users, Groups, and Permissions 306

Encrypting a Database 307

Distributing Your Application 308

Distributing the Application to Users with the Full Version of Access 308

Distributing the Application with the Access Runtime 308

Distributing an Execute-Only Application 309

Maintaining the Application 309

Compacting and Repairing the Database 309

Making Backup Copies of the Database 309

Summary 310

Exercises 310

Chapter 12: Case Study 1: Project Tracker Application 311

Design Specifications 311

Building the Database 317

Building the User Interface 320

Building the Class Modules for the Objects 330

xiii

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY044-FM WY044-Gosnell WY044-Gosnell-v2.cls August 13, 2004 15:34

Contents

The Project Class 331

The Contact Class 345

Building the Standard Modules 356

Connecting the User Interface to the Code 382

The Projects Form 382

The Contacts Form 411

Touring the Finished Project Tracker Application 422

Summary 428

Chapter 13: Case Study 2: Customer Service Application 429

Design Specifications 429

Building the Database and Database Objects 432

Building the User Interface 443

Building the Class Module for the Objects 448

Building the Standard Modules 455

Connecting the User Interface to the Code 467

The Customer Search Form 468

The View/Manage Customer Accounts Form 473

Touring the Finished Customer Service Application 481

Summary 487

Appendix A: Exercise Answers 489

Index 495

xiv