Access Cookbook

Preface

What This Book Is About

This is an idea book. It’s a compendium of solutions and suggestions devoted to making your work with Microsoft Access more productive. If you’re using Access and you aspire to create database applications that are more than wizard-created clones of every other database application, this is the book for you.

If, on the other hand, you’re looking for a book that shows you how to create a form, or how to write your first Visual Basic for Applications (VBA) function, or how to use the Crosstab Query Wizard, this may not be the book you need. For those kinds of things, we recommend one of the many Access books geared toward the first-time user.

Promotes Creative Use of the Product

Rather than rehashing the manuals, Access Cookbook offers you solutions to problems you may have already encountered, have yet to encounter, or perhaps have never even considered. Some of the issues discussed in this book are in direct response to questions posted in the Microsoft Access newsgroups online (at news://msnews.microsoft.com); others are problems we’ve encountered while developing our own applications. In any case, our goal is to show you how to push the edges of the product, making it do things you might not even have thought possible.

For example, you’ll learn how to create a query that joins tables based on some condition besides equality, how to size a form’s controls to match the form’s size, how to store and retrieve the locations and sizes of forms from session to session, and how to create a page-range indicator on every report page. You’ll see how to use some of the common Windows dialogs from your Access application, how to internationalize your messages, how to really control your printer, and how to store the username and date last edited for each row. There are tips for securing your database, filling list boxes a number of different ways, and optimizing your applications. You’ll find details on using Access in multiuser environments, creating transaction logs, adjusting database options depending on who’s logged in, and programmatically keeping track of users and groups. There are instructions for using the Windows API to restrict mouse movement to a specific area of a form, exiting Windows under program control, and checking the status of and shutting down another Windows application. You’ll see how, using COM and Automation, you can use Access together with other applications such as Word, Excel, PowerPoint, and Outlook. If you’ve never tried data access pages (DAPs), you’re in for a treat—this feature makes it simple for you to display and edit data using a web browser. You’ll learn enough to really get you started with this exciting technology, and you’ll learn solutions to common but tricky problems. Finally, you’ll find tips and techniques for using Access and SQL Server together, taking advantage of Access Data Projects (ADPs).

You won’t, however, find that this book pushes you into using new technology just because it’s new. Many of the techniques in this book are “old chestnuts” that have been helping Access developers solve common problems for years. The VBA code that performs data manipulation mostly uses the older technology, DAO, rather than the newer ADO, because DAO is still the most efficient way to work with Access data. When ADO provides a better approach to solving a particular problem, you’ll learn how to use it for that purpose. For example, you’ll learn how you can use an ADO recordset to retrieve a list of all the users logged on to your application—something that was never possible with DAO.

Uses the Tools at Hand

This book focuses on using the right tool for each problem. Some solutions here require no programming, while others require a little (or a lot) of VBA coding. Sometimes even plain VBA code isn’t sufficient, and we need to use the Windows API or other available code libraries. In each case, we’ve tried to make the implementation of the technique as simple, yet generic, as possible.

We did, however, decide to focus the data access features of this book squarely on using the Jet database engine (instead of SQL Server). There are several reasons why we made this choice, but first and foremost is that most Access developers still prefer the convenience and simplicity of using Jet. The Jet database engine remains a cost-effective and capable solution for database applications used by small workgroups, which make up the vast majority of the Access user base. Most of the techniques described in this book, however, will work just as effectively with data from SQL Server or from other ODBC data sources, even if the example uses a Jet database. And if you are working with non-Access data sources, you’ll find plenty of tips focused on helping you do so more efficiently.

Question-and-Answer Format

The structure of this book is simple. The chapters are arranged by categories: queries, forms, reports, application design, printing, data manipulation, VBA, optimization, user interface, multiuser, Windows API, Automation, DAPs, and SQL Server applications. Each section consists of a single question and its solution, followed by a discussion. Each solution contains a sample database (e.g., 01-01.MDB) with complete construction details, indicating which modules you’ll need to import, what controls you’ll need to create, and what events you’ll need to handle. All the code, bitmaps, sample data, and necessary tools are included with the CD-ROM that accompanies this book. (CD content is available online at http://examples.oreilly.com/accesscook.)