Access 2010 Programmer's Reference E-Book

Teresa Hennig

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1997

Veröffentlichungsjahr: 2010

Beschreibung

A comprehensive guide to programming for Access 2010 and 2007 Millions of people use the Access database applications, and hundreds of thousands of developers work with Access daily. Access 2010 brings better integration with SQL Server and enhanced XML support; this Wrox guide shows developers how to take advantage of these and other improvements. With in-depth coverage of VBA, macros, and other programming methods for building Access applications, this book also provides real-world code examples to demonstrate each topic. Access 2010 Programmer's Referenceis a comprehensive guide to the best-of-breed techniques for programming Access applications. Coverage Includes: * Introduction to Microsoft Access 2010 * New Features * Upgrading and Converting to Access 2010 * Macros in Access 2010 * Using the VBA Editor * VBA Basics * Using VBA in Access * Creating Classes in VBA * Extending VBA with APIs * Working with the Windows Registry * Using DAO to Access Data * Using ADO to Access Data * Using SQL with VBA * Using VBA to Enhance Forms * Enhancing Reports with VBA * Customizing the Ribbon * Customizing the Office Backstage * Working with Office 2010 * Working with SharePoint * Working with .NET * Building Client-Server Applications with Access * The Access 2010 Templates * Access Runtime Deployment * Database Security * Access 2010 Security Features

Leseprobe

Table of Contents

Title Page

Copyright

Dedication

Credits

About the Authors

Acknowledgments

Introduction

1 Introduction to Microsoft Access 2010

A Brief History of Access

When to Use Access

Access Database Basics

Access Database Objects

Summary

2 New Features

New Look

Development Environment

Forms

Macros

Integration with SharePoint

Browser Interface/Applications

What's Gone or Deprecated

Summary

3 Upgrading and Converting to Access 2010

To Convert or To Enable

Installing Multiple Versions of Access on One PC

Changing File Formats

Converting a Secured Database

Converting a Replicated Database

Enabling a Database

Access 2010: 64-Bit Considerations

Summary

4 Macros in Access 2010

VBA versus Macros in Access

Types of Macros

Creating Macros in Access 2010

Macro Objects and Embedded Macros

Data Macros

Summary

5 Using the VBA Editor

Anatomy of the VBA Editor

Using the Object Browser

Testing and Debugging VBA Code

Using Option Statements

Summary

6 VBA Basics

The Mindset of a Programmer

Anatomy of VBA Procedures

VBA Keywords

VBA Operators

Variables and VBA Syntax

Other VBA Components

VBA Objects

Using Code Behind Forms and Reports

Using VBA Code to Call Macros

Writing Code in Modules

Example: User-Defined Function

Summary

7 Using VBA in Access

When Events Fire

VBA Procedures

Evaluating Expressions in VBA

Using Recordsets

Using Multiple Recordsets

Using VBA in Forms and Reports

Debugging VBA

Investigating Variables

Common VBA Techniques

String Concatenation Techniques

VBA Error Handling

Summary

8 Creating Classes in VBA

Why Use Classes?

A Touch of Class

Creating a Class Module

Naming and Identifying Objects

Using Class Events

Forms and Reports as Objects

Variable Scope and Lifetime

The Me Property

Creating a Clone Method

Creating and Using Collection Classes

The Three Pillars

Summary

9 Extending VBA with APIs

Introducing the Windows API

Introducing Linking

Declaring APIs

Understanding C Parameters

Err.LastDLLError

Distributing Applications That Reference Type Libraries and Custom DLLs

Useful API Functions

Summary

10 Working with the Windows Registry

About the Registry

Using the Built-In VBA Registry Functions

Using the Windows Registry APIs

Summary

11 Using DAO to Access Data

Data Access Objects

Why Use DAO?

New Features in DAO

Referring to DAO Objects

The DBEngine Object

The Database Object

DAO Object Properties

Creating Schema Objects with DAO

Data Access with DAO

Append-Only Fields

Summary

12 Using ADO to Access Data

Introduction to ADO in Access

Adding ADO References

Connecting to ADO Data Sources

Data Access with ADO

Using ADO Events

Schema Recordsets with ADO

Creating Schema with ADOX

Summary

13 Using SQL with VBA

Working with SQL Strings in VBA

Using SQL When Opening Forms and Reports

Using SQL to Enhance Forms

The ReplaceOrderByClause and ReplaceWhereClause Functions

Summary

14 Using VBA to Enhance Forms

VBA Basics

Creating Forms the 2010 Way

Summary

15 Enhancing Reports with VBA

Introduction to Reports

Creating a Report

Working with VBA in Reports

Important Report Events and Properties

Report Properties

Working with Charts

Common Report Requests

Layout View

Report View

Summary

16 Customizing the Ribbon

Ribbon Overview

Custom Menu Bars and Toolbars

Ribbon Customization Using the Options Dialog Box

Ribbon Customization

Saving a Custom Ribbon

Specifying the Custom Ribbon

Creating an Integrated Ribbon

Creating a Ribbon from Scratch

More Ribbon Tips

Summary

17 Customizing the Office Backstage

Introducing the Office Backstage

Writing a Backstage Customization

Controls in the Backstage

Designing the Layout of Components

Extending the Existing Backstage

Backstage-Specific Callbacks

Backstage Scenarios

Summary

18 Working with Office 2010

Working with Outlook 2010

Working with Excel 2010

Working with Word 2010

Summary

19 Working with SharePoint

SharePoint 2010

Access Features Overview

SharePoint Features in Access

Access Features on SharePoint

Summary

20 Working with .NET

Overview

Visual Studio .NET 2010

Using Access Databases in .NET

Automating Access with .NET

Creating COM Add-Ins for Access

Using .NET Code in Access

Summary

21 Building Client-Server Applications with Access

Database Application Evolution

Client-Server Applications

The ACCDB/MDB File Format

The ADP File Format

ACCDB/MDB versus ADP

Controlling the Logon Process

Binding ADO Recordsets

Working with Unbound Forms

Summary

22 The Access 2010 Templates

Access 2010 Template Features

Access 2010 Templates Types

Application Parts

Table Field Templates

Save As Template

The ACCDT File Format

Summary

23 Access Runtime Deployment

The Access 2010 Runtime

Deploying the Access Runtime

Summary

24 Database Security

ACCDB File Security

MDB File Security

Summary

25 Access 2010 Security Features

The Office Trust Center

Disabled Mode

Digital Signatures and Certificates

Access Database Engine Expression Service

Summary

Appendix A: The Access Object Model

Appendix B: DAO Object Method and Property Descriptions

Appendix C: ADO Object Model Reference

Appendix D: 64-Bit Access

Appendix E: References for Projects

Appendix F: Reserved Words and Special Characters

Appendix G: Naming Conventions

Appendix H: The Access Source Code Control

Appendix I: Tips and Tricks

Index

Microsoft® Access® 2010 Programmer's Reference

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2010 by Teresa Hennig, Rob Cooper, Geoffrey Griffi th, Jerry Dennison

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-59166-6

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, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], 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 specifi cally disclaim all warranties, including without limitation warranties of fi tness 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 Web site 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 Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites 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 [phone], outside the United States at [phone] or fax [phone].

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 Control Number: 2010929738

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affi liates, in the United States and other countries, and may not be used without written permission. Microsoft and Access are 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.

To my family and incredible friends, you help me to realize that every day is filled with precious moments—sights, sounds, and emotions to be cherished. May we all remember to pause and savor the blessings that we have, especially the time that we share with those we love.

—Teresa

To my family, for all of the love and support you've shown me over the years.

—Rob

To my wife, Jamie, and my son, Ryan, I couldn't have been blessed with a better family and this would not have been possible without all of your love and support!

—Geoff

To my wife Dianne, my son, Jeremy, my daughter, Amber, and my late parents, Clifford and Jeanette Dennison. You are my inspiration and my life. Thanks Dad, for instilling into me the insatiable appetite to explore and learn, without which I would not be where I am. To my grandchildren: Brianna, Fisher, and Huntlee. You are our future.

—Jerry

Credits

Executive Editor

Robert Elliott

Project Editor

Tom Dinse

Contributing authors and Technical Editors

Ben Clothier

Doug (Dagi) Yudovich

Technical Editors

Dr. Jeff Boyce

Albert D. Kallal

Armen Stein

Contributors

Dane Miller

Garry Robinson

Steve Schapel

Larry Strange

Production Editor

Kathleen Wisor

Copy Editors

Nancy Rapoport

Paula Lowell

Editorial Director

Robyn B. Siesky

Editorial Manager

Mary Beth Wakefield

Marketing Manager

Ashley Zurcher

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Barry Pruett

Associate Publisher

Jim Minatel

Project Coordinator, Cover

Lynsey Stanford

Proofreader

Nancy Carrasco

Indexer

Robert Swanson

Cover Designer

Michael E. Trent

Cover Image

© Randolph Jay Braun/istockphoto

About the Authors

Teresa Hennig loves challenges, solving problems, and making things happen. So it is no surprise that she was immediately hooked on Access; by its tools for rapid development and the ability to quickly create intuitive, user-friendly applications. Within a month, she started her own company as an Access developer and business consultant (1997). With a strong background in business and project management, Teresa (and her company, Data Dynamics NW), focuses on using Access to provide cost-effective custom database solutions. In recognition of her expertise and dedication to the Access community, Teresa has been awarded as a Microsoft Access MVP (Most Valuable Professional) every year since 2006. She continues to serve as President of both the Pacific Northwest Access Developers Group (PNWADG) and the Seattle Access Group. Her leadership, expertise, and service to the Access community have also earned her recognition as a National Community Champion from INETA. Being the lead author of several Access books has afforded Teresa the opportunity to work with esteemed colleagues and to invite others to share the experience of becoming published authors. Learn more at www.DataDynamicsNW.com and www.SeattleAccess.org.

Rob Cooper is a Senior Test Lead at Microsoft. He started at Microsoft as a support engineer in Charlotte, North Carolina in 1998 and joined the Access 2003 test team in Redmond in 2001. During the Access 2010 release, he led the team that worked on exciting new features such as the Macro Designer, Navigation Control, Web Browser Control, and the design and migration of objects for Access Services. He also helped test the 64-bit version of VBA in Office 2010 and programmability and security in Access 2010. He is currently working on Microsoft Bing. Rob has spoken at user group meetings and conferences such as the Portland Access User Group conference and Office DevCon in Brisbane, Australia, and has written for the Microsoft Knowledge Base. Aside from writing code in Access and C#, he also enjoys spending time with his family cooking, watching movies, going to the zoo and aquarium, and hanging out in and around Seattle.

Geoffrey Griffith is a professional software developer from Colorado, where he owns his own software consulting company. He holds a Bachelor of Science degree in Computer Science from the University of Colorado at Boulder, where he studied software engineering. He began his Access life working on various database systems, starting with Access 2.0, developed at GBS, Inc., a software development firm in Boulder, Colorado. An avid Access user, he worked on the Microsoft Access Team for the Access 2007 release. Today, Geoff continues his work with Access, by writing books about Access and building applications that use Access databases.

Jerry Dennison has over 18 years of professional experience designing and developing MS Access database applications beginning with Access v1.0. He has been awarded the Microsoft Office Access Most Valuable Professional Award for the past four years. Jerry is an active contributor and Administrator at UtterAccess.com, the premier MS Access forum on the web. He is considered by many of his peers to be one of the foremost experts on the Forms of Data Normalization (a set of guidelines for relational databases developed by Dr. E. F. Codd). Jerry currently lives in Easley, South Carolina with his wife of 21 years and their two dogs, Duke and Duchess.

About the Contributing Authors and Technical Editors

Ben Clothier is an Access developer working as an independent contractor through development shops such as Advisicon and J Street Technology. He is also a certified MySQL developer, and has designed and supported Access front-end applications for corporate databases. In recognition of his contributions to the Access community, Ben is recognized as a Microsoft Access MVP (Most Valued Professional). Ben lives in San Antonio, Texas with his wife, Suzanne, and his son, Harry.

Suzanne, only with you could I have the time and energy to become a contributing author—you are the absolute best!

Doug (Dagi) Yudovich is the Director of Business Information Applications for UW Physicians in Seattle. The department's primary responsibilities range from developing enterprise-level Web-based reports and OLAP objects to developing database applications in support of various business needs for managing data. The applications vary in scope, from stop-gap applications, to bolt on tools, to mission critical–level applications that support up to 250 users. All of the database applications use Access for the FE, with some using Jet as the database platform, and some using SQL Server. Doug is also an Administrator on UtterAccess Forums and UtterAccess's Access Wiki. Joining UA in 2004, Doug progressed through the ranks (VIP, Editor, Administrator). UA is Doug's cyber home, where he shares his passion and knowledge of database design and information management with members who seek help, and with fellow developers discussing best practices. Doug is an active member of the Pacific Northwest Access Developer Group (PNWADG) and a presenter in the Seattle Access Group (SAG). Doug participated in two Developers' Kitchen events with the Microsoft Access Dev Team to work on early development versions of Access 2010. In acknowledgment of his contributions to the Access developers' community in UtterAccess and the user groups, and his work with the Access Dev Team, Doug was awarded as a Microsoft Access MVP in 2009 and 2010.

I'd like to thank my wife, Lisa, who patiently picks up my slack as I dive head-first into new adventures. I love you.

About the Technical Editors

Dr. Jeff Boyce has been providing process and technology solutions to both public and private sectors for over 30 years. Jeff's approach focuses first on business need, emphasizing processes and practices before evaluating suitable technologies. Jeff has designed and developed over 25 applications. Most recently, his integrated Microsoft Office solutions provide data-based applications employing Access, Word, and SQL Server. The support he has provided in the Microsoft Access newsgroups has resulted in his recognition as a Microsoft Access MVP for the past eight years. When not working at his day job, consulting, or offering support in the newsgroups, Jeff enjoys wallyball, swimming, and recreational landscaping.

Jeff acknowledges and thanks his wife, Sue—her forbearance made his contribution possible.

Albert D. Kallal is the owner of HCS consulting group and has been professionally developing software for more than 20 years. His first major project started while he was studying Computing Science at the University of Alberta. Albert was the original developer of a pilot project called Anat-sim, which was the basis for Omni-sim, the first successful commercial authoring system that allowed educators to create applications without having to write code. Albert's early success was an omen of great things to come. His software is currently used in more than six countries around the world with database platforms ranging from PDAs to server and mainframe systems. In recognition of Albert's contributions to the Access community, he received a Microsoft Most Valuable Professional award. Always seeking to stay on the leading edge, Albert was an early beta tester for the new Access 2010 Web Services that enables Access developers to build browser-neutral web applications with Access 2010.

Armen Stein is the president of J Street Technology, a Microsoft Gold Certified Partner with a team of database application developers near Seattle, Washington. J Street also offers CartGenie, a complete e-commerce web storefront and shopping cart. Armen is a Microsoft Access MVP and MCP, has taught college-level database classes, and has also developed and taught one-day training classes on Access and Access/SQL Server development. Armen has spoken at various user groups and conferences, including Microsoft TechEd in the United States and Office DevCon in Australia, and is co-author of Access 2007 VBA Programmer's Reference (Wrox). His other interests include activities with his family, travel, backgammon, Mariners baseball, and driving his 1969 Ford Bronco in the sun.

Thanks to my wife, Lori, and to my kids, Lauren and Jonathan. I also would like to thank my great team at J Street Technology, who capably manage projects so that I can spend time on books like this.

Acknowledgments

AS A TEAM, WE would like to express our respect and appreciation for all of the people who contributed to the editing and production of our book. Every book seems like a new adventure and we are fortunate to have the ongoing guidance from Wiley and Wrox to ensure that they are produced with the best standards. So we send a special thanks to Bob Elliott and Tom Dinse—is this our 5th book together? We'd also like to recognize some of the great behind-the-scenes players, Ashley Zurcher, Helen Russo, Mary Beth Wakefield, and Nancy Rapoport, and many others.

Of course, we wouldn't have anything to write about if it weren't for those incredible people on the Microsoft Access team. Their passion, dedication, and commitment to continuously expand and enhance Access are truly remarkable and inspiring. We see this, not only in the new features, but also in their support of the user community. And speaking of community, the Access community is a large part of what makes Access so special. Whether you are a novice or seasoned developer, if you have a problem, you can easily find assistance, guidance, and even friendship, from a variety of user groups and online forums.

We'll wrap up with a resounding round of appreciation for our five tech editors - and contributing authors. With the incredible new features and dramatic changes in Access, we have relied on their expertise to not only identify potential errors and omissions, but to question our content and writing to help ensure that it is both comprehensive and comprehensible. It is no wonder that they have each been recognized by Microsoft as Access MVPs. It is a pleasure to acknowledge you as an integral part of our team and to the success of this book.

—THE AUTHORS

FIRST AND FOREMOST, I want to thank my wonderful family who taught me to embrace life and to live each day with respect, integrity, and purpose. Thank you for helping me to recognize and celebrate the opportunities that life offers. I can rely on your encouragement and support as I dive into new adventures and projects such as this book.

I also want to thank my colleagues and great friends who continue to fuel my passion for helping others to learn about and use Access. In particular, I want to express my appreciation for the people in my user groups (Michka, Randy, Brandi, et al.) and the amazing people who are Access MVPs and community experts (Larry, Truitt, Arvin, George, our TEs, the list goes on)—friendships that are priceless!

One of my favorite aspects of being an author is the opportunity to involve and recognize contributions from people from around the world. In addition to our remarkable team of authors and editors, we have the benefit of sharing tips from our esteemed colleagues, Dane Miller, Garry Robinson, Steve Schapel, and Larry Strange. I especially want to recognize and thank Ben Clothier for his dedication, humor, and tenacity. Neither of us had any idea of what was to come when Ben became a contributing author. Even working into the wee hours of the morning, we were still having fun as we completed countless iterations of reviews. That epitomizes a great Access developer—passion, commitment, and collaboration. My thanks to everyone—a project of this magnitude is truly a team effort!

—TERESA

THANKS TO EVERYONE ON the Access team at Microsoft for their outstanding work during this release and in particular to the testers on my team for listening to my various ramblings about Access (and food): Nishant Kumar, Kevin Nickel, Michael Tucker, Sanghmitra Gite, Kevin Bell, and Rasika Chakravarthy. Thanks for all of your hard work on this release!

To Geoff Hollander, Jack Stockton, and everyone who's been a part of the Portland Access User Group conference the past few years. Thanks for the invite and for including me as a part of your family!

Last but not least, continued thanks to my wife, Sandi, and children, Isabel and Gillian, for their understanding while I was working evenings and weekends. To my oldest, Taryn, for coming to visit earlier in the year and indulging me with sushi. (Yup, still better on the left coast.)

—ROB

I'D LIKE TO THANK my wife, Jamie. Without all of your love and support, I would be nothing, and I appreciate everything you do for our family. I would like to thank my son, Ryan, and apologize for the time I missed in your life while I wrote this book. You two are the most important people in the world to me! I would like to thank my friends from GBS, Inc.: Andrei, Vladimir, Tianru, Steph, Mia, Richard, Bob, and Jeff. Without you, I would never have gotten my start on Access and the experience was invaluable to me. I would like to thank so many of my friends from the Access team who taught me so much about Access, and to call out a few specifically: Michael, Rob, Tosh, Tim, Shawn, and Sherri. Thanks for the knowledge and opportunities you gave me; the experience was invaluable. And, of course, I would like to thank all of my other friends, colleagues, and teachers that have meant to so much me over the years. You are all Rock Stars!

—GEOFF

I'D LIKE TO THANK my wife, Dianne, a shining light guiding this lost soul into the sunshine. My son, Jeremy and daughter, Amber, no father could ask for better or brighter children to keep him humbled. I'd like to also acknowledge my online “family” at UtterAccess.com; especially Gord, Ricky, and George. You guys are the best. Most of all, I would like to thank Teresa Hennig for giving me the opportunity to work on this project with such a great team (thanks Rob and Geoff for being so patient with the “new kid”).

—JERRY

Introduction

Welcome to Access 2010 Programmer's Reference, your guide to the new features and opportunities that Access 2010 has to offer. With this release, Access has expanded its reach beyond the desktop and network to join the ranks in the cloud. In support of this new level of connectivity, you will find significant changes within the developer environment that require more decisions, different programming tools, and more options. With changes of this magnitude, you will want to leverage community resources to get up-to-speed quickly so that you are working smarter and more efficiently. That's where this book comes in.

What makes this book so special? In addition to the extensive coverage of subject matter, it is the unparalleled team of authors and tech editors who are as devoted to helping fellow developers as they are passionate about Access. Jerry and Teresa have both earned Access MVP status in recognition of their expertise and contributions to the Access community, and Rob and Geoff have worked on the Microsoft Access test team. They are joined by a team of five technical editors who are all Access MVPs; they are not only technical experts but also seasoned consultants and leading-edge developers. Their areas of focus and expertise cover the spectrum from VBA to macros, and from simple solutions to enterprise deployments to web apps, and all areas in between.

Access 2010 builds on many of the new features that were introduced in Access 2007. Features such as templates, application parts, and Office themes will help you leverage existing resources to quickly create and tailor a custom application. To support the expanding use of macros, we have a new macro designer that makes it faster and easier to create and use macros. With their new functionality, you will want to learn how to utilize UI and Data macros in local solutions, not just web applications.

For many of us, being able to take advantage of the new features, right out-of-the-box, is more than enough reason to upgrade. So although the primary focus of this book is to help you extend the power of Access, we will also discuss the new features of Access 2010. Because many of you are familiar with prior versions of Access, we also point out some of the major changes, particularly if they affect the way that you will be working.

The goal is for Access 2010 Programmer's Reference to be your primary resource and tool to help you leverage Access's built-in functionality along with VBA and other tools in a manner that helps you to create solid, robust applications. Access 2010 makes it easy to start working as soon as it's installed. With the new UI (user interface), people will be building complex applications using the tools and resources that ship with Access. And, with a little outside guidance, they can work a lot smarter, with more confidence, and avoid several pitfalls.

This book is for the typical Access user as well as the seasoned programmer. It will help you utilize the power of Microsoft Access more effectively and help you choose when to let the wizards do the work, as well as showing you how to modify and enhance the code that the wizards create. Access builds great forms and reports that can be customized on-the-fly by using VBA code to respond to a multitude of events. Interactive reports, web apps, and new options for distributing applications exemplify the power and potential of Access.

With all the templates, macros, and built-in features, it is easier than ever to open the program and begin creating tables, forms, and reports with confidence. When you consider how easy it is to get started, you'll realize that it is doubly important to be working smart and in the right direction. Use this book and its online resources as your guide to better programming and more effective solutions.

Why Use VBA?

Microsoft Visual Basic for Applications (VBA) enables programmers to develop highly customized desktop applications that integrate with a variety of Microsoft and non-Microsoft programs. For example, all of the Microsoft Office System products support VBA. In addition, many third-party programs, such as accounting software packages, mapping software, and drafting programs also support VBA. Those companies often provide an integration tool, or SDK (software development kit), which typically requires VB or VBA to work with it.

VBA is actually a subset of the Visual Basic programming language and is a superset of VB Script (another member of the Visual Basic family of development tools). VBA includes a robust suite of programming tools based on the Visual Basic development, arguably the world's most popular rapid application development system for desktop solutions. Developers can add code to tailor any VBA-enabled application to their specific business processes. Whether you are starting with a blank database or building on a template, you can build complex custom solutions. For example, a construction company can use VBA within Microsoft Access to develop a sophisticated system covering estimating, ordering, scheduling, costing, and inventory control. The look and operation of the system can be tailored for each group and it can easily limit what data a person can view or change.

You might wonder why you should develop in VBA rather than Visual Basic or .NET, which are also robust, popular, and capable programming languages. Using VBA within Access gives you a couple of key benefits. First, you can leverage the built-in Access object library, taking full advantage of a wide variety of Access commands, including executing any command from the Ribbon or custom toolbar in Access. And second, it's cost effective because VBA is included in all Microsoft Office System applications. Other languages and frameworks, such as .NET, require a separate tool such as Visual Studio and thus a purchase and license. And although they can be cost-effective tools if they are needed, they can also be an unnecessary burden on a project—both in time and money. But do not fear, the book discusses leveraging the power of .NET framework as well.

Access now includes another tool that is often an alternative to VBA—the new Macro Designer—so we have a chapter dedicated to a discussion of how to create and use macros. If you've worked with macros in other Office programs, you will be a step ahead. That is one of the benefits of working with tools that are an intrinsic part of Microsoft Office. If you learn how to program using VBA and macros in Access, your code and skills are transferable to other programs. Even if your primary focus is on Access, knowing VBA will make it much easier to integrate your solution with other applications.

What Does This Book Cover?

Access 2010 Programmer's Reference covers the key topics of greatest importance to Access developers. Although the premise is that most readers will have some familiarity with the VBA programming language, the book includes a brief introduction to VBA and to macros. And to help you leverage the new tools that Access provides, it also has a chapter that highlights the new features in Microsoft Office Access 2010. And, because many people will be faced with upgrading and converting programs from prior versions, a chapter is dedicated to discussing some of the key factors and issues associated with upgrading and running Access in mixed environments.

To help you build a strong foundation on the fundamentals for programming in VBA, the book discusses how to create and name variables, how to use Data Access Object (DAO) and ActiveX Data Object (ADO) to manipulate data both within Access and within other applications, proper error-handling techniques, and advanced functions, such as creating classes and using APIs. Key new areas, such as using macros, customizing the Ribbon, and working with Office Backstage are also explored.

Once the basics are covered, the focus turns to forms and reports, the two most powerful tools for working with and displaying data. Several examples demonstrate how all of the pieces fit together to create an application that not only meets your immediate needs, but is also easy to maintain and enhance.

Working with other applications is covered extensively both in general, and more specifically for Microsoft Office applications, Windows SharePoint Services, Access Services, and SQL Server. Of course, this book wouldn't be complete without discussing security issues. And, to supplement the chapters, it has a full compliment of appendixes that put helpful reference material conveniently at your fingertips.

The Chapters

Chapters 1 provide material that you need if you're new to Access, VBA, or macros. We start with an introduction to Access and then provide a review of Access 2010's new features. To help you make informed decisions about converting and upgrading, we'll discuss some of the major factors, processes, and options. After that, you learn about the 2010 macros, how to use them, and how they compare with using VBA. Chapters 5 and 6 discuss the VBA language, VBA Editor, and the building blocks of VBA, including variables, procedures, events, and properties.

The next two chapters are devoted to building a strong foundation on which you will build strong, versatile applications. Chapter 7 emphasizes the dynamics of an Access application—and the need to control what happens to the data and when. In this chapter, you will learn how do to that with a variety of tools and techniques, including using events, procedures, expressions, and recordsets. In Chapter 8, we delve a bit deeper and examine VBA classes and class objects. You'll learn how to create, name, and use classes and objects.

The next step is working toward program integrations. Chapter 9 shows you how to use application programming interface (API) calls to work with and leverage libraries and objects from other sources, such as using Windows APIs. That leads you to the Win32 Registry. In Chapter 10, you will learn how the Registry is structured and how it works. Then we show you how to write basic VBA code to create, retrieve, edit, and delete Registry values.

Chapters 11 and 12 focus on using VBA to access data. Both DAO and ADO provide methods for accessing and working with data in Microsoft Access and other external data sources, which can be relational, such as Informix, SQL Server, or other RDBMSs (relational database management systems), as well as nonrelational, such as text files, spreadsheets, and XML, and a multitude of other possible sources including numerous accounting programs.

In Chapters 13 to 17, you will begin to use VBA within an Access application—or at least in demonstrations. You start by learning the structure for writing SQL statements, and we explain some critical techniques and nuances, such as the difference between using single and double quotes. As we focus on forms and reports, we'll provide real-world examples and techniques that you can incorporate directly into your solutions. From there, we'll move into more specialized areas, such as customizing the Ribbon and working with the new Office Backstage and manipulating XML for both Ribbon and Office Backstage.

Chapters 18–21 provide information about working with other Office applications, such as Word, Excel, Outlook, and PowerPoint. In addition to learning about the new web applications, you'll also discover some new techniques for deploying database solutions and for sharing and securing data using SharePoint. We'll expand the discussion to focus on programming with .NET to automate Access 2010 and working with Visual Studio. We wrap up with a chapter on client-server development.

The final chapters discuss ways to create and deploy applications and some of the security options. Templates and application parts can provide an invaluable tool for quickly creating a solution. Chapter 22 explains how to find, create, and use templates and application parts. As you may have experienced, not everyone that wants to use your application has a copy of Microsoft Access, but that need not preclude them from using your solution. In Chapter 23, you learn how the Access Runtime enables developers to deploy solutions in a custom package that allows their application to run even though a complete version of Access is not installed on the machine. The last two chapters, 24 and 25, discuss database security and Access security features. You'll review options for locking the VBA project, using ACCDE files, and employing data encryption. With Access security, you'll learn about the trust center, disabled mode and macros, digital certificates, the sandbox mode, and some workarounds.

Although that brings you to the end of the chapters, it is far from the end of the book. There are still nine appendixes filled with reference material.

The Appendixes

As a developer, you can often spend hours going from source to source looking for reference material. The authors of this book have applied the principles of relational databases (doing the work once so it can be used many times in multiple ways) to the appendixes, and have created a compilation of data from a myriad of sources.

The first three appendixes document the object models for Access, DAO and ADO. In Appendix D, you will find a discussion on working with 64-bit Access. As mentioned in the discussions on upgrading in Chapter 3, there are significant issues to consider and specific steps to take if you are migrating to a 64-bit version of Access, particularly if you will be integrating with 32-bit applications.

Appendix E discusses references and is an invaluable resource when you are converting from or to different versions of Access or moving an application from one platform or environment to another.

The next three appendixes, F, G, and H, are about good practices, such as avoiding reserved words and special characters, implementing good naming conventions, and having a way to manage code, such as by using Source Code Control.

Appendix I, is like the extra topping on the cake. We share tips and techniques that will help you design polished and professional applications. We demonstrate the techniques and provide a working application so it can become part of your developer toolbox. In addition to our demo application, we also have tips and files from four of our esteemed colleagues, Dane Miller, UtterAccess VIP and database consultant; Garry Robinson, Access MVP and editor of www.vb123.com/kb; Steve Schapel, Access MVP; and Larry Strange, with www.AccessHosting.com.

How to Use This Book

The initial chapters provide background information to help you build a strong foundation. They are written in a tutorial format with detailed examples. Subsequent chapters delve deeper into technical areas and demonstrate numerous techniques that will help you build robust solutions. Most chapters refer to online files that provide the code snippets and help demonstrate the scenarios. True to the Wrox Programmer's Reference format, the book includes numerous reference appendixes with details on the various object models, as well as guidelines and best practices that will help you to work with Access and to write VBA code.

Real-world examples are given for many, if not most, of the programming topics covered in this book. These are just of few of the topics and examples that are included:

Creating intuitive, user-friendly forms to quickly find and work with data, and minimizing data entry errorsCreating custom reports based on information entered on a formLeveraging report browse, the interactive report feature that enables drilling into data on reportsSummarizing and graphically displaying data using cross-tab reportsUsing VBA to transfer data between Access and other Office programs such as Outlook, Word, and ExcelConfiguring custom ribbons, toolbars, and menus for your Access database applicationsCreating custom web apps and leveraging the features offered through SharePoint and Access Services

Throughout the book, we've also included tips and tricks discovered during the authors' programming experiences.

We recommend that as you go through the book, you also work with the code and sample databases provided in the companion download files on Wrox.com. Being able to run the code, see the effect of minor changes, and experiment on your own will reinforce the learning process. (See the “Source Code” section later in this Introduction for details on downloading the code.) By working with the code and examples, you will begin to take ownership of the concepts and learn how to incorporate them into your work and solutions.

Other Access/VBA Sources

You've heard the saying that there are as many ways to build a solution as there are programmers. And that may only be a slight exaggeration. So, although this book is an excellent reference for all of your Access 2010 programming needs, there just isn't enough time and ink to cover everything—not to mention fixes, updates, and add-ons. That's where networking, newsgroups, and other information sites come in. Here are some of the authors' favorites for you to check out:

Microsoft Answers: Microsoft provides a landing place for users to ask questions about their products, and Access is one of several products that are covered at the forums. To quickly get to the Access questions and answers, start with this URL: http://social.answers.microsoft.com:80/Forums/en-US/addbuz/threads?filter=mf%3a6edab96e-a4cc-4ccb-ae44-7bcdb5ffa589The newsgroupcomp.databases.ms-access: An active community that can be accessed using an NNTP newsreader as well as by using groups.google.com. If you do not have NNTP access, free NNTP servers are available—for example, eternal-september.org.Microsoft's Community website: At (discussion groups) www.microsoft.com/office/community/en-us/default.mspxMVPS.ORG (www.mvps.org/access): Your jumping-off point to a number of interesting offerings provided by a few folks associated with the Microsoft Most Valuable Professional (MVP) program.Microsoft Office Online—Access (http://office.microsoft.com/en-us/FX010857911033.aspx): Provides quick tips, and direct links to Access resources such as downloads, templates, training, add-ins, and other pertinent information.Utter Access (www.utteraccess.com): Currently the leading independent forum for Microsoft Access questions and solutions.The Access developer center: Programming resources through Microsoft (http://msdn.microsoft.com/en-us/office/aa905400.aspx).vb123 Knowledge Base: A compilation of Access tips, tools, and articles. Hosted by Garry Robinson, Access MVP (www.vb123.com/kb).

Conventions

Several different styles of text are used in this book to help you understand different types of information. Some of those styles are illustrated here:

We highlight with italics new terms and important words when we introduce them.We show keyboard strokes like this: Ctrl+A.We present notes, warnings, and sidebar information in shaded boxes:

This is a note. It contains additional information about the subject under discussion.

This is a warning. It provides information about how to avoid problems related to the subject under discussion.

This Is a Sidebar

It contains asides that provide information related to the subject under discussion.

We show filenames, URLs, and code within the text like so: sample.doc, persistence.properties, and www.microsoft.com.We present code in two different ways:

We use a monofont type with no highlighting for most code examples.

We use bold to emphasize code that is particularly important in the present context or to show changes from a previous code snippet.

Source Code

As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All the source code used in this book is available for download at www.wrox.com. When at the site, simply locate the book's title (use the Search box or one of the title lists) and click the Download Code link on the book's detail page to obtain all the source code for the book. Code that is included on the website is highlighted by the following icon:

Listings include the filename in the title. If it is just a code snippet, you'll find the filename in a code note such as this:

code snippet filename

Because many books have similar titles, you may find it easiest to search by ISBN; this book's ISBN is 978-0-470-59166-6.

Once you download the code, just decompress it with your favorite compression tool. Alternately, you can go to the main Wrox code download page at www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

Errata

We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, like a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata, you may save another reader hours of frustration, and at the same time, you will be helping us provide even higher quality information.

To find the errata page for this book, go to www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page, you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list, including links to each book's errata, is also available at www.wrox.com/misc-pages/booklist.shtml.

If you don't spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We'll check the information and, if appropriate, post a message to the book's errata page and fix the problem in subsequent editions of the book.

p2p.wrox.com

For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a Web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At http://p2p.wrox.com, you will find a number of different forums that will help you, not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:

1. Go to p2p.wrox.com and click the Register link.

2. Read the terms of use and click Agree.

3. Complete the required information to join, as well as any optional information you wish to provide, and click Submit.

4. You will receive an e-mail with information describing how to verify your account and complete the joining process.

You can read messages in the forums without joining P2P, but in order to post your own messages, you must join.

Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the Web. If you would like to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works, as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.

1

Introduction to Microsoft Access 2010

What's In This Chapter?

A short history of the Access productA discussion of when to use AccessAn introduction to Access 2010 and its main featuresA discussion of how to create each of the Access object types using the Access designers

Microsoft Office Access 2010 is the latest version of Microsoft Access, the world-class relational database management system (RDBMS) for the Microsoft Windows platform, designed for building small- to medium-scale database applications. Access 2010 provides a rich set of features and tools for designing, creating, storing, analyzing, and viewing data, as well as the capability to connect to a large variety of other data sources. Access combines ease-of-use features with software development capabilities to support a wide range of user skill sets. Access also provides a Primary Interop Assembly (PIA) to allow other development platforms, such as Microsoft Visual Studio .NET 2010, to manage data using an Access database or even incorporate Access functionality into an external application. Access 2010, simply put, is a database system that provides a wide variety of functionality and flexibility to build Windows-based applications.