INTRODUCTION

A LETTER FROM THE AUTHOR

Dear Reader,

Another version of Access is here again, and along with it, this book. This is the third version I have worked on, and I am as excited as ever.

Through many new features in Access 2000, Microsoft is again expanding the horizon of what we can do with Access. One of these features is really closing the gap the between using Jet for the back end and SQL Server. Microsoft even included SQL Server in the box this time! Other new features include very cool enhancements in VBA and Data Pages, which blend even more the world of desktop and the Internet. This book covers all these new features.

Since Access 97 came out, I have worked (played?) hard to really use the new features that came out at that time, and really have gotten into increasing development productivity. I hope to pour that knowledge back into you through my Microsoft Access 2000 Power Programming book. A couple of topics I am really excited about are the use of custom collections and class modules. Creating a useful class module can save you hours of hassles and programming time. The other is creating your own wizards to increase productivity. These little builder wizards can take some of the cut-and-paste errors out of your development time.

The first thing I do when I buy a book is look at the table of contents. I then make sure that I can use at least five things from the book, so I know I'll get my money's worth. In this book, I am hoping you can use at least 50 things. Some techniques found here expand your development skill set, whereas others are items you can plug and play right into your code and don't have to care how they work (although if you are like me, you will).

F. Scott Barker's Microsoft Access 2000 Power Programming is also reworked to give you value faster in your reading. I have moved some chapters that might have been more introductory into the appendixes in the hope that if you've been using Access for a while, you can get to the meat quickly. However, if you want to get deeper into things such as the VBE (Visual Basic Editor) and debugging code, you can still find that information in this book.

I hope you enjoy this book. As one reader of an earlier edition told me, “I have used it 'til the cover came off.” If you see me at a conference or training, please stop me, say hi, and let me know what you think of the book.

Sincerely,

F. Scott Barker
Author, Microsoft Access 2000 Power Programming

WHO IS THIS BOOK FOR?

Again, this book is for both the corporate developer and independent consultant, because both have the same goal of cranking out a robust system in as little time as possible. For example, routines for startup system checking allow developers to control when users can get onto the system. These routines not only check for a good back end (and locate it if not found), but also repair the back-end database, if necessary (of course, after logging out all other users). Other situations are covered as well.

If you've been using Access macros all your life, don't be put off by the first chapter, “Macros Are for Weenies; Code Is Cool!” You've just entered a new dimension in your development careers, and just as the seniors in college harass juniors, VBA developers must give a bad time to those who use macros exclusively. Seriously—you will find in VBA an environment in which you have total control, more or less.

If you're switching from another language, the first few chapters are what you need to learn to get a handle on VBA. If you've been developing in Visual Basic, Chapter 2, “Coding in Access 2000 with VBA,” will give you an idea of the language differences.

If you're an advanced developer, you'll appreciate the chapters on the changes to the development environment, such as when to use ADO versus DAO, and using ADP versus `MDB. In addition to new material on automating Outlook from Access, there are examples of using class modules for real-world use.

WHAT'S COVERED IN F. SCOTT BARKER'S MICROSOFT ACCESS 2000 POWER PROGRAMMING?

F. Scott Barker's Microsoft Access 2000 Power Programming is broken into seven parts, which are laid out in a logical progression to lead you from power programming basics to routines that you can use to finish a robust application.

Part I: The Root of Power Programming

Part I begins with a quick chapter showing you how to switch from macros to Visual Basic for Applications (VBA). Then we get right into the meat for those getting into Access from other languages, with a quick study on VBA so that you can understand what's going on in the rest of the book.

Also included in this section is an examination of the virtually two environments Access 2000 now gives us to develop solutions. With this is a close examination of the two data-access methods that can now be used to work with data from code. Lastly, various techniques for error handling are presented.

• Chapter 1, “Macros Are for Weenies; Code Is Cool!” gives power users information on what's needed to switch from using macros to coding in VBA.

• Chapter 2, “Coding in Access 2000 with VBA,” gives a complete overview of the commands and features found in VBA.

• Chapter 3, “Making the Choice: MDB or ADP, DAO or ADO,” introduces the new Access environment choices. You read about the paths you can go down in developing your application and examine the differences between them.

• Chapter 4, “Looking at the Access Collections,” shows the various collections and objects that make up the Access object model, including the ADP and MDB models.

• Chapter 5, “Working with Data Access Objects,” shows the various collections and commands that can be used with DAO, which has been the lifeblood for data manipulation in Access.

• Chapter 6, “Introducing ActiveX Data Objects,” explains ADO, Microsoft's new preferred method for working with data. You can get going with the latest technology used throughout the Microsoft products.

• Chapter 7, “Handling Your Errors in Access with VBA,” explains how to trap errors to your full advantage, including how to use a centralized error routine for logging errors to disk.

Part II: Manipulating and Presenting Data

Although the term power programming sounds as though it could relate only to cranking out code, Access has tools that are powerful in their own right and are accessed through the user interface. That's what the chapters in Part II of the book are all about.

Underlying every great form or task is a query, and making sure that your queries are optimized as much as possible can literally make or break an application. Next, creating reusable forms is discussed, as are new VBA features that are displayed with code behind forms. You'll see how some powerful things can be done with combo and list boxes, as well as how to manipulate the controls in code. Some various techniques also are shown for creating versatile reports using VBA. Lastly, Data Pages are introduced, which gives you the best worlds of both forms and reports.

• Chapter 8, “Using Queries to Get the Most Out of Your Data,” shows how to create powerful queries for tasks, forms, and reports, as well as what optimization techniques can be used and when. Examples are given for using advanced query by form and for creating totals for crosstab queries.

• Chapter 9, “Creating Powerful Forms,” explains how to create reusable forms, use paging techniques, and use some of the new developer features found in Access 2000.

• Chapter 10, “Expanding the Power of Your Forms with Controls,” gives some useful examples on how to use combo and list boxes creatively and how to create a form with spreadsheet-type cursor movement. Included are techniques for creating forms and controls on-the-fly to build data-driven applications through VBA.

• Chapter 11, “Creating Powerful Reports,” shows how to create dynamic reports based on user requests—including one form that does the job of three—and how to create a report that reflects changing months even while using a crosstab query for the record source.

• Chapter 12, “Working with Data Pages,” shows how to take advantage of this interactive report feature. This chapter not only shows how to get going with Data Access Pages, but also what to watch out for when working with them.

Part III: Extending Access with Interoperability

Easily my favorite part of the book (and Access in general), Part III shows how to use Automation to drive the various Microsoft Office applications from each other through VBA. Some ActiveX controls that you can use with Access are explored with sample code, including the Windows 95 Interface ActiveX controls, which are included in the Office Developer's Edition.

I expanded this section to include discussions on creating class modules, collections, and wizards to increase your programming productivity. Access's exciting new Internet features are also looked at, with plenty of examples.

• Chapter 13, “Driving Office Applications with Automation and DDE,” includes abundant sample code that shows how to drive various Office applications from Access, including Word, Excel, Project, Outlook, and Graph. Working the other way, other examples show how to drive Access from Excel and Project.

• Chapter 14, “Programming for Power with ActiveX Controls,” introduces you to the Windows Common Controls that come in the Microsoft Office Developer. Sample code shows how to use the properties and methods for each control.

• Chapter 15, “Using API Calls to Extend the Power of Access,” gives you ideas on what you can do with API calls and how to find what API calls are available. This chapter gives some useful examples to show how to make API calls, including connecting and disconnecting logical drives on the network from within Access.

• Chapter 16, “Extending Your VBA Library Power with Class Modules and Collections,” shows you how to really take advantage of some of VBA's more advanced features and brings them down to a level where they can do a lot to increase development productivity. This chapter includes a cool example of providing a way to track multiple bookmarks for a user's form by using class modules and collections.

• Chapter 17, “Creating Your Own Wizards and Add-ins,” continues where Chapter 16 leaves off. Chapter 17 discusses how to create real-world wizards to use during development. Included are a couple of builders that help you add the new features discussed in Chapter 16 to any of your forms in only a couple of keystrokes.

• Chapter 18, “Manipulating the Registry with VBA,” shows you how to use the Registry for storing and retrieving your own information for your applications. Both VBA commands are covered as well as API routines.

• Chapter 19, “Using Access with the Internet,” discusses the features built into Access for the Internet. Included are the Hyperlink datatype, importing from and exporting to HTML tables, publishing forms and reports on the Internet from Access, and more.

Part IV: Adding the Professional Look and Distributing Applications

When it comes time to distribute your application to multiple users, there's no better way than to use the Office Developer's Edition's Setup Wizard. This ODE tool, as well as others, is examined to see what it can do for you. Also examined are Office command bars and API calls. When you run up against a feature that doesn't exist in Access, you'll most likely turn to the Windows API for the solution. And when you're trying to keep users out of the code, forms, and other object structures, using Access security is the only way.

• Chapter 20, “Programming Office Command Bars and the Office Assistant,” discusses the Command Bar object, which has methods and properties for creating menus, toolbars, and pop-up menus by using the user interface and VBA. You no longer have to use brain-dead macros to create menus and toolbars—you now have full control over these objects! Along with Command Bars, you can use the Office Assistant to give your users a user-friendly help system.

• Chapter 21, “Securing Your Application,” gives a thorough overview of the Access Security Model through the user interface and code. This chapter also presents tips, tricks, and VBA code examples for handling some of the most asked-about situations in Access security.

Part V: Managing Databases

No matter how advanced the software is, you must always take extra steps in programming when you're using your application on a network. Access is no different; although you can get by with simply throwing an application out on the server, you still need to know how to optimize it for the network environment and how to handle different situations that can occur.

Microsoft has worked hard to get replication right in this version of Access. This feature is fully explained, showing not only how to use it and to optimize for it, but also the theories behind it.

SQL Server is becoming so predominant that Access developers nowadays have to know something about it. It's even included in the Access box! One chapter in this part of the book helps you think ahead for what's in store when developing applications that can be used with SQL Server. Another discusses using it right from the workstation where you are doing your development.

• Chapter 22, “Handling Multiuser Situations,” gives an overview on the multiuser environment within Access, including locking methods and error handling. You'll also see sample code for using unbound forms, a popular method for increasing performance in certain situations. New options for row-level locking are also discussed.

• Chapter 23, “Welcome to the World of Database Replication,” discusses how to use database replication to its full advantage. Topics range from distributing replicated databases, understanding how conflicts are handled, and troubleshooting replicated databases.

• Chapter 24, “Moving Workgroup Applications to Client/Server,” gives a good overview of what it takes to move legacy Access applications over to client/server.

• Chapter 25, “Developing SQL Server Projects Using ADPs,” shows how to use the new Access Project (ADP) for creating solutions between Access and SQL Server.

Part VI: Adding Finishing Touches

The chapters in Part VI are somewhat where the rubber hits the road. Think of them as being “Scott's idea of what all applications should have in them.” I've even gone so far as to rewrite them to take advantage of both environments, DAO and ADO. Have fun!

• Chapter 26, “Startup Checking System Routines Using DAO,” covers not only checking the back end to make sure that it's still there, but also checking for valid versions of the application, even if the front end should be running at this time. Actual code for logging users out of the back end is given.

• Chapter 27, “Startup Checking System Routines Using ADO,” is similar to Chapter 26 but brings you into the world of ADO.

• Chapter 28, “Creating Maintenance Routines,” continues with what I deem as useful routines for allowing users to generically export tables to any supported format, compacting and repairing the back end from the front end (again, logging users out first), replicating semistatic tables for performance enhancements, and more.

Part VII: Appendixes

I expanded the appendixes greatly to include some elements that perhaps advanced developers might not need, but some of the more intermediate developers will find value in them. Also included are some useful lists to have when you're in the middle of cranking out code and don't want to have to look it up in a hundred different places (even if you could).

• Appendix A, “Debugging Code in Access 2000,” examines the Access debugging tools available, including the enhanced Debug window and its commands.

• Appendix B, “Getting Started with ActiveX Controls,” shows how to deal with ActiveX controls overall in registering them, using them on a form, and using VBA with them. Programming the Calendar ActiveX control, which comes with Access, is shown in its entirety. This appendix is great if you haven't dealt with ActiveX controls before. If you have used them, you can instead use Chapter 14.

• Appendix C, “Access 2000 and Jet 4 Errors,” documents the errors that can now occur from within Access and Jet.

• Appendix D, “Leszynski Naming Conventions for Microsoft Access,” gives the standards that are predominant in the industry, as well as in this book.

CONVENTIONS USED IN THIS BOOK

The Leszynski Naming Convention is used for all code examples. (For more information on this standard, see Appendix D.)

As with other routines created for system or generic purposes, most of the routines and forms in this chapter have ap_ in front of them. The ap_ is the initials for Applications Plus, my company's name. This isn't for copyright purposes, but to make sure that they don't conflict with other routines you might have with the same name.

Names of all dialogs and dialog options are written with initial capital letters. Messages that appear onscreen, as well as all program code and Access commands, appear in a special monospaced font, as in the following example: Variable undefined. Placeholders, or words that you replace with actual code values, are indicated with monospace italic. Text that you are to type appears in monospace boldface. New terms are introduced in italic type. When a choice is given for parameters, a pipe symbol (|) is used.

USING THE EXAMPLES FOR THIS BOOK

You can find the examples of this book on the accompanying CD-ROM in the Examples folder. To use the examples from this book, you need to copy them from the CD-ROM onto your hard drive. Depending on the copying method you use, you might have to open the properties for the database while in Explorer and deselect the Read-Only attribute so that you can read and write the file. (This is due to copying from a CD-ROM media and not any action that I did.)

Other items are included on the CD-ROM for your enjoyment and use as well:

• Demos of utility programs such as Speed Ferret, which is a utility for searching and replacing text throughout your Access application, and Total Access Analysis, by FMS, Inc.

• ActiveX Control demos including DBI Technologies Component Toolbox, which has these controls: an Outlook-style list bar, a tree view control, and a list box control.