You are previewing Fixing Access Annoyances.
O'Reilly logo
Fixing Access Annoyances

Book Description

When an application is part of the Microsoft Office suite, it'ssure to be a leader in its field. In the realm of desktop databasemanagement, Access is top dog with millions of users. But this isone dog that can bite. Although Access is a powerful, relationaltool with the fetching talents of a Labrador, it's not an easybeast to train.

Still, millions of users count on Access for everything frommanaging parts databases to running Web catalogs to working as afront end to mondo SQL databases. But Access is chockablock withannoyances---report hassles, query conundrums, VBA bugs, arcaneerror messages, and more.

O'Reilly's Annoyances series offer real-world help, right now,and Fixing Access Annoyances continues tradition. You'll notonly squash bugs and workaround Access' limits, but you'll learnhow to use Access to the max, whether you're a newbie or a seasonedpro. Coverage includes install/configuration annoyances, buildingbetter tables and queries, creating forms that work right,generating reliable and sophisticated reports, pulling in data froma variety of sources, crafting macros and VBA code to customizeAccess, and much more.

You could grab those other books for help, but do they solveproblems from page one? Meet a book of a different stripe. Theauthors come armed with knowledge of the program's quirks, designhurdles and interface snags. They provide you with battle plans inFixing Access Annoyances to save you time and bouts of hairpulling.

Stop information from spiraling out of control when working withAccess and trying to make this #$@@#$ thing work! Don't let itsquirks, bugs, and troublemaking features beat you. Who you gonnacall for help? Instead of waiting on the line for tech support orsearching for the answer on the Internet with its too manyresources to find exactly what you need, take control of databaseswith Fixing Access Annoyances, your partner on databaseadventures.

Table of Contents

  1. Copyright
  2. Dedication
  3. Special Upgrade Offer
  4. Introduction
    1. How to Use This Book
    2. Access Versions
    3. MSKB 123456
    4. Conventions Used in This Book
    5. Using Code Examples
    6. I Did Exactly What You Said, and It Still Doesn’t Work!
    7. Safari® Enabled
    8. Acknowledgments
  5. 0. Access Basics
    1. 0.1. The Database Window
    2. 0.2. Wizards
    3. 0.3. Design View and User Views
    4. 0.4. Setting Properties
    5. 0.5. Bound Versus Unbound Objects
    6. 0.6. Multi-User Databases and Split Design
    7. 0.7. How to Create an Event Procedure
    8. 0.8. How to Add Code to Your Application
    9. 0.9. DAO Versus ADO
  6. 1. General Annoyances
    1. 1.1. CUSTOMIZING ACCESS
      1. 1.1.1. Access’s Bad Defaults
        1. 1.1.1.1. Global Defaults
        2. 1.1.1.2. Specific Defaults
      2. 1.1.2. Alt-Tab Clutter
      3. 1.1.3. A Better Alt-Tab
      4. 1.1.4. The Recently Used File List
      5. 1.1.5. Grayed-Out Menu Items
        1. 1.1.5.1. Import Spreadsheet Wizard: Data Type box
        2. 1.1.5.2. Import Spreadsheet Wizard: “In an Existing Table” option
        3. 1.1.5.3. Tools → Database Utilities: Linked Table Manager, Convert Database, and Upsizing Wizard
        4. 1.1.5.4. New Record(*)Button on a Form, or Insert → New Record for a Table
        5. 1.1.5.5. Tools → Database Utilities → Convert Database → To Access 2000 File Format
        6. 1.1.5.6. Edit Relationships Dialog (Tools → Relationships): Enforce Referential Integrity
        7. 1.1.5.7. Format → Conditional Formatting (Design View for a form)
        8. 1.1.5.8. Query → Parameters
        9. 1.1.5.9. Tools → Database Utilities → Make MDE File or Make ADE File
        10. 1.1.5.10. Change Owner Button in Tools → Security → User and Group Permissions
      6. 1.1.6. Define Your Own Defaults for Forms, Reports, and Controls
      7. 1.1.7. Defaults for Tables, Queries, and Datasheets
      8. 1.1.8. Hidden (but Indispensable) Keyboard Shortcuts
      9. 1.1.9. Create Keyboard Shortcuts
      10. 1.1.10. Toggle Between Design View and Object View
      11. 1.1.11. Type Loooooong Lines in Small Places
      12. 1.1.12. Access Changes My Data
      13. 1.1.13. Help That Isn’t/Is Helpful
        1. 1.1.13.1. Newsgroups
        2. 1.1.13.2. Utter Access
        3. 1.1.13.3. Expert Sites
        4. 1.1.13.4. The Microsoft Knowledge Base
        5. 1.1.13.5. Microsoft Developer Network (MSDN)
      14. 1.1.14. Give Me Full Menus!
      15. 1.1.15. Missing or Mangled Toolbars and Menus
        1. 1.1.15.1. Toolbar/Menu Is Missing
        2. 1.1.15.2. Toolbar/Menu Has Been Altered
        3. 1.1.15.3. Toolbar/Menu Has Been Moved or Merged into Another One
        4. 1.1.15.4. Toolbar Is Disabled at Startup
      16. 1.1.16. Missing Objects (Forms, Reports, Queries…)
      17. 1.1.17. Scroll Wheel Confusion
      18. 1.1.18. Turn Off Dire Hyperlink Warnings
      19. 1.1.19. Find/Replace for Database Objects
      20. 1.1.20. Save Custom Form Placement
      21. 1.1.21. Data Entry Without Access
      22. 1.1.22. Getting Lost in Datasheet View
    2. 1.2. ACCESS RUNS AMOK
      1. 1.2.1. Access Is Buggy and Unstable
      2. 1.2.2. Flaky or Corrupted Database
        1. 1.2.2.1. Preparation
        2. 1.2.2.2. Use the Built-in Compact/Repair Utility
        3. 1.2.2.3. Export to a New Database
        4. 1.2.2.4. Problem-Specific Suggestions
        5. 1.2.2.5. Professional Help
      3. 1.2.3. That Darn Corruption
        1. 1.2.3.1. Network Problems
        2. 1.2.3.2. System Crashes
        3. 1.2.3.3. Access Bugs
        4. 1.2.3.4. Application Bugs
        5. 1.2.3.5. User Error
      4. 1.2.4. Missing Wizards or Import/Export/Link Options
        1. 1.2.4.1. Missing or Broken Wizards
        2. 1.2.4.2. Missing Import/Export/Link Options
      5. 1.2.5. Agonies of a Sick Installation
    3. 1.3. ERROR MESSAGES
      1. 1.3.1. #Name? and #Error?
        1. 1.3.1.1. Misspelled or Nonexistent Name
        2. 1.3.1.2. Missing Equals Sign
        3. 1.3.1.3. Name Is Out of Scope
        4. 1.3.1.4. Incorrect Expression Syntax
        5. 1.3.1.5. Use of Visual Basic Constants
        6. 1.3.1.6. Empty Query or Subform
        7. 1.3.1.7. Parameter Query Embedded in Expression
        8. 1.3.1.8. Wrong Syntax for Subform Reference
        9. 1.3.1.9. Circular References
      2. 1.3.2. #Num!, #Div/0!, #Deleted, and #Locked
      3. 1.3.3. Enter Parameter Value
      4. 1.3.4. No Access License
      5. 1.3.5. User-Defined Type Not Defined
      6. 1.3.6. Missing References
      7. 1.3.7. Disk or Network Errors
      8. 1.3.8. Database Is Read-Only or File Is Already in Use
  7. 2. Performance, Versions, Security, and Deployment
    1. 2.1. PERFORMANCE
      1. 2.1.1. Database Bloat from Images or OLE Objects
      2. 2.1.2. Why Is My Database So Slow?
        1. 2.1.2.1. Name AutoCorrect and Subdatasheet Name
        2. 2.1.2.2. Queries
        3. 2.1.2.3. Reports
        4. 2.1.2.4. Forms
        5. 2.1.2.5. Functions
      3. 2.1.3. Why Is My Split Database So Slow?
        1. 2.1.3.1. Maintain a Persistent Connection to the Backend
        2. 2.1.3.2. Make the Backend Easy to Get To
        3. 2.1.3.3. Check Your Anti-Virus Software
      4. 2.1.4. XP Home Is Way Slow
      5. 2.1.5. Access with SQL Server Is Still Slow
    2. 2.2. VERSIONS, UPGRADES, AND CONVERSION
      1. 2.2.1. Access Works on One PC but Not Another
      2. 2.2.2. Access/Windows Compatibility Issues
      3. 2.2.3. Converting Old Databases
      4. 2.2.4. Running the Same Database in Multiple Versions of Access
      5. 2.2.5. Running Different Versions of Access on the Same PC
      6. 2.2.6. Running the Right Version of Access
      7. 2.2.7. This Upgrade Is Killing Me!
      8. 2.2.8. Many Offices, Many Library Incompatibilities
    3. 2.3. SECURITY
      1. 2.3.1. Access Security Is Weak
      2. 2.3.2. Upgrading Secured Databases
      3. 2.3.3. Database Lockout
        1. 2.3.3.1. Simple Security
        2. 2.3.3.2. User-Level Security
        3. 2.3.3.3. Missing or Corrupted Workgroup File
        4. 2.3.3.4. Third-Party Products/Services
      4. 2.3.4. Administering User-Level Security Correctly
        1. 2.3.4.1. Create a New Workgroup File
        2. 2.3.4.2. Create a New Database Administrator
        3. 2.3.4.3. Create a New, Secured Database
        4. 2.3.4.4. Create Custom Groups
        5. 2.3.4.5. Assign Permissions to Groups
        6. 2.3.4.6. Create Users
        7. 2.3.4.7. Restore the Default Workgroup (Optional)
      5. 2.3.5. Undo Botched Security
      6. 2.3.6. Frontend Secure, Backend Unprotected
      7. 2.3.7. First Login Fails After Securing Database
      8. 2.3.8. Too Many Logins
      9. 2.3.9. Splitting a Secured Database
      10. 2.3.10. Security Fails on Network Deployment
      11. 2.3.11. “Unsafe Expressions” Warning
      12. 2.3.12. Creating Digital Signatures
    4. 2.4. ENTERPRISE DEPLOYMENT AND NETWORKED DATABASES
      1. 2.4.1. Keeping Access Running Smoothly
      2. 2.4.2. Automating Maintenance Chores
      3. 2.4.3. Best Practices for Access on Networks
        1. 2.4.3.1. Minimize Network Connections
        2. 2.4.3.2. Optimize Your Design
        3. 2.4.3.3. Hardware Considerations
        4. 2.4.3.4. Oplocks
      4. 2.4.4. Getting Around Jet’s 2-GB Limit
      5. 2.4.5. Avoiding Manual DSN Setup for ODBC
      6. 2.4.6. VB Code Fails After Split
      7. 2.4.7. Merging Two Frontends
      8. 2.4.8. Can’t Get Replication to Work
      9. 2.4.9. Replication Fails on Frontend Objects
      10. 2.4.10. Corrupted Replicas
  8. 3. Data, Tables, and Database Design
    1. 3.1. DATABASE DESIGN AND DATA INTEGRITY
      1. 3.1.1. Table Design 101
      2. 3.1.2. Relationship Angst
      3. 3.1.3. I Don’t Want to Design a Database from Scratch
      4. 3.1.4. Bad Field Names
      5. 3.1.5. Flaws in the Decimal Data Type
      6. 3.1.6. AutoNumber Nightmares
      7. 3.1.7. Can’t Create a Relationship
      8. 3.1.8. Can’t Delete Records
      9. 3.1.9. Default Values Don’t Apply to Existing Records
      10. 3.1.10. Simple Validation Rules
        1. 3.1.10.1. The Like Operator
        2. 3.1.10.2. The Between Operator
        3. 3.1.10.3. The In Operator
        4. 3.1.10.4. Record-Level Rules
      11. 3.1.11. Complex Validation Rules
      12. 3.1.12. Subtypes and Supertypes
      13. 3.1.13. Duplicate Records
      14. 3.1.14. Relationships Window Ghosts
      15. 3.1.15. Relationships Window Layout Distress
      16. 3.1.16. Attack of the Nonexistent Tables
    2. 3.2. IMPORTING, LINKING, AND EXPORTING DATA
      1. 3.2.1. Errors with Imported Data
      2. 3.2.2. Linked Spreadsheet Woes
      3. 3.2.3. Data Incorrectly Imported as Dates
      4. 3.2.4. Obscure Excel Import Errors
      5. 3.2.5. Unhelpful Error Messages During Import
      6. 3.2.6. Can’t Import Word Tables
      7. 3.2.7. Copy/Paste to Excel Is Broken
      8. 3.2.8. Exporting Reports Produces Weird Numbers
      9. 3.2.9. Hidden Apostrophes in Exported Data
      10. 3.2.10. Exported Numeric Data Is Truncated
      11. 3.2.11. Exported Text Data Is Truncated
      12. 3.2.12. Miscellaneous Export Annoyances
      13. 3.2.13. Putting Data on the Web
        1. 3.2.13.1. Static and Read-Only
        2. 3.2.13.2. Dynamic and Read-Only
        3. 3.2.13.3. Dynamic and Editable
      14. 3.2.14. Automating Import/Export
      15. 3.2.15. Exporting Data on the Fly
  9. 4. Queries
    1. 4.1. QUERY BASICS
      1. 4.1.1. Query a Single Table
      2. 4.1.2. Query Multiple Tables
      3. 4.1.3. Meaningless Column Names
      4. 4.1.4. Create a Calculated Field
      5. 4.1.5. Create a Totals Query
      6. 4.1.6. Find the Properties Sheet for a Query
      7. 4.1.7. Divide and Conquer
      8. 4.1.8. Spurious Joins
      9. 4.1.9. Limit the Number of Records Returned
      10. 4.1.10. Avoid Duplicates in a Query
      11. 4.1.11. Find Duplicate Records
      12. 4.1.12. Count Yes/No Answers
      13. 4.1.13. Jump to SQL View
      14. 4.1.14. Speed Up Slow Queries
    2. 4.2. QUERY MISFIRES
      1. 4.2.1. Data Is Missing from a Multi-Table Query
      2. 4.2.2. Query Has No Data or Has Wrong Data
      3. 4.2.3. “Aggregate Function” Error
      4. 4.2.4. Totals Query Gives Incorrect Result
      5. 4.2.5. Sort Order Is Out of Order
      6. 4.2.6. Left Join Doesn’t Work
      7. 4.2.7. “Join Expression Not Supported” and “Ambiguous Outer Joins” Errors
      8. 4.2.8. Input Mask Nixes Queries
    3. 4.3. PARAMETER AND CROSSTAB QUERIES
      1. 4.3.1. Parameter Queries with Wildcards
      2. 4.3.2. Parameter Queries and Blank Responses
      3. 4.3.3. User-Friendly Parameter Queries
      4. 4.3.4. Parameter Queries That Accept Lists
      5. 4.3.5. Parameters in Crosstab Queries
      6. 4.3.6. Sorting Crosstab Rows Based on Totals
      7. 4.3.7. Crosstab Queries with Multiple Values
    4. 4.4. TECHNIQUES FOR DIFFICULT QUERIES
      1. 4.4.1. Comparing Different Rows
      2. 4.4.2. Working with Hierarchies
      3. 4.4.3. Working with Ranges
      4. 4.4.4. Full Outer Joins
      5. 4.4.5. Divide and Conquer with Subqueries
      6. 4.4.6. Finding Rows That Don’t Exist
      7. 4.4.7. Working with Calendar Dates
      8. 4.4.8. Missing SQL
  10. 5. Forms
    1. 5.1. WORKING IN DESIGN VIEW
      1. 5.1.1. Activating the Wizards
      2. 5.1.2. Edit Many Controls at Once
      3. 5.1.3. Leaving the Properties Sheet Open
      4. 5.1.4. Where Are the Form’s Properties?
      5. 5.1.5. Attached Labels
      6. 5.1.6. Fix Combo and List Box Names
      7. 5.1.7. Subform Is Blank in Design View
      8. 5.1.8. Form Opens Slowly in Design View
    2. 5.2. FORM DESIGN
      1. 5.2.1. Building the Right Form
        1. 5.2.1.1. Unrelated Table
        2. 5.2.1.2. One-to-Many
        3. 5.2.1.3. One-to-Many-to-Many
        4. 5.2.1.4. Many-to-One
        5. 5.2.1.5. Many-to-Many
      2. 5.2.2. Synchronizing Subforms
      3. 5.2.3. Synchronizing Two Forms
      4. 5.2.4. Create Dialog Box Input Forms
      5. 5.2.5. Display Subform Values on the Main Form
      6. 5.2.6. Hide an Empty Subform
      7. 5.2.7. Reusing Subforms
    3. 5.3. CREATE FORMS YOUR USERS WILL LOVE
      1. 5.3.1. Simplify Data Entry
        1. 5.3.1.1. Autofill Dates
        2. 5.3.1.2. Autofill Related Fields
        3. 5.3.1.3. Define the Tab Order
        4. 5.3.1.4. Distinguish Noneditable Fields
        5. 5.3.1.5. Keyboard Shortcuts for Find Boxes
      2. 5.3.2. Open a Form to a New Record
      3. 5.3.3. Refresh Data Automatically
      4. 5.3.4. Hide Foreign Keys
      5. 5.3.5. Find Records Faster
      6. 5.3.6. Handle Items Not in a Combo Box
      7. 5.3.7. One Form, Many Screen Resolutions
      8. 5.3.8. Option Groups Don’t Allow Text Values
      9. 5.3.9. Can’t Change Column Headings for Datasheet View
      10. 5.3.10. Set Focus Doesn’t Work
      11. 5.3.11. Enable Null Values in a Combo Box
      12. 5.3.12. Fix Scrolling in Combo Boxes
      13. 5.3.13. Use Arrow Keys in Combo Boxes
      14. 5.3.14. Speed Up Slow Combo Boxes
        1. 5.3.14.1. Restrict with a Separate Control
        2. 5.3.14.2. Limit Rows with Type-Ahead
      15. 5.3.15. The Limits of Conditional Formatting
      16. 5.3.16. Form Is Blank in Form View
      17. 5.3.17. Edit User-Entered Data
      18. 5.3.18. Can’t Save Changes to Data
      19. 5.3.19. Some Forms Shouldn’t Be Editable by Default
      20. 5.3.20. Too Many Controls on One Page
      21. 5.3.21. Carry Data Forward to the Next Record
  11. 6. Reports, Mailing Labels, and Charts
    1. 6.1. GENERAL REPORT ANNOYANCES
      1. 6.1.1. Report Wizard Isn’t Working Its Magic
      2. 6.1.2. Build a Report from Scratch
      3. 6.1.3. Report Preview Is Too Small
      4. 6.1.4. Unexpected Parameter Boxes
      5. 6.1.5. Calculated Field Shows #Error?
      6. 6.1.6. Truncated Memo Field
      7. 6.1.7. Reports with No Data
      8. 6.1.8. Error 2501: The OpenReport Action Was Canceled
      9. 6.1.9. Filter with Parameters
      10. 6.1.10. Number Records Consecutively
      11. 6.1.11. Export to Word/RTF
      12. 6.1.12. Give Up and Try Excel Instead
    2. 6.2. PAGE LAYOUT AND PRINTING
      1. 6.2.1. Too Much Blank Space
      2. 6.2.2. Every Other Page Is Blank
      3. 6.2.3. Blank Page at End of Report
      4. 6.2.4. Column Layout Issues
      5. 6.2.5. At Least One Printer Required
      6. 6.2.6. Using the Default Printer
    3. 6.3. SORTING, GROUPING, AND SUBREPORTS
      1. 6.3.1. Records Aren’t Sorted Like the Underlying Query
      2. 6.3.2. Orphaned Group Header
      3. 6.3.3. Subreport Loses Its Headings
      4. 6.3.4. Total Not Allowed in Page Footer
      5. 6.3.5. Crosstab Headings Don’t Match
        1. 6.3.5.1. Fixed Column Headings
        2. 6.3.5.2. Dynamic Column Headings
    4. 6.4. MAILING LABELS
      1. 6.4.1. Off to See the Wizard
      2. 6.4.2. Custom Label Templates
      3. 6.4.3. Too Much Label, Not Enough Margin
      4. 6.4.4. Eliminate Blank Lines, and Other Label Tweaks
      5. 6.4.5. Prompt for Trim Function
      6. 6.4.6. Zip+4 Codes Run Together
      7. 6.4.7. More Than One Label for Each Record
      8. 6.4.8. Printing Partial Sheets of Labels
    5. 6.5. CHARTS AND GRAPHS
      1. 6.5.1. Where’s Charting?
      2. 6.5.2. Chart Only Shows Sample Data
      3. 6.5.3. Chart Changes Are Gone
      4. 6.5.4. Incorrect Data in Chart
      5. 6.5.5. Chart Is Blank
      6. 6.5.6. Fixed-Scale Charts
      7. 6.5.7. Control Charts in Code
      8. 6.5.8. Link a Chart from Excel
  12. 7. Expressions, Macros, Code Modules, and Custom Controls
    1. 7.1. EXPRESSIONS
      1. 7.1.1. .Dot, !Bang, and [Bracket]
      2. 7.1.2. Expressions That Go Blank
      3. 7.1.3. Tangled Up in Null
      4. 7.1.4. Debugging Expressions
        1. 7.1.4.1. Rule 1: Simplify, Simplify
        2. 7.1.4.2. Rule 2: Check the Documentation
        3. 7.1.4.3. Rule 3: Examine Your Data
      5. 7.1.5. Mysterious Syntax Errors
      6. 7.1.6. “Quotes”, #Quotes#, and More #%&@!! “"""Quotes"""”
      7. 7.1.7. Like, In, and Between Operators
      8. 7.1.8. “Like” Operator Changes to “Alike”
      9. 7.1.9. Expression-Building Blues
      10. 7.1.10. Dollar$ Sign$ Functions
      11. 7.1.11. [Brackets] Versus “Quotes”
      12. 7.1.12. Last Name, First
      13. 7.1.13. Dates! Dates! Dates!
        1. 7.1.13.1. Define a Date Range (Previous Month, Last Two Weeks, and so on)
        2. 7.1.13.2. Find a Specific Date (Last Day of Month, First Day of Previous Month, and so on)
        3. 7.1.13.3. Find the Elapsed Time
        4. 7.1.13.4. Find Overlapping Date Ranges
      14. 7.1.14. Compare Dates, Ignore Times
      15. 7.1.15. Working Days Not Working
      16. 7.1.16. Refer to Subform Properties
    2. 7.2. MACROS AND CODE MODULES
      1. 7.2.1. Find a Macro Action
      2. 7.2.2. Tame the Visual Basic Editor
        1. 7.2.2.1. Navigating Code and Modules
        2. 7.2.2.2. Getting Help
        3. 7.2.2.3. Debugging
      3. 7.2.3. Find a Visual Basic Function
      4. 7.2.4. Pick the Right Event
      5. 7.2.5. Apostrophe Errors
      6. 7.2.6. Disable Confirmation Dialogs
      7. 7.2.7. Pause Macro or Code
      8. 7.2.8. Better Error Handling
        1. 7.2.8.1. Error Arises in Access
        2. 7.2.8.2. Error Arises in Custom VB Code
      9. 7.2.9. Save a Record
      10. 7.2.10. Lost Data
      11. 7.2.11. Mysterious Syntax Errors, Part Deux
    3. 7.3. CUSTOM CONTROLS AND EXTERNAL APPLICATIONS
      1. 7.3.1. Mysteries of ActiveX Controls
      2. 7.3.2. ActiveX Control Is Missing Events
      3. 7.3.3. Calendar Controls
      4. 7.3.4. File Choosers
        1. 7.3.4.1. Using the Windows API
        2. 7.3.4.2. Using the FileDialog Object
        3. 7.3.4.3. Using the Common Dialog Control
      5. 7.3.5. Use Excel Functions
      6. 7.3.6. Create Email Links
      7. 7.3.7. Send Email from Access
  13. A. Appendix
    1. A.1. Installation Checklist
      1. A.1.1. Keep Windows Up-to-Date
      2. A.1.2. Keep Office Up-to-Date
      3. A.1.3. Keep Jet 4.0 Up-to-Date
      4. A.1.4. Install All Access Features
      5. A.1.5. Re-Register DLLs
      6. A.1.6. Install ODBC Drivers
    2. A.2. Access Newsgroups
    3. A.3. Visual Basic Functions
    4. A.4. Macro Actions
    5. A.5. Events
  14. Glossary
  15. About the Authors
  16. About the Authors
  17. Colophon
  18. Special Upgrade Offer
  19. Copyright