Introduction
“I have been at this for two months already. So far nothing seems to work.”
—Seen in an Access newsgroup
The first time I used Access, I tore my hair out. I’d volunteered to help one of my favorite nonprofits (http://www.earthdance.net) fix their database, figuring that it would be a piece of cake for a “seasoned” pro like me. Instead, it was a nightmare. Every time I tried something that I knew should work, it didn’t quite work.
Since that first wrestling bout, I’ve used Access many times in my job as a software developer. Once you know its pathways and tricks, it’s almost indispensable—but it’s as frustrating as it is useful. In fact, at one point when I was talking to O’Reilly Executive Editor Robert Luhn about doing such a book, he asked if Access had enough “annoyances.” I shot back, “How many volumes did you have in mind?”
Access is a goulash of pitfalls and idiosyncrasies. Sometimes its user interface seems well designed and easy to use, and sometimes it seems to be a maze of narrow, twisty paths that dead-end. Some things in Access “just work,” while others require a secret hand-shake. (Don’t get me started on the Help system.)
For this book, I have been really fortunate to collaborate with Microsoft veteran Evan Callahan, who’s been working with Access since Version 1.0. We’ve drawn on our own experience as developers and teachers, and we’ve scoured newsgroups, user groups, and online communities to identify the Access features, practices, and glitches that cause the most confusion, exasperation, and desperate calls to that Access guy your cousin Sharlene knows.
This book covers a wide range of annoyances, some so basic that even new Microsoft Office users will appreciate them, and some so advanced that only hard-core Access programmers will be able to follow the code. So before we start, let’s talk about how you can get the most out of this book.
For openers, check out the Glossary at the back of the book. As you work your way through the various chapters, if you encounter a term or a concept that confuses you, check the Glossary. Hopefully you’ll find a definition that helps.
For an overview of fundamental Access operations, see our cleverly titled "Chapter 0,” Access Basics. For the inside scoop from Microsoft, see the section entitled “MSKB 123456” on how to mine the company’s Knowledge Base articles. Finally, if you need more technical reference material (such as a list of Visual Basic functions or macro actions), check the Appendix at the back of the book.
How to Use This Book
This book starts out with general annoyances, and then targets specific areas such as queries and reports. Each chapter is organized (more or less) with simpler annoyances toward the front and more advanced material toward the back. If you open to one of the chapter-closers and find yourself neck-deep in complicated code, don’t panic; the easy stuff is just a few pages toward the front. Also, we’ve tried to include enough background so that newbies can fix things they didn’t even know were broken. If you’re still working your way up the Access learning curve, there’s a lot you can learn here.
There are two different kinds of annoyances in Access. One kind, such as those listed in “Errors with Imported Data” in Chapter 3, pops up and hits you over the head. You’ll come to this book looking for help with these issues, because you can’t get the @#$@%# features to work at all. The other kind of annoyance has to do with making Access work better (rather than just work at all). For example, you can use Access for months and never think of changing the default option that leaves Name AutoCorrect turned on (see “Access’s Bad Defaults” in Chapter 1). Maybe no one ever told you that this is a hidden bomb that can cause database corruption and poor performance. When you start browsing through this book, pay special attention to the topics that offer preventive medicine (for instance, “Keeping Access Running Smoothly” in Chapter 2).
Access Versions
Most of the material in this book is valid for most versions of Access, but when we need to be specific about commands or options, we usually refer to Access 2002 and 2003. We’ll let you know if something is substantially different in Access 2000. We don’t cover versions prior to Access 2000, but here and there we may drop hints for dealing with Access 97 hassles.
MSKB 123456
Throughout this book you’ll see references that look like this: MSKB 209132. This is our shorthand for referring to Microsoft Knowledge Base articles by ID number. The main Knowledge Base is found at http://support.microsoft.com/default.aspx?pr=kbhowto, but to look up an article by its number you’ll want to browse to Microsoft’s advanced search page (http://support.microsoft.com/search/?adv=1) and set the Search Type drop-down menu to Article ID. In the For box above the Search Type drop-down menu, type in the number and hit Enter. You can also just do a plain search on the ID number, but you may have to wade through multiple hits.
If you’re using a browser that supports keyword substitution in URLs (such as Mozilla or Firefox), bookmark http://support.microsoft.com/default.aspx?kbid=%s and set the keyword to “mskb”. Then you’ll be able to jump directly to any Knowledge Base article by entering “mskb” followed by the article ID in the browser’s address box. (The browser substitutes the ID for the %s in the bookmark.) If, for some incomprehensible reason, you’re still using Internet Explorer, there’s a software patch that lets it do the same thing. See “A Shorter Path to Microsoft’s Knowledge” in Steve Bass’s PC Annoyances, Second Edition (O’Reilly) for details, and download PowerToys for Windows XP from http://www.oreilly.com/catalog/pcannoy2/index.html.
There’s a wealth of useful information in the Knowledge Base, but the search interface has some maddening blind spots. If you’re getting no hits on a specific phrase (and you really, truly spelled it correctly), chances are you set your search to use “The exact phrase entered.” That may seem like a reasonable option, but it doesn’t work very well. Instead, select “All of the words entered,” and you should get the hits you’re looking for. (Incidentally, Google crawls the Knowledge Base, so you can bypass Microsoft’s search interface entirely by doing an advanced Google search and limiting your results to the support.microsoft.com domain.)
Most of the Knowledge Base’s search interface is self-explanatory, but there are a couple of places where you need to be careful. First, you’ll probably want to click the “Specify a product or version” link to limit your search to materials related to Access. We recommend searching for information on all versions of Access—you’ll miss too many useful articles if you limit your search to a specific version. To specify all versions of Access, go to the advanced search page noted above, pop open the Search Product drop-down, and select “More Products.” Scroll down and click the “Office Access” item that has no version number.
The “Show results for” drop-down on the advanced search page is just as confusing. By default it’s set to “This product only,” which means that it ignores articles that aren’t associated with the specific product you’ve selected (i.e., Access). This is usually fine, but if you’re not getting any hits you should try “This product first,” which will scour the whole Knowledge Base and use your product preference to weight hits accordingly.
Conventions Used in This Book
The following typographic conventions are used in this book:
Italic is used for filenames, pathnames, URLs, email addresses, new terms where they are defined, and emphasis.
Constant widthis used for code excerpts, commands, method names, and function names.Constant width boldis used for items that should be typed verbatim by the user.Constant width italicis used for text that should be replaced with user-supplied values.Menu sequences are separated by arrows, such as Data → List → Create List. Tabs, radio buttons, checkboxes, and the like are identified by name, such as “click the Options tab and check the ‘Always show full menus’ box.”
Using Code Examples
This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.
We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: "Fixing Access Annoyances by Phil Mitchell and Evan Callahan. Copyright 2006 O’Reilly Media, Inc., 0-596-00852-X.”
If you feel your use of code examples falls outside fair use or the permission given here, feel free to contact us at permissions@oreilly.com.
I Did Exactly What You Said, and It Still Doesn’t Work!
This book is full of advice, and all of it has been tested. So how come some fix doesn’t work for you? Well…there could be something about your setup or Access version that’s causing problems. It’s even possible that one of our pinpoint instructions somehow eluded your unflinching eye. Or maybe, just maybe, we screwed up. In any case, let us know! We want to hear from you. Send email to annoyances@oreilly.com.
Safari® Enabled
When you see a Safari® Enabled icon on the cover of your favorite technology book, that means it’s available online through the O’Reilly Network Safari Bookshelf.
Safari offers a solution that’s better than e-books: it’s a virtual library that lets you easily search thousands of top tech books, cut and paste code samples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com.
Acknowledgments
First, we’d like to acknowledge the Access MVPs, and everyone who contributes to Access newsgroups; this is where so many of the “gotchas” in Access are brought to light, and these discussions were a great source of ideas for this book. Access MVP Lynn Trapp made an exceptional contribution to the early chapters of this book, and MVP Joan Wild kindly commented on an early outline. The Microsoft Support professionals who write the articles in the Knowledge Base also deserve our thanks for the expertise they share with the Access community.
Thanks to “Uncle Bob” (a.k.a. Robert Luhn, Executive Editor at O’Reilly), specifically for his prank phone calls pretending to be a foreign speaker with little English seeking Access help, and, in general, for shepherding this project through its highs and lows with unflappability.
Thanks also to Michael Oliver-Goodwin, editor extraordinaire, for his steamrolling patience and meticulous desire to make the book better. He didn’t even lose his cool when we got cranky….
Get Fixing Access Annoyances now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
