Book description
When an application is part of the Microsoft Office suite, it's sure to be a leader in its field. In the realm of desktop database management, Access is top dog with millions of users. But this is one dog that can bite. Although Access is a powerful, relational tool with the fetching talents of a Labrador, it's not an easy beast to train.
Still, millions of users count on Access for everything from managing parts databases to running Web catalogs to working as a front end to mondo SQL databases. But Access is chockablock with annoyances---report hassles, query conundrums, VBA bugs, arcane error messages, and more.
O'Reilly's Annoyances series offer real-world help, right now, and Fixing Access Annoyances continues tradition. You'll not only squash bugs and workaround Access' limits, but you'll learn how to use Access to the max, whether you're a newbie or a seasoned pro. Coverage includes install/configuration annoyances, building better tables and queries, creating forms that work right, generating reliable and sophisticated reports, pulling in data from a variety of sources, crafting macros and VBA code to customize Access, and much more.
You could grab those other books for help, but do they solve problems from page one? Meet a book of a different stripe. The authors come armed with knowledge of the program's quirks, design hurdles and interface snags. They provide you with battle plans in Fixing Access Annoyances to save you time and bouts of hair pulling.
Stop information from spiraling out of control when working with Access and trying to make this #$@@#$ thing work! Don't let its quirks, bugs, and troublemaking features beat you. Who you gonna call for help? Instead of waiting on the line for tech support or searching for the answer on the Internet with its too many resources to find exactly what you need, take control of databases with Fixing Access Annoyances, your partner on database adventures.
Publisher resources
Table of contents
- Copyright
- Dedication
- Introduction
- 0. Access Basics
-
1. General Annoyances
-
1.1. CUSTOMIZING ACCESS
- 1.1.1. Access’s Bad Defaults
- 1.1.2. Alt-Tab Clutter
- 1.1.3. A Better Alt-Tab
- 1.1.4. The Recently Used File List
-
1.1.5. Grayed-Out Menu Items
- 1.1.5.1. Import Spreadsheet Wizard: Data Type box
- 1.1.5.2. Import Spreadsheet Wizard: “In an Existing Table” option
- 1.1.5.3. Tools → Database Utilities: Linked Table Manager, Convert Database, and Upsizing Wizard
- 1.1.5.4. New Record(*)Button on a Form, or Insert → New Record for a Table
- 1.1.5.5. Tools → Database Utilities → Convert Database → To Access 2000 File Format
- 1.1.5.6. Edit Relationships Dialog (Tools → Relationships): Enforce Referential Integrity
- 1.1.5.7. Format → Conditional Formatting (Design View for a form)
- 1.1.5.8. Query → Parameters
- 1.1.5.9. Tools → Database Utilities → Make MDE File or Make ADE File
- 1.1.5.10. Change Owner Button in Tools → Security → User and Group Permissions
- 1.1.6. Define Your Own Defaults for Forms, Reports, and Controls
- 1.1.7. Defaults for Tables, Queries, and Datasheets
- 1.1.8. Hidden (but Indispensable) Keyboard Shortcuts
- 1.1.9. Create Keyboard Shortcuts
- 1.1.10. Toggle Between Design View and Object View
- 1.1.11. Type Loooooong Lines in Small Places
- 1.1.12. Access Changes My Data
- 1.1.13. Help That Isn’t/Is Helpful
- 1.1.14. Give Me Full Menus!
- 1.1.15. Missing or Mangled Toolbars and Menus
- 1.1.16. Missing Objects (Forms, Reports, Queries…)
- 1.1.17. Scroll Wheel Confusion
- 1.1.18. Turn Off Dire Hyperlink Warnings
- 1.1.19. Find/Replace for Database Objects
- 1.1.20. Save Custom Form Placement
- 1.1.21. Data Entry Without Access
- 1.1.22. Getting Lost in Datasheet View
- 1.2. ACCESS RUNS AMOK
-
1.3. ERROR MESSAGES
-
1.3.1. #Name? and #Error?
- 1.3.1.1. Misspelled or Nonexistent Name
- 1.3.1.2. Missing Equals Sign
- 1.3.1.3. Name Is Out of Scope
- 1.3.1.4. Incorrect Expression Syntax
- 1.3.1.5. Use of Visual Basic Constants
- 1.3.1.6. Empty Query or Subform
- 1.3.1.7. Parameter Query Embedded in Expression
- 1.3.1.8. Wrong Syntax for Subform Reference
- 1.3.1.9. Circular References
- 1.3.2. #Num!, #Div/0!, #Deleted, and #Locked
- 1.3.3. Enter Parameter Value
- 1.3.4. No Access License
- 1.3.5. User-Defined Type Not Defined
- 1.3.6. Missing References
- 1.3.7. Disk or Network Errors
- 1.3.8. Database Is Read-Only or File Is Already in Use
-
1.3.1. #Name? and #Error?
-
1.1. CUSTOMIZING ACCESS
-
2. Performance, Versions, Security, and Deployment
- 2.1. PERFORMANCE
-
2.2. VERSIONS, UPGRADES, AND CONVERSION
- 2.2.1. Access Works on One PC but Not Another
- 2.2.2. Access/Windows Compatibility Issues
- 2.2.3. Converting Old Databases
- 2.2.4. Running the Same Database in Multiple Versions of Access
- 2.2.5. Running Different Versions of Access on the Same PC
- 2.2.6. Running the Right Version of Access
- 2.2.7. This Upgrade Is Killing Me!
- 2.2.8. Many Offices, Many Library Incompatibilities
-
2.3. SECURITY
- 2.3.1. Access Security Is Weak
- 2.3.2. Upgrading Secured Databases
- 2.3.3. Database Lockout
- 2.3.4. Administering User-Level Security Correctly
- 2.3.5. Undo Botched Security
- 2.3.6. Frontend Secure, Backend Unprotected
- 2.3.7. First Login Fails After Securing Database
- 2.3.8. Too Many Logins
- 2.3.9. Splitting a Secured Database
- 2.3.10. Security Fails on Network Deployment
- 2.3.11. “Unsafe Expressions” Warning
- 2.3.12. Creating Digital Signatures
-
2.4. ENTERPRISE DEPLOYMENT AND NETWORKED DATABASES
- 2.4.1. Keeping Access Running Smoothly
- 2.4.2. Automating Maintenance Chores
- 2.4.3. Best Practices for Access on Networks
- 2.4.4. Getting Around Jet’s 2-GB Limit
- 2.4.5. Avoiding Manual DSN Setup for ODBC
- 2.4.6. VB Code Fails After Split
- 2.4.7. Merging Two Frontends
- 2.4.8. Can’t Get Replication to Work
- 2.4.9. Replication Fails on Frontend Objects
- 2.4.10. Corrupted Replicas
-
3. Data, Tables, and Database Design
-
3.1. DATABASE DESIGN AND DATA INTEGRITY
- 3.1.1. Table Design 101
- 3.1.2. Relationship Angst
- 3.1.3. I Don’t Want to Design a Database from Scratch
- 3.1.4. Bad Field Names
- 3.1.5. Flaws in the Decimal Data Type
- 3.1.6. AutoNumber Nightmares
- 3.1.7. Can’t Create a Relationship
- 3.1.8. Can’t Delete Records
- 3.1.9. Default Values Don’t Apply to Existing Records
- 3.1.10. Simple Validation Rules
- 3.1.11. Complex Validation Rules
- 3.1.12. Subtypes and Supertypes
- 3.1.13. Duplicate Records
- 3.1.14. Relationships Window Ghosts
- 3.1.15. Relationships Window Layout Distress
- 3.1.16. Attack of the Nonexistent Tables
-
3.2. IMPORTING, LINKING, AND EXPORTING DATA
- 3.2.1. Errors with Imported Data
- 3.2.2. Linked Spreadsheet Woes
- 3.2.3. Data Incorrectly Imported as Dates
- 3.2.4. Obscure Excel Import Errors
- 3.2.5. Unhelpful Error Messages During Import
- 3.2.6. Can’t Import Word Tables
- 3.2.7. Copy/Paste to Excel Is Broken
- 3.2.8. Exporting Reports Produces Weird Numbers
- 3.2.9. Hidden Apostrophes in Exported Data
- 3.2.10. Exported Numeric Data Is Truncated
- 3.2.11. Exported Text Data Is Truncated
- 3.2.12. Miscellaneous Export Annoyances
- 3.2.13. Putting Data on the Web
- 3.2.14. Automating Import/Export
- 3.2.15. Exporting Data on the Fly
-
3.1. DATABASE DESIGN AND DATA INTEGRITY
-
4. Queries
-
4.1. QUERY BASICS
- 4.1.1. Query a Single Table
- 4.1.2. Query Multiple Tables
- 4.1.3. Meaningless Column Names
- 4.1.4. Create a Calculated Field
- 4.1.5. Create a Totals Query
- 4.1.6. Find the Properties Sheet for a Query
- 4.1.7. Divide and Conquer
- 4.1.8. Spurious Joins
- 4.1.9. Limit the Number of Records Returned
- 4.1.10. Avoid Duplicates in a Query
- 4.1.11. Find Duplicate Records
- 4.1.12. Count Yes/No Answers
- 4.1.13. Jump to SQL View
- 4.1.14. Speed Up Slow Queries
-
4.2. QUERY MISFIRES
- 4.2.1. Data Is Missing from a Multi-Table Query
- 4.2.2. Query Has No Data or Has Wrong Data
- 4.2.3. “Aggregate Function” Error
- 4.2.4. Totals Query Gives Incorrect Result
- 4.2.5. Sort Order Is Out of Order
- 4.2.6. Left Join Doesn’t Work
- 4.2.7. “Join Expression Not Supported” and “Ambiguous Outer Joins” Errors
- 4.2.8. Input Mask Nixes Queries
- 4.3. PARAMETER AND CROSSTAB QUERIES
- 4.4. TECHNIQUES FOR DIFFICULT QUERIES
-
4.1. QUERY BASICS
-
5. Forms
- 5.1. WORKING IN DESIGN VIEW
- 5.2. FORM DESIGN
-
5.3. CREATE FORMS YOUR USERS WILL LOVE
- 5.3.1. Simplify Data Entry
- 5.3.2. Open a Form to a New Record
- 5.3.3. Refresh Data Automatically
- 5.3.4. Hide Foreign Keys
- 5.3.5. Find Records Faster
- 5.3.6. Handle Items Not in a Combo Box
- 5.3.7. One Form, Many Screen Resolutions
- 5.3.8. Option Groups Don’t Allow Text Values
- 5.3.9. Can’t Change Column Headings for Datasheet View
- 5.3.10. Set Focus Doesn’t Work
- 5.3.11. Enable Null Values in a Combo Box
- 5.3.12. Fix Scrolling in Combo Boxes
- 5.3.13. Use Arrow Keys in Combo Boxes
- 5.3.14. Speed Up Slow Combo Boxes
- 5.3.15. The Limits of Conditional Formatting
- 5.3.16. Form Is Blank in Form View
- 5.3.17. Edit User-Entered Data
- 5.3.18. Can’t Save Changes to Data
- 5.3.19. Some Forms Shouldn’t Be Editable by Default
- 5.3.20. Too Many Controls on One Page
- 5.3.21. Carry Data Forward to the Next Record
-
6. Reports, Mailing Labels, and Charts
-
6.1. GENERAL REPORT ANNOYANCES
- 6.1.1. Report Wizard Isn’t Working Its Magic
- 6.1.2. Build a Report from Scratch
- 6.1.3. Report Preview Is Too Small
- 6.1.4. Unexpected Parameter Boxes
- 6.1.5. Calculated Field Shows #Error?
- 6.1.6. Truncated Memo Field
- 6.1.7. Reports with No Data
- 6.1.8. Error 2501: The OpenReport Action Was Canceled
- 6.1.9. Filter with Parameters
- 6.1.10. Number Records Consecutively
- 6.1.11. Export to Word/RTF
- 6.1.12. Give Up and Try Excel Instead
- 6.2. PAGE LAYOUT AND PRINTING
- 6.3. SORTING, GROUPING, AND SUBREPORTS
-
6.4. MAILING LABELS
- 6.4.1. Off to See the Wizard
- 6.4.2. Custom Label Templates
- 6.4.3. Too Much Label, Not Enough Margin
- 6.4.4. Eliminate Blank Lines, and Other Label Tweaks
- 6.4.5. Prompt for Trim Function
- 6.4.6. Zip+4 Codes Run Together
- 6.4.7. More Than One Label for Each Record
- 6.4.8. Printing Partial Sheets of Labels
- 6.5. CHARTS AND GRAPHS
-
6.1. GENERAL REPORT ANNOYANCES
-
7. Expressions, Macros, Code Modules, and Custom Controls
-
7.1. EXPRESSIONS
- 7.1.1. .Dot, !Bang, and [Bracket]
- 7.1.2. Expressions That Go Blank
- 7.1.3. Tangled Up in Null
- 7.1.4. Debugging Expressions
- 7.1.5. Mysterious Syntax Errors
- 7.1.6. “Quotes”, #Quotes#, and More #%&@!! “"""Quotes"""”
- 7.1.7. Like, In, and Between Operators
- 7.1.8. “Like” Operator Changes to “Alike”
- 7.1.9. Expression-Building Blues
- 7.1.10. Dollar$ Sign$ Functions
- 7.1.11. [Brackets] Versus “Quotes”
- 7.1.12. Last Name, First
- 7.1.13. Dates! Dates! Dates!
- 7.1.14. Compare Dates, Ignore Times
- 7.1.15. Working Days Not Working
- 7.1.16. Refer to Subform Properties
-
7.2. MACROS AND CODE MODULES
- 7.2.1. Find a Macro Action
- 7.2.2. Tame the Visual Basic Editor
- 7.2.3. Find a Visual Basic Function
- 7.2.4. Pick the Right Event
- 7.2.5. Apostrophe Errors
- 7.2.6. Disable Confirmation Dialogs
- 7.2.7. Pause Macro or Code
- 7.2.8. Better Error Handling
- 7.2.9. Save a Record
- 7.2.10. Lost Data
- 7.2.11. Mysterious Syntax Errors, Part Deux
- 7.3. CUSTOM CONTROLS AND EXTERNAL APPLICATIONS
-
7.1. EXPRESSIONS
- A. Appendix
- Glossary
- About the Authors
- About the Authors
- Colophon
- Copyright
Product information
- Title: Fixing Access Annoyances
- Author(s):
- Release date: February 2006
- Publisher(s): O'Reilly Media, Inc.
- ISBN: 9780596008529
You might also like
book
Access 2013: The Missing Manual
Unlock the secrets of Access 2013 and discover how to use your data in creative ways. …
article
Why the Power of Technology Rarely Goes to the People
Throughout history, the advantages and costs of technological innovations have been unevenly distributed between the powerful …
article
How to Overcome a Power Deficit
At some point in their careers, most executives — even the most talented — will face …
book
IBM Financial Transaction Manager for Automated Clearing House Services
Automated Clearing House (ACH) payment volume is increasing every year. NACHA estimates that ACH payments crossed …