Access 2003 VBA programmer's reference

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

i

Access 2003 VBA

Programmer’s Reference

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

ii

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

iii

Access 2003 VBA

Programmer’s Reference

Patricia Cardoza

Teresa Hennig

Graham Seach

Armen Stein

Wiley Publishing, Inc.

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

iv

Access 2003 VBA Programmer’s Reference

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright
c 2004 by Patricia Cardoza, Teresa Hennig, Armen Stein, Graham Seach. All rights reserved.

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

eISBN: 0-7645-7166-4

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 Section 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, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400,

fax (978) 646-8700. 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-4447, E-mail: [email protected].

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR

MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR

COMPLETENESS OF THE CONTENTS OF THISWORK AND SPECIFICALLY DISCLAIM ALLWAR￾RANTIES,INCLUDINGWITHOUT LIMITATION,WARRANTIES 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 PUB￾LISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL

SERVICES. IF PROFESSIONAL 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 HEREFROM. THE FACT THAT AN ORGANIZA￾TION 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 PUB￾LISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR

RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTER￾NET 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 service 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.

Trademarks: Wiley, the Wiley 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.

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

v

To my husband Rob, together we can climb mountains. To my parents, who’ve given

me the foundation to succeed in life; I love you.

—Patricia

To my family, for their unwavering love and encouragement. To the PNWADG,

for the privilege of leading such a great group of developers.

—Teresa

I dedicate this book (or at least my chapters) to my beautiful wife Julie-Anne, for

carrying the world on her shoulders so I could work uninterrupted, and for her

unfailing support, encouragement, and sacrifice, despite illness and personal

hardship. And to my children, Madeleine and Eli, who tried to understand why

Daddy couldn’t spend so much time with them. I am indeed a lucky man.

—Graham

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

ii

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

vi

Credits

Authors Editorial Manager

Patricia Cardoza Kathryn Malm

Teresa Hennig

Armen Stein Vice President & Executive Group Publisher

Graham Seach Richard Swadley

Executive Acquisitions Editor Vice President and Executive Publisher

Robert Elliott Robert Ipsen

Development Editor Vice President and Publisher

Eileen Bien Calabro Joseph B. Wikert

Production Editor Executive Editorial Director

Angela Smith Mary Bednarek

Copy Editor Proofreading and Indexing

TechBooks TECHBOOKS Production Services

Senior Production Manager

Fred Bernardi

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

ii

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

vii

About the Authors

Authors

Patricia Cardoza is an Exchange and Outlook Applications developer, writer, and Microsoft Outlook MVP.

Patricia currently serves as Exchange Administrator and lead developer for Pacific Southwest Container,

a large manufacturing company. She has authored two other books, Special Edition Using Microsoft Office

Outlook 2003 and Absolute Beginner’s Guide to Microsoft OneNote 2003. She has also written numerous

articles about Outlook, Exchange, and wireless devices for a variety of journals including .NET Magazine

and Tech Republic. In her spare time, she loves to cook, read, watch movies with her husband, and spoil

her three cats.

Teresa Hennig is the owner of Eade.com, which specializes in developing cost-effective Access

applications. Her energy and enthusiasm are complemented by her ability to quickly grasp a situation

and provide a solution. Her biggest rewards are being a partner with her clients and sharing their joy in

their successes. Her site, www.DatabaseAnswerSite.com, is recognized as a resource for learning about

relational databases by several universities and technical support organizations.

An avid Access aficionado, Teresa is the president of the two leading Access user groups in the US

northwest: the Pacific Northwest Access Developers Group (PNWADG) and the Access Special Interest

Group. She is also dedicated to helping the Spinal Cord Society raise money to find a cure for spinal cord

injuries. And, she wishes that she had more time to enjoy dancing.

Graham Seach is Chief Development Officer for a Sydney-based database development company, Pacific

Database (www.pacificdb.com.au). He has been developing applications in Access since version 1.0, has

participated in the Microsoft Office 2003 beta program, and has presented at several Microsoft events,

notably the Microsoft Office 2003 System launch in Sydney and Melbourne in 2003.

Graham holds an MCP in Access Development, master-level Brainbench certification in Access, and is

recognized by Microsoft as an MVP (most valuable professional). He has received this award three times.

He regularly provides worldwide Access developer support for Microsoft via the Microsoft newsgroups,

and has been published in MSDN Magazine. Graham’s technical focus is now on Access and the SQL

Server integration, having provided many business solutions to a wide range of government, military,

and private organizations.

Armen Stein is the owner and founder of J Street Technology, Inc., a team of database application

developers in Redmond, Washington. He is President Emeritus of the Pacific Northwest Access

Developers Group, and has contributed to Access/Office/VB Advisor magazine and Smart Access. He has

taught database classes at Bellevue Community College and Seattle Central Community College and also

developed and taught his own 1-day training class called Access Development the J Street Way. Armen

earned a business administration/computer science degree from Western Washington University, and

has been developing computer applications since 1984. His other interests include activities with his

family, backgammon, cheering on the Mariners, and driving his 1969 Ford Bronco in the sun.

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

viii

Contributors

Steve Clark is a Microsoft Access MVP, an MCP, and a Project Manager and Developer for the FMS

Professional Solutions Group (www.fmsinc.com/consulting). As a developer, he specializes in database

development and has produced Access, VB(.NET), and ASP(.NET) applications for clients from all

elements of business, both nationally and internationally. He earned a bachelor’s degree in computer

science from the University of Cincinnati in 1994 and has been with FMS since 1998 and an Access MVP

since 2001. When he’s not managing or developing, he enjoys participating in all forms of motorcycle

racing.

Brian M. Sockey is founder and president of Farsight Solutions, Inc. (DBA TeleVantage NorthWest), a

Microsoft Business Solutions partner, and value-added reseller of Artisoft TeleVantage software-based

business telephone systems. Before starting TeleVantage NorthWest, Brian worked in the Developer

Support group at Microsoft, where he specialized in client/server development issues involving

Microsoft Access and SQL Server. Brian enjoys the great outdoors, theater, and good food (almost

everything except blue cheese).

Randall J. Weers, the founder and president of Procia, Inc., specializes in process and data management

applications utilizing the Microsoft Office suite. His primary focus is to help people manage business

processes and business rules through the appropriate application of process improvement and

automation. Randall’s experience includes everything from business process analysis to technical and

training manual writing. Randall makes his home in Seattle, Washington, where he is the facilitator for IT

WorkGroups, a Puget Sound-based alliance of senior-level independent information technology

professionals.

Sam Radakovitz joined Microsoft in 1998 and was a member of the Access team for Access 2000, 2002, and

2003. A lot of his efforts were focused on wizards and security features. For Access 2003, Sam was

thoroughly engaged in the new macro security. This made it a perfect fit for him to review this book’s

security chapters, especially the one on macro security. When creating Access applications, Sam enjoys

going outside the box and adding a bit of creativity. So, being invited to provide applications that could

demonstrate some of Access’s new features seemed like an awesome opportunity to share his ideas with

other developers.

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

Acknowledgments

We’d like to thank all the folks at Wiley who made this book possible. Bob Elliott worked with

Patricia and Teresa to build the amazing team of authors present on this book as well as to get all

the mounds of paperwork straight. Eileen Bien Calabro showed incredible patience formatting,

organizing, and editing our chapters. She reassured us that we could get all this done and done

well, in a remarkably short time, as long as we focused. Our technical editor, Michael Stowe,

tirelessly checked all of our facts and gently steered us back on course when needed. Techbooks

International Pvt. Ltd. worked incredibly hard to format all the pages and work with all of the

figures in this book. Thanks everyone!

—The Group

Thanks to all who had to put up with me when I had to meet deadlines. My husband, Rob,

endured many nights of takeout and several weekends when I was glued to my computer. My

parents, Joe and Sally, have supported me in everything I do and I thank God for them every day.

I would not be where I am today in my career if it were not for the Microsoft Most Valuable

Professional (MVP) program. I am honored to be included in that esteemed group of

professionals.

Lastly, I’d like to thank Teresa Hennig. She handled the majority of the scheduling of chapters

and helped me keep all of the information for this book organized. Coordinating four authors

and several contributors takes patience and dedication. Thanks Teresa for all your hard work.

—Patricia

First, I’d like to thank Paul Eade for being such an incredible resource and wealth of knowledge.

You are an amazing friend, and I cherish the opportunities to laugh and just be silly.

I can’t say enough about the Microsoft Access Team, including Rita Nikas. Their passion and

dedication has made Access an incredibly powerful program. A special note of appreciation to

Bill Ramos, Tim Getsch, and Sanjay Jacob for investing so much of themselves into finding out

what developers want, for making the ADE so awesome, and for being so responsive to all of our

requests! I also want to thank Michael Kaplan for sharing his opinions and encouragement and

for doing so much for the Access groups.

To everyone I’ve met through this book . . . thank you for an amazing opportunity to enjoy a new

challenge and to help Access developers! Very special thanks go to Patricia Cardoza, who seems

like Wonder Woman—balancing a career and family while writing multiple books. She not only

wrote several elements, but also reviewed every chapter and helped us all in countless ways.

And thanks to Randy Weers, who offered help when I needed it. Yes, work should be fun! And

last thanks go to all the Mikes and Michaels. I can’t imagine a world without Michaels!

—Teresa

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

Acknowledgments

Needless to say, I want to first thank my wife and children for their support, encouragement, and

understanding throughout a very difficult and demanding time.

I want to thank Rita Nikas, my Microsoft MVP Lead, who first made me aware of the book, and

whose resolute determination to solve problems and to source much needed information will

always be appreciated.

I also want to thank Mike Gunderloy, who, despite the fact that we had never met, kindly sent

pages from his own book, to help me with mine; a gesture I will never forget.

Finally, I would like to express my thanks to Professor Roger Box of Charles Sturt University,

who gave me a 2-week extension on my final assignment, so I could make a book deadline.

—Graham

I would like to acknowledge the encouragement and support from my wife, Lori, and kids,

Lauren and Jonathan.

—Armen

I’m glad that I could help Randy and Teresa. It is cool to feature some of my work on the book’s

Web site. I want to thank my girlfriend Denice and my parents for putting up with my long

hours of work and extra work! Without everyone’s support and understanding my daily life

would be much more difficult!

—Steve

My thanks to Teresa Hennig for giving me the opportunity to contribute to this book and for

cracking the whip when I was struggling to keep moving forward. My deep appreciation to Sam

Radakovitz for the invaluable insight into the new security features of Access. Thanks also to the

members in IT Workgroups who helped me deliver a better picture for the Access Security

model. And most of all, in loving memory of my wife Lisa, I dedicate my efforts on this book.

—Randall

x

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

Contents

About the Authors vii

Acknowledgments ix

Foreword xxiii

Introduction 1

Chapter 1: Introduction to Microsoft Access 7

Why Use Microsoft Access? 7

Is Access the Only Database I’ll Ever Need? 8

Microsoft Access 8

MSDE 8

SQL Server 9

Automating Microsoft Access Without VBA Code 10

The Database Wizard 10

Creating a Switchboard for Your Database 13

Begin at the Beginning 14

Create a Switchboard through the Switchboard Manager 19

Add and Configure Controls Using Wizards 20

Building Automation into Your Project 21

Using Expression Builder 22

Using the Macro Builder 23

Using the Code Builder 24

Summary 25

Chapter 2: Access, VBA, and Macros 27

VBA within Access 27

Writing Code within Modules 27

VBA versus Macros in Access 29

Macros in Access 2003 30

Advantages to Using VBA over Macros 34

Summary 35

Chapter 3: New Features in Access 2003 (and 2002) 37

Easy Upgrading 37

Database Structure and Management Tools for Developers 38

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

WY007-FM WY007-Sample WY007-Sample-v4.cls February 28, 2004 13:37

Contents

Find Object Dependencies 38

Form/Report Error Checking 40

Propagate Field Properties 42

Customize SQL Font 43

Get Context-Sensitive SQL Help 44

Backup Database/Project 46

Sorting Option on Lookup Wizard 47

Copy and Paste Linked Table as Local Table 47

Notable Mention 49

New Wizards and Builders and Managers 51

A Wizard for Every Occasion 52

Available Builders 52

Managers 53

Changes to Jet 53

Service Pack 8 53

Security and Related Features 54

What Is Macro Security? And Why Are We Talking about Macros? 54

Digital Signatures 56

Expression Sandbox 58

End-User Enhancements 59

Pivot Charts 59

Windows XP Theming 59

Templates 60

Smart Tags 61

AutoCorrect Options Buttons 62

Better Accessibility 62

XML and Access 62

Relating XML to HTML 63

The Advantages of XML 64

Using Access 2003 with SharePoint Services 66

Export Information to a SharePoint Server 66

Import Information From a SharePoint Server 67

Access Developer Extensions 68

The Property Scanner 69

The Custom Startup Wizard 69

The Package Wizard 71

Summary 72

Chapter 4: VBA Basics 73

VBA Objects 73

Objects 73

xii