Expert Access™ 2007 Programming

Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com

Copyright © 2008 by Wiley Publishing, Inc., Indianapolis, Indiana

ISBN: 978-0-470-17402-9

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

Library of Congress Cataloging-in-Publication Data is available from the publisher.

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, Wrox 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.

To my family, Sandi, Isabel, Gillian, and Taryn for their support and patience over the years.

— Rob Cooper

To my family, and to everyone who shares my love for data.

— Michael Tucker

About the Authors

Rob Cooper is a Test Lead on the Access team 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 2007 release, he led the security efforts across the test team and worked on several new features including disabled mode, database encryption, Office Trust Center, and sorting, grouping, and totals. Rob also led efforts around the Access object model and continues to provide direction around programmability and security in Access.

A long-time fan of Access, Rob is a frequent speaker at the Seattle Access Group and PNWADG meetings and has written for the Microsoft Knowledge Base and Access Advisor. Aside from writing code in Access and C#, he also enjoys spending time with his family watching movies, going to the zoo and aquarium, and hanging out in and around Seattle.

Michael Tucker is a Software Design Engineer on the Microsoft Access test team in Redmond. He joined Microsoft in 1993 as a Product Support Engineer supporting the very first release of Microsoft Access, and has been passionate about Access ever since. During the Access 2007 release, he worked on the new ACE database engine, and owned the complex data and SharePoint workflow integration features.

Michael designed and coordinated implementation of the test automation infrastructure used by the test team. He also has previous experience as a Test Lead and Program Manager on a variety of products at Microsoft. His specialties include SQL, data normalization, and object model design, and anything related to his passions for airplanes, airports, and traveling the world.

Credits

Executive Editor

Robert Elliott

Development Editor

Ed Connor

Technical Editors

Oliver Stohr and Matt Bumgarner

Production Editor

Daniel Scribner

Copy Editor

Nancy Rapoport

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

Project Coordinator, Cover

Lynsey Osborn

Compositor

Craig Woods, Happenstance Type-O-Rama

Proofreader

Sossity Smith

Indexer

Ron Strauss

Anniversary Logo Design

Richard Pacifico

Acknowledgments

The authors would like to thank the following individuals who really made this possible. First of all to Teresa Hennig for helping us get set up on this project. By giving us the opportunity to work on the last book, you helped lead to this one. To our incredible tech editors, Oli Stohr and Matt Bumgarner. Your insight and suggestions really make this book what it is and we are grateful for your hard work and keen eyes! To Kevin Nickel on the Access test team who gave us some great suggestions and considerations as we were beginning to flush out the content.

We’d also like to thank everyone at Wiley who helped bring this together, in particular Bob Elliott for getting us started and guiding us through the process. Special appreciation goes out to our development editor Ed Connor who helped us make sure that the content and tone was accurate and for keeping us on track.

Lastly, but certainly not least, to the Access team at Microsoft. The incredible amount of passion, hard work, and dedication that went into the Access 2007 release from everyone involved is nothing short of amazing.

Rob Cooper

I’d like to thank my children, Isabel and Gillian, who were even more patient and more understanding than they were for the last book—you continue to inspire me! Thanks to my wife, Sandi, for her support during all of the late nights and weekends. I’m free to do yard work again!

Thanks to my co-author Michael for keeping me in check and helping me make sure that we covered things that we felt should be covered. Thanks in particular for your great suggestions and for setting the tone for the SQL and Data chapters!

Extra thanks to Bob Elliott at Wiley for his insight and wisdom as I was taking on this role. Your patience, understanding and willingness to help were greatly appreciated.

Huge thanks to everyone on the Access product team at Microsoft — without you and your hard work on a great release of Access 2007, this wouldn’t have been possible! To Sherri Duran for her continued support while I worked this project.

Lastly but certainly not least, thanks to the Access community, particularly the amazing people at utteraccess.com. It’s truly an honor to be a member of a community of people who are as passionate about Access as we are, and to the Seattle Access Group and Pacific Northwest Access Developer’s Group for allowing me to come listen and speak from time to time.

Michael Tucker

To my family, whom I don’t see often enough, but who are never out of my thoughts: Mom and Dad, Mike and Margie, Shari, Kimberlee, Heidi, Danna, Holly, Kathlee, Rayne and Ann. Monica and Jason and their little Archer, Scott, Kittridge, and Markus: thank you for your friendship, constant kind words of encouragement, thought-provoking conversations, and nudges.

My co-author and colleague Rob Cooper has been an inspiration ever since I returned home to the Access team. His knowledge of Access is encyclopedic, and his enthusiasm and passion are infectious. I am honored and privileged to have been invited to join him on this project.

To all of the amazing professionals on the Microsoft Access product team who daily put so much energy into making an amazing product even more so, thank you for your inspiration. The most satisfying thing about working at Microsoft is being surrounded by so many amazing minds from around the world, who bring a myriad of talents and varied perspectives. To Mike Garlick and Sherri Duran who welcomed me home, I will always be in your debt. To Anderson Dupree, who was my first Best Manager; Steve Alboucq who opened doors to the product team; and Mark Langley who mentored me into management, my gratitude for your support and guidance.

Finally, my appreciation to all the Access users I’ve had the privilege to work with over the last 14 years. You never cease to amaze me with the unusual, often creative, and occasionally inspirational ways you use our product. I remember fondly a small and select group of particularly memorable customers who made my time in product support especially interesting: my psychic friends in Florida, a certain banker in San Diego who taught me the value of scaled integers, the OSHA man in Portland who shares my passion for aviation, and the lovely lady from AT&T in Chicago who proved once and for all that everything is relative.

Introduction

The largest release of Access in five years, Access 2007 includes many new features that you can leverage both in your own development work and as application features that you can pass along to your users. This book discusses new features such as developing for the Ribbon and the Attachment control, as well as new ways of looking at old problems such as automation, debugging, and deployment. It aims to fill the gaps between casual Access development and professional Access development. We wrote this book as a tribute to the product that we’ve come to use every day. It represents a labor of love for the years that we have spent working with Access both at Microsoft and prior to joining Microsoft.

Building Off-the-Shelf Applications

Throughout this book, we discuss several topics that contribute to off-the-shelf quality. Off-the-shelf is a term used to describe software that is typically available for release to the public. This doesn’t necessarily mean that the software is available commercially (although it could). Even departmental or larger scale internal applications can benefit from features such as configuration (discussed in Chapter 12), and deployment (discussed in Chapter 14). We live in a global marketplace. If you are developing databases for use by people in locales other than your own, you might be particularly interested in Chapter 12, where we will also discuss localization.

Forms are essential to most Access applications. Forms are used for everything from displaying and validating data, to splash screens, to About dialog boxes. You will learn about creating different types of forms such as these in Chapter 8. It doesn’t stop there, however. You will also learn about how to create custom navigation solutions for forms to provide intuitive experiences for your users. In many solutions, seeing an error message from Access is less than desirable so you will also learn how to suppress Access error messages to provide your own. We also discuss using Form events to validate data; detect when records are added, updated, or deleted; and much, much more.

But let’s not forget about reports! Reports are also an integral feature of Access solutions. They provide another mechanism for presenting information to the user and are optimized for printing. Access 2007 includes a new view for reports called Report view, which allows users to interact with reports as never before. You will learn how to use Report view to create compelling scenarios for your users such as sorting and filtering. You no longer need to use a separate form to sort or filter reports. Features such as this are discussed in Chapter 9.

A Little Background

We both started our careers at Microsoft in what was then known as Product Support Services (PSS) as members of the Access support team. Learning Access from the support perspective was extremely valuable. For starters, calls didn’t come in because Access was working just fine! People tend to call when something is broken or they have questions about how to implement a piece of functionality. This breadth of knowledge led to developing common methods for working with a particular task. Upon making the transition to testing Access, we learned development practices that were in place on a large software project. It is largely these practices that we implement in our Access development projects.

Whom This Book Is For

This book is primarily geared toward intermediate to advanced Access programmers using VBA and SQL. Some of the concepts apply to VBA as a language and not to one application in particular. As a result, VBA programmers using other tools such as Excel or Word may also find some of the topics useful. Familiarity with Access 2007 is helpful but not required. New features mentioned in Access 2007 are called out as such and explained throughout. You’ll find that several of the concepts apply to previous versions of Access as well.

This book contains some examples written in managed code using Visual C#. A basic familiarity with this language will be helpful for those examples. The code available for download with this book includes managed code samples in both Visual C# and Visual Basic .NET.

Some experience with XML will be useful for Chapter 11.

What This Book Covers

Expert Access 2007 Programming aims to show you how to create off-the-shelf quality applications using Access that are fun to write, feature-rich, and easy to use. Several of the techniques covered in this book are used to develop Access itself at Microsoft such as using assertions, build numbers, and classes. Some techniques such as handling exceptions are also closely tied to the work we do with managed code. We have adapted these techniques to apply to the process of writing Access-based applications, and these are techniques we implement in our own solutions. Employing these techniques, even in Access-based solutions, can simplify the development process and provide consistency across multiple applications.

How This Book Is Structured

This book is structured in four parts with either three or four chapters in each part. This book is meant to walk you through the important steps in creating a full-featured application. As such, it is designed to be read sequentially from beginning to end.

Part I: Programming Access Applications

The chapters in Part I are designed to provide the foundations of programming Access applications. Chapters 2 through 4 apply to VBA developers, regardless of the application.

Chapter 1, “Overview of Programming for Access,” describes in broad strokes what it means to write applications for Access. Often, this means writing VBA code in a particular database, but there may also be times when you are required to write applications that target Access, for example, using managed code. This chapter discusses some of the basics of working with managed code, in particular C# and VB.NET.

Chapter 2, “Extending Applications Using the Windows API,” provides information about using API functions that are defined in Windows, and then provides many useful examples of API functions that you can use in your Access applications.

Chapter 3, “Programming Class Modules,” gives you in-depth information about writing class modules for use in your applications. The chapter begins with an overview of writing class modules in Access, and then jumps right in to using them to extend, and even simplify, programming tasks. The chapter ends with defining events that you can use as notification when particular conditions are met.

Chapter 4, “Debugging, Error Handling, and Coding Practices,” provides a detailed look at debugging VBA applications and extending debugging to make it work for you.

Part II: Data Manipulation

With a new version of the Access database engine (formerly known as Jet) and built-in support for other database engines, it’s an understatement to say that Access applications are well suited for data. In Part II, we move into the nitty-gritty of Access development — data. The chapters in Part II describe working with data in many different forms.

Chapter 5, “Parsing Text and HTML,” starts out with an in-depth look at working with data in different types of files using VBA I/O statements and the FileSystemObject. Along the way, it shows you how to determine the byte order mark of a file to determine whether a text file is ANSI or Unicode. The chapter then discusses working with strings and how to parse data into various data types. It ends with a look at generating formatted HTML.

Chapter 6, “Using SQL to Retrieve Data and Manipulate Objects,” shows you how to use SQL to do just about anything. It begins with a discussion about places in Access where SQL can be used and the different types of queries that can be created. It then goes into the details of the SELECT statement. It also shows you how to work with other query types such as crosstab and action queries, and ends with data-definition and SQL pass-through queries.

Chapter 7, “Managing Data,” shows you how to find data in a database, as well as how to categorize data and create a custom sort field. Creating a backup of data in the database is an important task and the chapter describes a mechanism for archiving and backing up records in the database. This chapter ends with examples that target new features in Access 2007: append-only memo fields and attachment fields.

Part III: Interacting with the Application

Once data is stored in the database, your users will need a way to view the data and interact with it. This is where Part III comes in. The chapters in Part III are designed to make interfaces and functionality that pop.

Chapter 8, “Using Code to Add Advanced Functionality to Forms,” shows you how to create reusable forms for your applications such as dynamic menu screens and dashboards, as well as splash screens and About dialog boxes. As events are critical of any form-driven application in Access, there is an in-depth look at working with form and control events.

Chapter 9, “Using Code to Add Advanced Functionality to Reports,” begins with interactive scenarios that use an exciting new feature of Access 2007 — Report view. It then provides some other examples such as creating a calendar report and data bars à la Excel 2007, and ends with a full-featured report manager.

Chapter 10, “Using Automation to Add Functionality,” provides samples that can be used to extend an Access application outside the boundaries of Access. It starts out with the basics of automation such as the GetObject and CreateObject functions, and then gives you some scenarios for automating Windows — yes, Windows. Because Access is a part of the overall Office family of applications, the chapter provides code for interacting with Word, Excel, Outlook, and Project. Web applications and social applications such as instant messaging are becoming more and more popular. To address this trend, the chapter ends with examples for automating Internet Explorer and Windows Live Messenger, formerly MSN Messenger.

Chapter 11, “Creating Dynamic Ribbon Customizations,” goes into the fine points of Ribbon development. It begins with some development tips that we found useful and then jumps right in to the details of writing callback routines and XML for the Ribbon. The chapter also shows you how to use images in your customizations and then contains some examples of moving typical Access functionality such as the NotInList event of a combo box into the Ribbon. The chapter is aimed primarily at Access developers, but because the Ribbon can be used by other applications in Office, VBA developers in Word, Excel, and PowerPoint may also find this useful.

Part IV: Finalizing the Application

Okay, so you’ve developed the core functionality in your application and tested it. It’s time to start wrapping it up. Part IV covers some of the essentials for finishing an Access-based application.

Chapter 12, “Configuration and Extensibility,” shows you how to create localized applications and detect locale information on the computer. It then creates a framework for extending an application using options and, last, shows you how to theme your Access applications.

Chapter 13, “Personalization and Security,” describes the new security features in Access 2007. It discusses how you can create personalized solutions for your users using the navigation pane, and how to add Windows integration to an application. Chapter 13 ends with some best practices related to security and personalization.

Chapter 14, “Deployment,” shows you how to create builds for your applications and includes a managed code tool to accomplish this. This chapter also discusses including dependencies for your applications and how to update them. It ends with a discussion about licensing and registering an Access application.

Chapter 15, “Help and Documentation,” shows you how to add help to your applications. It describes some built-in approaches such as status bar text and tooltips, and then discusses context-sensitive help. Web-based help is also popular for many applications and the chapter shows you how to create that. The chapter ends with the details of creating a compiled HTML help file.

What You Need to Use This Book

You must have a computer that meets the minimum system requirements for Microsoft Office 2007. The minimum requirements for Office 2007, according to Microsoft, are a 500 MHz computer or higher, 256MB of RAM, and 2GB of hard-drive space.

To create the managed code examples, you will need a version of Visual Studio that targets the Microsoft .NET Framework 2.0. This includes Visual Studio 2005 Standard Edition or higher or either the Visual Basic .NET 2005 Express Edition or Visual C# 2005 Express Edition. Creating the resource DLL described in Chapter 12 requires Visual Studio 2005 Standard Edition or higher, or the Visual C++ 2005 Express Edition.

The minimum requirements for Visual Studio 2005 Standard Edition are a 600 MHz computer or higher, 192MB of RAM, and 2GB of hard-drive space.

Conventions

To help you get the most from the text and keep track of what’s happening, we’ve used a number of conventions throughout the book.

Source

This section includes the source code.

Source code

Source code

Source code

Output

This section lists the output:

Example output

Example output

Example output

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 of the source code used in this book is available for download at www.wrox.com. Once at the site, simply locate the book’s title (either by using the Search box or by using 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.

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

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.

Part I

Programming Access Applications

Chapter 1: Overview of Programming for Access

Chapter 2: Extending Applications Using the Windows API

Chapter 3: Programming Class Modules

Chapter 4: Debugging Error Handling and Coding Practices

Chapter 1

Overview of Programming for Access

In this chapter, you take a look at the different mechanisms available for programming for Access and what it means to program Access applications. You will see that with the ability to use the Access object model outside of Access, programming for Access is not necessarily the same as programming in Access. And, while the possibility is there to develop external applications that consume the Access object model, the primary focus of the book is scenarios that use the Access object model and Visual Basic for Applications (VBA) from within Access itself.

In this chapter, you:

Learn about using managed code and how it can be used to work with Access applications

Review object models that are often used with Access

Learn about off-the-shelf applications in the context of Access development and what it means to develop off-the-shelf applications

Writing Code for Access

As a part of the overall Microsoft Office family of products, Access finds itself in an interesting position. It provides many tools that Access developers and programmers at all levels use to create robust database applications for themselves or their users. Access 2007 includes a database engine for storage, forms and reports for presenting data, macro objects for automating simple tasks, and a full-featured programming model using VBA. Collectively, these components make it possible for you, as the programmer or developer, to create rich solutions that can be easily deployed to your users.

As fun as it is to write Access applications, Access doesn’t provide everything. For example, it doesn’t provide an easy way to create e-mail messages with an arbitrary attachment such as Outlook. It doesn’t provide statistical functions, such as Excel, and it doesn’t provide word processing functionality, such as Word. It can, however, interact with these applications, as a part of the overall Office family, using each application’s respective object model to participate in the larger solution. Chapter 10 provides insight into how Access fits into the larger picture when creating a solution.

In many cases, writing code for Access is different from writing code for other Office applications, such as Word and Excel. Many Access solutions are designed specifically to work with multiple Office applications. In our day-to-day work, we use applications that we have written to enable us to send custom e-mail messages using Outlook or to create a Word document with very specific formatting. In addition, when you develop a database in Access, often you are developing a full-featured application for use by multiple users.

The Access Object Model

The code you write for Access forms, reports, and controls targets the Access object model, but to retrieve data you need to use a data access technology such as Data Access Objects (DAO), or ActiveX Data Objects (ADO). The requirement for using two object models creates an interesting dichotomy when you’re writing applications for Access, which also sets Access apart from writing code for other Office applications.

The result of this separation makes it possible to write code for Access applications that can easily be reused between applications. You might be thinking, “Can’t I write modular code for Excel-based applications as well?” Of course. But we think you’re more likely to do so when writing an Access-based application. From the pure coding perspective, we are not suggesting that you should always separate presentation code from data access code (in different files), but rather that it’s possible. However, if you think about it, we do frequently separate the presentation layer from the data access layer when we create an application with linked tables.

The DAO Object Model

Data Access Objects, or DAO, has long been used as the native data access technology for Access. Originally included in the Jet database engine with previous versions of Access, new features appear in DAO for use with Access 2007. You learn more about these new features, and other features of DAO in Chapter 7.

The ADO Object Model

ActiveX Data Objects, or ADO, is another data access technology available to use with Access. Both technologies are acceptable and can be used in conjunction with one another, although it’s probably not necessary to do so. DAO, as the native application programming interface (API) for the Access database engine, has performance benefits over ADO. That said, ADO is more generic and thus has its own benefits. For example, ADO provides the ability to create Recordset objects that are not bound to a table or query, but rather are created at runtime by appending fields. In addition, you can use an ADO recordset as the data source for a form, combo box, or list box.

Object-Oriented Thinking

Object-oriented programming (OOP) organizes programming tasks into classes. A class is a blueprint of the thing being modeled. Often, this is something in the real world such as a customer, or an employee. Other times, it may be something more abstract such as a log file, or a dialog box. Classes are said to be self-describing because they describe the characteristics of an entity, and its behaviors — that is, what it can do. The characteristics of a class are known as properties, and the behaviors of a class are known as methods.

A class is different from an object. An object is a unique instance of a class. Over the years, language changes to Visual Basic, and subsequently VBA, have provided some of the OOP features long used by C++ programmers to VB and VBA developers.

Making the decision to use classes often represents a different way of thinking from traditional procedural programming. Classes play an important role in some of the concepts discussed throughout the book. As a result, we discuss programming classes and class design in greater detail in Chapter 3. Classes form the basis of several other pieces of functionality that follow in this book.

Windows API Programming

For those tasks that VBA or the Access object model does not provide, you can use the Windows API. An API, or application programming interface, is a set of functions grouped together by technology. The Windows API contains the core set of functions for Windows itself, but there are other APIs as well. For example, the DAO API consists of the objects, properties, and methods that make up DAO. The API is discussed in depth in Chapter 2.

Working with Managed Code

At the beginning of this chapter, we mentioned managed code as an alternative for working with the Access object model. Managed code refers to code whose memory is managed by the Common Language Runtime (CLR) of the .NET Framework. This often refers to code written in Visual C#, or Visual Basic .NET, although this is not a requirement. For example, Visual C++ developers can use C++/CLI available with Visual Studio 2005 to write managed code.

The focus of this book is writing solutions that utilize Access for its strengths and for building high-quality applications that are based on Access. Sometimes, this means writing code using other object models such as Outlook, or Excel, and integrating them as part of an overall solution. Other times, it means writing code in a different language altogether. Several chapters in this book provide examples using C# to either drive the Access object model, or as a library that you call from VBA code inside Access. The managed code samples that are available for download with this book include both C# and VB.NET.

A great amount of material has been written about C# ranging from syntax to constructs to design. Therefore, we won’t spend much time explaining the language. We don’t expect you to be a C# whiz, but if you are that’s great! If not, no worries. We explain the code as we go so the managed code solutions provided are straightforward and understandable. Although the managed code in this book is written using C#, we have provided the VB.NET equivalents on the corresponding Web site for this book.

Because we use managed code in this book, in addition to VBA, let’s spend some time discussing managed code in a little more detail.

What Is Managed Code?

As mentioned, managed code refers to any code that is written where memory is managed by the CLR. For the purposes of this book, we write managed code in C# — although you could just as well write in VB.NET. We chose C# because we use it in our daily work in testing Access and find it to be a nice language for many programming tasks.

The portion of the CLR that manages memory is known as the garbage collector. The garbage collector is responsible for detecting when objects are no longer needed, and for disposing of them appropriately. If you have written code in other languages such as C or C++, you may quickly recognize this as a powerful feature, although many C and C++ developers may prefer to manage memory themselves. If you fall into this camp, the garbage collector in the CLR provides features for tighter control when objects are cleaned up. Although VBA does not have built-in garbage collection, you are, in effect, managing your own memory in VBA when you set an object to Nothing such as:

Set objMyObject = Nothing

The opposite of managed code of course is unmanaged code. Unmanaged code is code whose memory is not managed by a runtime such as the .NET Runtime. This includes code written in C, C++, Visual Basic, or even VBA.

For more information about managed code, please refer to Appendix C of the Access 2007 VBA Programmer’s Reference, ISBN 978-047004703.

Versions of Visual Studio

Managed code and the .NET Runtime were first available with Visual Studio 2002, which included version 1.0 of the .NET Framework. Version 1.1 of the .NET Framework was released with Visual Studio 1.1. The most recent release of Visual Studio, Visual Studio 2005 includes version 2.0 of the .NET Framework. For users running Windows XP or Windows 2003 Server, you can download either version of the .NET Framework from the Microsoft Web site. If you are running Windows Vista, version 3.0 of the .NET Framework is now included with the operating system and offers new libraries that further enhance managed code development.

You can even use one of the Visual Studio Express Editions to write managed code using either VB.NET or C#. However, be cautious — the Express Editions of the languages do not include the tools required to create a type library for use with Access and so you have to use a command-line tool that is included with Express Edition. We talk more about this tool in the next section.

Writing Managed Code Libraries to Use with Access

In addition to features such as garbage collection, the .NET Framework provides many libraries that you can use in managed code. Collectively, these libraries are known as the Base Class Library (BCL), and include functionality that is typically found in the Windows API. Because these libraries are available to you when writing managed code, they are nice to use in a type library that you call from VBA code in Access.

A library you create in a managed language such as C# creates a dynamic link library (DLL) file. And, while you can create references to DLL files from Access and VBA, you cannot reference a managed DLL directly. In order to set a reference to a DLL from VBA, it must expose the necessary COM interfaces that are used to provide type information about a class. Managed DLL files do not include these interfaces. Therefore, in order to create a reference to a managed code library, you must first create a type library for the DLL. You can use Visual Studio to create the type library or a command-line tool called tlbexp.exe. This tool is available as part of the Visual Studio SDK, which is freely available on the Microsoft Web site.

So, you’re probably thinking, why would we include an additional reference in the application when we can just use the Windows API? Good question. Managed code makes it pretty straight forward to write complex libraries that would have required a fair amount of API code to implement in VBA. Examples of this include:

Using Windows common dialog boxes

Working with the Windows Registry

Writing to the Windows event log

Retrieving the version number of a file

Writing managed code libraries to use in an Access solution has its benefits — namely, it’s easier to write and deploy. However, it is not without its drawbacks. Installing a library written in any language requires an additional file as a part of the installation. However, we feel that the benefits of using managed code (when necessary) as part of a solution outweighs the drawbacks.

Referencing the Access Object Model from Managed Code

In addition to creating managed code libraries that you can call from VBA, you can write managed code that drives the Access object model itself. For example, you might write a report manager solution that bundles the reports in an Access application together to print multiple reports based on a timer. Such an application could use the Access object model to get a list of reports in the database and to print them.

You can add two types of references to a managed application — .NET and COM. Because Access is a COM-based application, the reference to the Access object model is a COM reference. If you add a reference to the Microsoft Access 12.0 object library in Visual Studio, you’ll notice that a few additional references are given to you. These additional references are ADODB, DAO, Microsoft.Office.Core, and VBIDE. These references are all used somewhere in the Access object model and as a result are included automatically when you add the Access object model from your managed code.

In Chapter 14, you see how to create a managed application using C# to create a build of an Access application including features such as version and build numbers and release dates.

Referencing Other Applications

In order to use object models provided by applications such as Outlook or Project, you typically add a reference. However, adding references creates dependencies in an application that may not be desired. For instance, what happens if you add a reference to the Outlook 12.0 object library but your users are using Outlook 2002 (10.0)?

Issues such as these can be avoided using a technique known as late-binding. Late-binding enables you to write code without providing type information about an object. And while it also enables you to trap for error conditions at runtime instead of compile time, it tends to lead to code that is slower to execute. This lag is negligible on today’s fast processors with a decent amount of memory. The opposite of late-binding is early-binding. Early-binding provides benefits such as compile-time checking and performance improvements. This sounds pretty nice, but it causes problems if your users do not have the same applications installed or the same version. For these times, late-binding becomes very useful.

Discovering References

The easiest way to find a reference to use in your Access application is to view the References dialog box from the Visual Basic Editor, as shown in Figure 1-1.

Figure 1-1

You can add references to COM objects that reside in DLL, EXE, or OCX files, or even to other databases that you create in Access. By adding references to other databases you can create reusable libraries of your own written in VBA. We discuss using references to databases in more detail in Chapter 3, and the issue of updating references in Chapter 14.

If you’ve opened the References dialog box, shown in Figure 1-1, and scrolled through the list, you’ll quickly notice that there are a lot of files listed. Because there are probably more files listed on your development machine than those of your users, can you imagine the headache of trying to manage multiple references in an application? With all of these references to choose from, how do you know what you can or should use?

Adding References to Office Applications

The applications in the Office family of products are designed to work well together. As such, it’s very common to find Access applications that include references to Office, Outlook, Excel, and the like. You can add a reference to other Office applications in the References dialog box. Adding a reference uses early-binding to an external application.

To use late-binding to another application, you must know its programmatic identifier, or ProgID. The ProgID is a string that identifies an application for use by another application. The following table provides the ProgID values for Office applications:

Application | ProgID
Access | Access.Application
Excel | Excel.Application
InfoPath | InfoPath.Application
OneNote | OneNote.Application
Outlook | Outlook.Application
PowerPoint | PowerPoint.Application
Project | Project.Application
Publisher | Publisher.Application
Visio | Visio.Application
Word | Word.Application

Summary

In this chapter, we provided some thoughts about what it means to write an Access-based solution. We examined how writing applications for Access is quite different from writing VBA code in other Office applications. Because Access is used to develop applications, sometimes off-the-shelf, it is often viewed as a development tool when compared to other applications in Office such as Word and Excel.

To support this view of Access as a development tool, we discussed the following:

Object models commonly used while writing Access applications

Programming tools and techniques such as the Windows API and object-oriented programming

Using managed code that targets the Access object model, as well as using managed code as a library inside of an Access application

You also saw a glimpse into what lies ahead in the rest of this book. Features that are often found in commercial applications such as configuration, deployment, and help also have their place in Access applications. More important, you can standardize them across many applications. Over the course of the book we introduce code that you can use to integrate features such as these into your own applications.

Coming up next, you learn how to use the Windows API and why it can be useful in your applications.

Chapter 2

Extending Applications Using the Windows API

VBA simplifies certain tasks, such as reading data from files, asking the user for information, or even launching an external application. As a programming language, it also does a good job at preventing you from shooting yourself in the foot. As a result, a fair amount of functionality to developers of other languages, such as C++ or C#, is not available natively. Therefore, you utilize the Windows API to achieve some of this same functionality.

In this chapter, you learn how you can leverage the Windows API in your applications. More specifically, you learn the following:

How to translate an API function declaration written in C/C++ to VBA

How to use the API to retrieve information about the system

How to control processes and threads, and work with the Windows shell

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!