Table of Contents Table of Contents 1 ............................................................................................................................................................................ Back Cover 2 ........................................................................................................................................................................................... Beginning Access 2007 VBA 3 .................................................................................................................................................. Introduction 5 ........................................................................................................................................................................................... What This Book Covers .............................................................................................................................................................. 6 How This Book Is Structured .................................................................................................................................................... 7 What You Need to Use This Book .......................................................................................................................................... 9 Conventions .................................................................................................................................................................................. 10 Source Code ................................................................................................................................................................................. 11 Errata ............................................................................................................................................................................................... 12 p2p.wrox.com ............................................................................................................................................................................... 13 Chapter 1: Introduction to Access 2007 VBA 14 ........................................................................................................ What Is Access 2007 VBA? ................................................................................................................................................... 15 What’s New in Access 2007 VBA? ....................................................................................................................... 16 Access 2007 VBA Programming 101 ................................................................................................................................. 17 Summary ........................................................................................................................................................................................ 26 Exercises ........................................................................................................................................................................................ 27 Chapter 2: The Basics of Writing and Testing VBA Code 28 ........................................................................... Creating Modules ........................................................................................................................................................................ 29 Standard Modules versus Class Modules ........................................................................................................................ 30 Creating and Calling Procedures ......................................................................................................................................... 32 Using Variables and Constants to Store Values ............................................................................................................ 40 Controlling Program Flow ....................................................................................................................................................... 43 Documenting Your Code .......................................................................................................................................................... 47 Error Debugging .......................................................................................................................................................................... 48 Error Handling .............................................................................................................................................................................. 52 Summary ........................................................................................................................................................................................ 55 Exercises ........................................................................................................................................................................................ 56 Chapter 3: Programming Applications Using Objects 57 .................................................................................... Viewing Objects Using the Object Browser ..................................................................................................................... 63 Ways to Refer to Objects ........................................................................................................................................................ 65 Using Access Collections and Objects .............................................................................................................................. 66 Summary ........................................................................................................................................................................................ 69 Exercises ........................................................................................................................................................................................ 70 Chapter 4: Creating Your Own Objects 71 ...................................................................................................................... Create Custom Properties for Existing Objects .............................................................................................................81 Create Custom Methods for Existing Objects ................................................................................................................ 82 Summary ........................................................................................................................................................................................ 83 Exercises ........................................................................................................................................................................................ 84 Chapter 5: Interacting with Data Using ADO and SQL 85 .................................................................................. Using ADODB to Work with Data ........................................................................................................................................ 86 Introduction to SQL ................................................................................................................................................................. 100 Using ADOX to Manipulate Data Objects ..................................................................................................................... 109 Summary ...................................................................................................................................................................................... 111 Exercises ..................................................................................................................................................................................... 112 Chapter 6: Building Interactive Forms 113 ...................................................................................................................... Form Navigation and Flow ................................................................................................................................................... 114 Working with Controls ............................................................................................................................................................ 117 Building User Interaction ...................................................................................................................................................... 121 Adding Polish to Your Forms .............................................................................................................................................. 122 Summary ..................................................................................................................................................................................... 123 Exercises ..................................................................................................................................................................................... 124 Chapter 7: Importing, Linking, and Exporting Using External Data Sources 125 ........................... Linking, Importing, and Exporting Overview ............................................................................................................... 126 Access and Other Databases ............................................................................................................................................. 127 Transferring Complete SQL Server Database ............................................................................................................ 129 Spreadsheets .............................................................................................................................................................................130 Text Files ..................................................................................................................................................................................... 131 XML Files .................................................................................................................................................................................... 132 E-mails and Outlook ............................................................................................................................................................... 134 Other Ways to Export Data ................................................................................................................................................. 135 Summary ..................................................................................................................................................................................... 136 Exercises ..................................................................................................................................................................................... 137 Chapter 8: Using Access with Web Services and SharePoint Lists 138 ................................................ Introduction to Web Services ............................................................................................................................................. 139 Using Data from Web Services ......................................................................................................................................... 140 Introduction to SharePoint Lists ........................................................................................................................................ 143 Using Data from SharePoint Lists .................................................................................................................................... 144 Summary ..................................................................................................................................................................................... 149 Exercises ..................................................................................................................................................................................... 150 Chapter 9: Creating Reports and Web-Enabled Output 151 ........................................................................... Exporting Access Data to Web Formats ........................................................................................................................158 Summary ..................................................................................................................................................................................... 159 Exercises ..................................................................................................................................................................................... 160 Chapter 10: Building SQL Server Applications with Access Projects 161 ............................................. Setting Up SQL Server ......................................................................................................................................................... 162 Creating Access Projects (ADPs) ..................................................................................................................................... 164 Working with SQL Server Objects from Access ......................................................................................................... 166 Migrating an Existing Access Database to an Access Project ............................................................................. 172 Summary ..................................................................................................................................................................................... 176 Exercises ..................................................................................................................................................................................... 177 Chapter 11: Advanced Access Programming 178 ..................................................................................................... Using Dynamic Link Libraries (DLLs) ............................................................................................................................. 179 Automation ................................................................................................................................................................................. 183 ActiveX Controls ...................................................................................................................................................................... 188 Libraries ....................................................................................................................................................................................... 190 Add-Ins ......................................................................................................................................................................................... 192 Transactions ............................................................................................................................................................................... 193 Summary ..................................................................................................................................................................................... 194 Exercises ..................................................................................................................................................................................... 195 Chapter 12: Finishing the Application 196 ...................................................................................................................... Multiuser Considerations ...................................................................................................................................................... 197 Optimizing Your Applications .............................................................................................................................................. 200 Securing Your Application .................................................................................................................................................... 203 Distributing Your Application ............................................................................................................................................... 204 Maintaining the Application ................................................................................................................................................. 205 Summary ..................................................................................................................................................................................... 206 Exercises ..................................................................................................................................................................................... 207 Chapter 13: Case Study 1-Project Tracker Application 208 ............................................................................. Design Specifications ............................................................................................................................................................ 209 Building the Database ........................................................................................................................................................... 212 Building the User Interface .................................................................................................................................................. 214 Building the Class Modules for the Objects ................................................................................................................. 219 Building the Standard Modules ..........................................................................................................................................233 Connecting the User Interface to the Code .................................................................................................................. 247 Touring the Finished Project Tracker Application ...................................................................................................... 269 Summary ..................................................................................................................................................................................... 272 Chapter 14: Case Study 2-Customer Service Application 273 ....................................................................... Design Specifications ............................................................................................................................................................ 274 Building the Database and Database Objects ............................................................................................................ 276 Building the User Interface .................................................................................................................................................. 281 Building the Class Module for the Objects ................................................................................................................... 284 Building the Standard Modules ..........................................................................................................................................289 Connecting the User Interface to the Code .................................................................................................................. 295 Touring the Finished Customer Service Application .................................................................................................302 Summary ..................................................................................................................................................................................... 305 Index 306 .................................................................................................................................................................................................... B ...................................................................................................................................................................................................... 308 C ......................................................................................................................................................................................................309 D ......................................................................................................................................................................................................312 E ...................................................................................................................................................................................................... 314 F ...................................................................................................................................................................................................... 316 G ..................................................................................................................................................................................................... 318 H ......................................................................................................................................................................................................319 I ........................................................................................................................................................................................................320 J .......................................................................................................................................................................................................321 K ...................................................................................................................................................................................................... 322 L .......................................................................................................................................................................................................323 M ..................................................................................................................................................................................................... 324 N ......................................................................................................................................................................................................325 O ..................................................................................................................................................................................................... 326 P ...................................................................................................................................................................................................... 327 Q ..................................................................................................................................................................................................... 329 R ......................................................................................................................................................................................................330 S ...................................................................................................................................................................................................... 332 T ...................................................................................................................................................................................................... 335 U ......................................................................................................................................................................................................337 V ...................................................................................................................................................................................................... 338 W .................................................................................................................................................................................................... 340 X ...................................................................................................................................................................................................... 341 Y ...................................................................................................................................................................................................... 342 List of Try It Outs 343 ....................................................................................................................................................................... Beginning Access 2007 VBA Beginning Access 2007 VBA byDenise Gosnell Wrox Press 2007 (574 pages) ISBN:9780470046845 If you want to take your Access databases to a more robust level of functionality, this comprehensive guide provides the tools and techniques you'll need in order to build more sophisticated solutions. Table of Contents Beginning Access 2007 VBA Introduction Chapter 1 -Introduction to Access 2007 VBA Chapter 2 -The Basics of Writing and Testing VBA Code Chapter 3 -Programming Applications Using Objects Chapter 4 -Creating Your Own Objects Chapter 5 -Interacting with Data Using ADO and SQL Chapter 6 -Building Interactive Forms Chapter 7 -Importing, Linking, and Exporting Using External Data Sources Chapter 8 -Using Access with Web Services and SharePoint Lists Chapter 9 -Creating Reports and Web-Enabled Output Chapter 10-Building SQL Server Applications with Access Projects Chapter 11-Advanced Access Programming Chapter 12-Finishing the Application Chapter 13-Case Study 1-Project Tracker Application Chapter 14-Case Study 2-Customer Service Application Index List of Try It Outs 1 / 344 Beginning Access 2007 VBA Back Cover If you want to take your Access databases to a more robust level of functionality, then this is the book for you. It provides the tools and techniques you'll need in order to build more sophisticated solutions. You'll find plenty of source code and fully developed sample applications to guide you along the way. After walking through the features of Access 2007 VBA, you'll learn all about object-oriented programming and ADO. You'll then progress to advanced VBA concepts such as working with external DLLs and using automation to control external programs. And with the help of two comprehensive case studies, you'll gain hands-on experience in building real-world solutions using the concepts covered in the book. From creating custom objects and reports programmatically to integrating Access with web services and SQL Server, this hands-on guide gets you started quickly. What you will learn from this book Techniques for using ActiveX Data Objects (ADO) to retrieve and update data Steps for building reports and web content from the database How to develop SQL Server solutions with Access projects Tips for fine-tuning and distributing your application Ways to import, link, and export data to and from external data sources How to send emails programmatically from your database How to create interactive forms, export data to HTML, enhance security, and much more Who this book is for This book is for the Access 2007 user who is just getting started in creating their own applications as well as the programmer who is new to Access development. No prior experience with VBA is required. About the Author Denise Gosnell is a software patent attorney and technology consultant with Gosnell & Associates, Inc., which she founded in 2005. 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 was recognized in February 2005 with the Indianapolis Business Journal award: “Top 40 Under 40,” which recognizes the top up-and-coming professionals under the age of 40 in the city of Indianapolis each year. She also appeared on the Inside Indiana Business with Gerry Dick television show in March 2005 to discuss hi-tech issues facing the business world. Denise has over 12 years of experience in 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, where she currently serves as an adjunct professor for the Computer Science Department. Denise obtained a doctor of jurisprudence degree from Indiana University School of Law in Indianapolis. Denise has authored and coauthored seven other software development books to date, covering topics such as database development, Access, Visual Basic .NET, and web services. Most recently, Denise authored Professional Development with Web APIs (Wiley 2005) and Beginning Access 2003 VBA (Wiley 2004). 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. 2 / 344 Beginning Access 2007 VBA Beginning Access 2007 VBA Denise Gosnell Wiley Publishing, Inc. Published by Wiley Publishing, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 www.wiley.com © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana ISBN 9780470046845 10 9 8 7 6 5 4 3 2 1 Library of Congress Cataloging-in-Publication Data Gosnell, Denise. Beginning Access 2007 VBA / Denise Gosnell. p. cm. ISBN-13: 978-0-470-04684-5 (paper/website) ISBN-10: 0-470-04684-8 (paper/website) 1. Microsoft Access. 2. Database management. 3. Microsoft Visual Basic for applications. I. Title. QA76.9.D3G68 2007 005.75'65-dc22 2007004985 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, or online at http://www.wiley.com/go/permissions. 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 THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT 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 PUBLISHER 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 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 RECOMMENDATIONS 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 please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572- 3993 or fax (317) 572-4002. 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. Microsoft and Access are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. 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. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. Dedication This book is dedicated to my daughter, Victoria. You are too young to understand this now, but I hope to teach you that you can accomplish anything you set your mind to. The sky is truly the limit. About the Author Denise Gosnell is a software patent attorney and technology consultant with Gosnell & Associates, Inc., which she founded in 2005. 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 was recognized in February 2005 with the Indianapolis Business Journal award: “Top 40 Under 40,” which recognizes the top up-and-coming professionals under the age of 40 in the city of Indianapolis each year. She also appeared on the Inside Indiana Business with Gerry Dick television show in March 2005 to discuss hi-tech issues facing the business world. Denise has over 12 years of experience in 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, where she currently serves as an adjunct professor for the Computer Science Department. Denise obtained a doctor of jurisprudence degree from Indiana University School of Law in Indianapolis. Denise has authored and coauthored seven other software development books to date, covering topics such as database development, Access, Visual Basic .NET, and web services. Most recently, Denise authored Professional Development with Web APIs (Wiley 2005) and Beginning Access 2003 VBA (Wiley 2004). 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. Denise can be reached at [email protected] or [email protected]. Acknowledgments To my assistant Pam Woodsford, keep up your progress and aim high. You are only limited by your own imagination. I am proud of how fast you have grown in such a short amount of time and look forward to watching you reach your full potential. I would like to offer a special thanks to my friend Jonathon Walsh for helping me update the content in this book for Access 2007, and for contributing the materials on SharePoint Lists. I always enjoy working with you and hope to get many more opportunities. I had a great technical editor on this book too, quite possibly the best one I have ever had to date: Auri Rahimzadeh. Thanks Auri! Last, but certainly not least, I would like to thank Rosanne Koneval, Chris Webb, and the staff at Wiley who played such an important role in producing this book. Thanks for putting up with me through all the delays. I hope the end result makes up for it! Credits Executive Editor Chris Webb Development Editor Rosanne Koneval 3 / 344 Beginning Access 2007 VBA Rosanne Koneval Technical Editor Auri Rahimzadeh Production Editor Elizabeth Kuball Copy Editor Foxxe Editorial Services Editorial Manager Mary Beth Wakefield Production Manager Tim Tate Vice President and Executive Group Publisher Richard Swadley Vice President and Executive Publisher Joseph B. Wikert Graphics and Production Specialists Denny Hager Jennifer Mayberry Barbara Moore Ronald Terry Quality Control Technicians Laura Albert John Greenough Project Coordinator Erin Smith Proofreading Aptara Indexing Potomac Indexing, LLC Anniversary Logo Design Richard Pacifico 4 / 344 Beginning Access 2007 VBA Introduction Microsoft Access 2007 is a powerful database application that allows you to build standalone and client-server database applications. Access applications are widespread in the enterprise. Access has powerful wizards and tools that make it relatively easy for users to build a database application that then grows in popularity and requirements that are beyond the capabilities of the relatively simple design. VBA is a powerful programming language that can be used in Access applications to expand the functionality in ways you never imagined. If you are hoping to take your current Access databases or future databases to a more robust level of functionality, then Beginning Access 2007 VBA is the book for you. Whom This Book Is For This book assumes that you have created Access databases in the past that have tables, forms, and possibly macros. You may have even written some VBA code in Access before, or you may have experience with Visual Basic or VBScript and want to learn how to write VBA code from within Access applications. No prior experience with VBA is required. As long as you have the ability to create Access tables and forms and the desire to learn VBA, this book will have a lot to offer you. It is very possible that you have realized limitations with your current Access applications and now desire to build more sophisticated solutions. For example, your current application may need additional features included that must be programmed with VBA. Your current application may need to be improved to support more users or a client-server environment because it has been a great success and has outgrown the current design. Or, you may want to begin creating your new database applications with expanded functionality. The tools and techniques covered in this book will teach you how to take your Access applications to the next level. 5 / 344 Beginning Access 2007 VBA What This Book Covers Access 2007 is vastly different in many ways from Access 2003, such as the revamped user interface that uses “ribbons,” among other differences. However, Access 2007 VBA has not changed a whole lot since the prior versions of Access. The VBA interface for Access 2007 still uses the same old user interface as before, not the ribbons that Access 2007 uses. The VBA language syntax has not changed very much either. Even if you are using a prior version of Access, you can still gain a lot from this book. However, there will be some examples that deal with features only supported in Access 2007. After reading this book, you will learn: Basic programming life-cycle and architecture concepts How VBA can be used for professional application development The basics of programming using VBA Basic object-oriented programming techniques using VBA How to use existing objects How to create custom objects How to use ActiveX Data Objects (ADO) to retrieve and update data How to retrieve data from external data sources How to create reports and web content from the database How to integrate with Office applications How to integrate with SharePoint Lists How to build SQL Server solutions with Access projects How to fine-tune and distribute the application 6 / 344 Beginning Access 2007 VBA How This Book Is Structured Just as with the prior edition, the chapters are structured with standalone examples that do not require you to follow the chapters in order. This structure will allow you to go straight to a chapter that interests you, or to skip around among chapters as desired. Certainly, there are some chapters that will not make as much sense unless you have read the prior chapters or are already familiar with the topics covered therein. At least you will not be required to read the prior chapters that cover topics you already know just for the purpose of building parts of a solution that are prerequisites to the chapter you are really interested in. Chapter 1 will provide an overview of Access 2007 VBA and the programming life cycle, and will introduce the Visual Basic editor environment to you. Chapter 2 teaches you several important VBA programming concepts, such as how to create code procedures, how to control the flow of execution of your code, how to make decisions in your code, how to handle errors, how to debug your application, and so on. Chapters 3 and 4 will demystify the concept of object-oriented programming and will teach you how to use existing objects and to create your own custom objects. Chapter 5 will illustrate how to use ADO to retrieve and update data in databases. Chapter 5 will also illustrate how to create and execute SQL statements using ADO. Chapter 6 discusses how you can build interactive forms. Chapter 7 covers various ways to import, link, and export data to and from external data sources, such as MDB files, SQL Server databases, XML files, and other data sources. Chapter 8 illustrates how to use VBA with web services and SharePoint Lists. Chapter 9 describes how you can use VBA to create reports programmatically, as well as how to export data in your Access 2007 application to various web formats, such as HTML, ASP, and data access pages. Chapter 10 illustrates how to create a new Access project as a front end to SQL Server for more robust applications, and covers how to migrate an existing Access database to an Access project that uses SQL Server. Chapter 11 covers various advanced VBA topics, such as how to work with external DLLs, how to use automation to control external programs such as Microsoft Word and Excel, as well as security, transactions, and multiuser considerations. Chapter 12 then rounds out the prior chapters by covering some finishing touches that you can apply to prepare your application for distribution. Chapters 13 and 14 provide two comprehensive case studies that will give you hands-on experience in building real-world solutions using the concepts covered in the book. Chapter 13 focuses on building a standalone project-tracking application. The project-tracking application allows a user to track projects and related details, such as comments, contacts, and file attachments. A few examples of the user interface for the project tracking application you will create in Chapter 13 are shown in Figures I-1 and I-2. Figure I-1 Figure I-2 Chapter 14 focuses on building a client-server customer service application that uses SQL Server as the database. The customer service application allows a customer service agent to locate a customer record based on search criteria and then view and modify the customer record details. An example of the user interface for this customer service application is shown in Figure I-3. 7 / 344