Book Review: Access 2002 Developer’s Handbook
Suffice it to say that the Access Developer’s Handbook (ADH) is legendary. It seems like not a week goes by at the comp.databases.ms-access newsgroup where someone with a question isn’t directed to the ADH. Invariably, the handbook thoroughly and completely answers the question, usually with a comprehensive explanation including code. If you’ve never seen a copy, you owe it to yourself to give it a look. If, like me, you didn’t read the Access 2000 version (I practically skipped Access 2000 entirely), you will definitely find the Access 2002 Developer’s Handbook worth the read.
What is the Access 2002 Developer’s Handbook?
The ADH is not an Access tutorial for beginners. It’s assumed that the reader is familiar with the basics of creating tables, queries, forms and reports. Though macros are mentioned from time to time, you will need at least a rudimentary understanding of how to write and run VBA code in standard modules. The handbook will help you sharpen your skills and will prepare you to avoid common mistakes. And, as an added bonus, you get lots of ready to run, plug and play code that you can import into your projects.
As the title suggests, this is a handbook. You can read it from cover to cover, as I did in preparing this article, or you can flip to the chapter you need. The authors prepared the material in such a way that each chapter stands on its own, with cross-references to assist the reader in finding related information elsewhere in the two volumes. The code examples are concise and easy to follow and are included on the companion CDs. Interspersed in each chapter are notes, tips and warnings. I found these comments especially useful and tagged many pages for future reference.
What’s new in 2002?
The handbook contains so much valuable information, it was difficult to know what to include in this review. Since most Access developers are familiar with the ADH, I’ll try to focus on what’s new, dropping occasional reminders of some “oldies but goodies” carried over from previous editions. Not having read the ADH for Access 2000, the line between “old” and “new” sometimes gets blurred for me, so what follows may be more accurately titled, “What’s new in 200x”.
Beginning with the Access 2000 ADH, the work spans two volumes. One focuses on Desktop issues (over 1300 pages) and the other covers Access in the Enterprise (nearly another 1000 pages). Though I was most interested in Enterprise issues, I read the desktop volume first, thinking I could breeze through it quickly. After all, I had already read the Access 95 and Access 97 ADHs. How much new stuff could there be? Plenty! Consider these new topics:
New topics in Access 2002 ADH Desktop
- Printer object and Printer collection
- PivotTable and PivotChart
- New report properties
- MDB conversion between version enhancements
- Office XP Web Components
- Form Dirty and Undo events
New topics in Access 2002 ADH Enterprise
- ImportXML, ExportXML and XSL generation
- SQL Server 2000 Desktop Engine
- Graphical designers for SQL Server stored procs and functions
- Data Access Page enhancements
As I read these books, I kept two highlighters at my side. A yellow one for marking important points to remember and a red marker to flag warnings never to forget. For example, the authors explain when to use a bang (!) versus a dot (.) operator and why you might use DBEngine(0)(0) instead of CurrentDB() to instantiate a DAO Database object. I also tagged pages containing simple but useful code like that to retrieve values from controls on subforms and how to initialize list and combo boxes to their first list item. One point I had to mark with red highlighter was a detail about ADO recordsets that occasionally catches me unawares. Readers are reminded that opening an ADO recordset using the Execute method of the Command object always returns a read-only recordset. To create an editable ADO recordset, stick with the Open method of the Recordset object.
Covering new technology is, of course, interesting and sexy, but some of the most useful topics in the ADH have been there for years. Wherever possible, code previously written for the DAO library has been upgraded to work with ADO, the default for Access 2000 and 2002 databases. Some of the discussions I found most helpful include the following:
- The 20 step approach to database design.
- Make form navigation behave like an Excel spreadsheet.
- Print mailing labels starting from a specific position.
- Automatically resizing forms and their controls.
- Tips for speeding up queries.
- Tips for optimizing VBA code.
- Tips for avoiding logic errors in your applications.
I hazard to guess that regardless of how long you’ve been working with Access, you’ll find something new and useful in the chapters that discuss application optimization. The authors give great suggestions for making your applications run faster — or in some cases, appear to run faster. Another well represented topic is error handling and debugging. Its 52 pages compose one of the best discussions I’ve read on the subject. Though more experienced developers will not likely find any new information here, the advice given serves as a good reminder of practices we should already be implementing.
Multiuser, enterprise and Web issues covered
In my opinion, programming with Microsoft Access really starts to get interesting as your applications move to the enterprise and to the Web. Accordingly, I was excited to finally begin reading the Access 2002 Enterprise Developer’s Handbook.
The book begins with a good discussion about splitting your Access database into two: an application database and a data database. Both file server and true client server scenarios are explained and compared. The chapter on Access security is the most comprehensive I’ve seen. In addition to describing how to implement security through the Access user interface, code is provided to manage users and permissions using DAO, ADOX and DDL SQL scripts.
Several chapters are devoted to working with SQL Server. The information included is sufficient to help an Access developer get started with SQL Server. Enough basic training is provided to show you how to create database objects such as tables, views, stored procs and the like. Though a brief T-SQL tutorial is provided, if you plan to do anything fancy, you’ll want to invest in a good book on SQL Server.
The last set of chapters deal with Data Access Pages, using Access as a Web client and integrating Access with XML. DAPs were first introduced in Access 2000 to provide Web pages for displaying and editing your Access data. Creating and deploying them was problematic, but according to the ADH, the Access 2002 version is much more mature. In addition to explaining how to use the Access user interface to create deployable DAPs, the book gives a good tutorial on creating basic ASP pages, even touching on the new ASP.Net technology. The chapter on integrating XML with Access 2002 covers all the technical aspects of importing and exporting data but lacks real-life scenarios where this technology applies.
I usually like to end book reviews with a “What’s the verdict?” section, but that question just doesn’t apply to the Developer’s Handbook. It’s not a question of whether or not the book is a great value. There really is something for everyone in the Access 2002 Developer’s Handbook. How much you personally can expect to leverage from these volumes depends on how much you may already know, but for the serious Access developer, these books are a must have.
This article originally appeared in the Smart Access Newsletter, Pinnacle Publishing, Inc. (c) All rights reserved.