Fixing Access Annoyances. How to Fix the Most Annoying Things About Your Favorite Database (ebook)(audiobook)(audiobook)

Spis treści

• Fixing Access Annoyances
• Copyright
• Dedication
• Introduction
  • How to Use This Book
  • Access Versions
  • MSKB 123456
  • Conventions Used in This Book
  • Using Code Examples
  • I Did Exactly What You Said, and It Still Doesnt Work!
  • Safari Enabled
  • Acknowledgments
• 0. Access Basics
  • 0.1. The Database Window
  • 0.2. Wizards
  • 0.3. Design View and User Views
  • 0.4. Setting Properties
  • 0.5. Bound Versus Unbound Objects
  • 0.6. Multi-User Databases and Split Design
  • 0.7. How to Create an Event Procedure
  • 0.8. How to Add Code to Your Application
  • 0.9. DAO Versus ADO
• 1. General Annoyances
  • 1.1. CUSTOMIZING ACCESS
    • 1.1.1. Accesss Bad Defaults
      • 1.1.1.1. Global Defaults
      • 1.1.1.2. Specific 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 Isnt/Is Helpful
      • 1.1.13.1. Newsgroups
      • 1.1.13.2. Utter Access
      • 1.1.13.3. Expert Sites
      • 1.1.13.4. The Microsoft Knowledge Base
      • 1.1.13.5. Microsoft Developer Network (MSDN)
    • 1.1.14. Give Me Full Menus!
    • 1.1.15. Missing or Mangled Toolbars and Menus
      • 1.1.15.1. Toolbar/Menu Is Missing
      • 1.1.15.2. Toolbar/Menu Has Been Altered
      • 1.1.15.3. Toolbar/Menu Has Been Moved or Merged into Another One
      • 1.1.15.4. Toolbar Is Disabled at Startup
    • 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.2.1. Access Is Buggy and Unstable
    • 1.2.2. Flaky or Corrupted Database
      • 1.2.2.1. Preparation
      • 1.2.2.2. Use the Built-in Compact/Repair Utility
      • 1.2.2.3. Export to a New Database
      • 1.2.2.4. Problem-Specific Suggestions
      • 1.2.2.5. Professional Help
    • 1.2.3. That Darn Corruption
      • 1.2.3.1. Network Problems
      • 1.2.3.2. System Crashes
      • 1.2.3.3. Access Bugs
      • 1.2.3.4. Application Bugs
      • 1.2.3.5. User Error
    • 1.2.4. Missing Wizards or Import/Export/Link Options
      • 1.2.4.1. Missing or Broken Wizards
      • 1.2.4.2. Missing Import/Export/Link Options
    • 1.2.5. Agonies of a Sick Installation
  • 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
• 2. Performance, Versions, Security, and Deployment
  • 2.1. PERFORMANCE
    • 2.1.1. Database Bloat from Images or OLE Objects
    • 2.1.2. Why Is My Database So Slow?
      • 2.1.2.1. Name AutoCorrect and Subdatasheet Name
      • 2.1.2.2. Queries
      • 2.1.2.3. Reports
      • 2.1.2.4. Forms
      • 2.1.2.5. Functions
    • 2.1.3. Why Is My Split Database So Slow?
      • 2.1.3.1. Maintain a Persistent Connection to the Backend
      • 2.1.3.2. Make the Backend Easy to Get To
      • 2.1.3.3. Check Your Anti-Virus Software
    • 2.1.4. XP Home Is Way Slow
    • 2.1.5. Access with SQL Server Is Still Slow
  • 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.3.1. Simple Security
      • 2.3.3.2. User-Level Security
      • 2.3.3.3. Missing or Corrupted Workgroup File
      • 2.3.3.4. Third-Party Products/Services
    • 2.3.4. Administering User-Level Security Correctly
      • 2.3.4.1. Create a New Workgroup File
      • 2.3.4.2. Create a New Database Administrator
      • 2.3.4.3. Create a New, Secured Database
      • 2.3.4.4. Create Custom Groups
      • 2.3.4.5. Assign Permissions to Groups
      • 2.3.4.6. Create Users
      • 2.3.4.7. Restore the Default Workgroup (Optional)
    • 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.3.1. Minimize Network Connections
      • 2.4.3.2. Optimize Your Design
      • 2.4.3.3. Hardware Considerations
      • 2.4.3.4. Oplocks
    • 2.4.4. Getting Around Jets 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. Cant 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 Dont 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. Cant Create a Relationship
    • 3.1.8. Cant Delete Records
    • 3.1.9. Default Values Dont Apply to Existing Records
    • 3.1.10. Simple Validation Rules
      • 3.1.10.1. The Like Operator
      • 3.1.10.2. The Between Operator
      • 3.1.10.3. The In Operator
      • 3.1.10.4. Record-Level 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. Cant 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.13.1. Static and Read-Only
      • 3.2.13.2. Dynamic and Read-Only
      • 3.2.13.3. Dynamic and Editable
    • 3.2.14. Automating Import/Export
    • 3.2.15. Exporting Data on the Fly
• 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 Doesnt 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.3.1. Parameter Queries with Wildcards
    • 4.3.2. Parameter Queries and Blank Responses
    • 4.3.3. User-Friendly Parameter Queries
    • 4.3.4. Parameter Queries That Accept Lists
    • 4.3.5. Parameters in Crosstab Queries
    • 4.3.6. Sorting Crosstab Rows Based on Totals
    • 4.3.7. Crosstab Queries with Multiple Values
  • 4.4. TECHNIQUES FOR DIFFICULT QUERIES
    • 4.4.1. Comparing Different Rows
    • 4.4.2. Working with Hierarchies
    • 4.4.3. Working with Ranges
    • 4.4.4. Full Outer Joins
    • 4.4.5. Divide and Conquer with Subqueries
    • 4.4.6. Finding Rows That Dont Exist
    • 4.4.7. Working with Calendar Dates
    • 4.4.8. Missing SQL
• 5. Forms
  • 5.1. WORKING IN DESIGN VIEW
    • 5.1.1. Activating the Wizards
    • 5.1.2. Edit Many Controls at Once
    • 5.1.3. Leaving the Properties Sheet Open
    • 5.1.4. Where Are the Forms Properties?
    • 5.1.5. Attached Labels
    • 5.1.6. Fix Combo and List Box Names
    • 5.1.7. Subform Is Blank in Design View
    • 5.1.8. Form Opens Slowly in Design View
  • 5.2. FORM DESIGN
    • 5.2.1. Building the Right Form
      • 5.2.1.1. Unrelated Table
      • 5.2.1.2. One-to-Many
      • 5.2.1.3. One-to-Many-to-Many
      • 5.2.1.4. Many-to-One
      • 5.2.1.5. Many-to-Many
    • 5.2.2. Synchronizing Subforms
    • 5.2.3. Synchronizing Two Forms
    • 5.2.4. Create Dialog Box Input Forms
    • 5.2.5. Display Subform Values on the Main Form
    • 5.2.6. Hide an Empty Subform
    • 5.2.7. Reusing Subforms
  • 5.3. CREATE FORMS YOUR USERS WILL LOVE
    • 5.3.1. Simplify Data Entry
      • 5.3.1.1. Autofill Dates
      • 5.3.1.2. Autofill Related Fields
      • 5.3.1.3. Define the Tab Order
      • 5.3.1.4. Distinguish Noneditable Fields
      • 5.3.1.5. Keyboard Shortcuts for Find Boxes
    • 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 Dont Allow Text Values
    • 5.3.9. Cant Change Column Headings for Datasheet View
    • 5.3.10. Set Focus Doesnt 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.14.1. Restrict with a Separate Control
      • 5.3.14.2. Limit Rows with Type-Ahead
    • 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. Cant Save Changes to Data
    • 5.3.19. Some Forms Shouldnt 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 Isnt 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.2.1. Too Much Blank Space
    • 6.2.2. Every Other Page Is Blank
    • 6.2.3. Blank Page at End of Report
    • 6.2.4. Column Layout Issues
    • 6.2.5. At Least One Printer Required
    • 6.2.6. Using the Default Printer
  • 6.3. SORTING, GROUPING, AND SUBREPORTS
    • 6.3.1. Records Arent Sorted Like the Underlying Query
    • 6.3.2. Orphaned Group Header
    • 6.3.3. Subreport Loses Its Headings
    • 6.3.4. Total Not Allowed in Page Footer
    • 6.3.5. Crosstab Headings Dont Match
      • 6.3.5.1. Fixed Column Headings
      • 6.3.5.2. Dynamic Column Headings
  • 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.5.1. Wheres Charting?
    • 6.5.2. Chart Only Shows Sample Data
    • 6.5.3. Chart Changes Are Gone
    • 6.5.4. Incorrect Data in Chart
    • 6.5.5. Chart Is Blank
    • 6.5.6. Fixed-Scale Charts
    • 6.5.7. Control Charts in Code
    • 6.5.8. Link a Chart from Excel
• 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.4.1. Rule 1: Simplify, Simplify
      • 7.1.4.2. Rule 2: Check the Documentation
      • 7.1.4.3. Rule 3: Examine Your Data
    • 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.13.1. Define a Date Range (Previous Month, Last Two Weeks, and so on)
      • 7.1.13.2. Find a Specific Date (Last Day of Month, First Day of Previous Month, and so on)
      • 7.1.13.3. Find the Elapsed Time
      • 7.1.13.4. Find Overlapping Date Ranges
    • 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.2.1. Navigating Code and Modules
      • 7.2.2.2. Getting Help
      • 7.2.2.3. Debugging
    • 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.8.1. Error Arises in Access
      • 7.2.8.2. Error Arises in Custom VB Code
    • 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.3.1. Mysteries of ActiveX Controls
    • 7.3.2. ActiveX Control Is Missing Events
    • 7.3.3. Calendar Controls
    • 7.3.4. File Choosers
      • 7.3.4.1. Using the Windows API
      • 7.3.4.2. Using the FileDialog Object
      • 7.3.4.3. Using the Common Dialog Control
    • 7.3.5. Use Excel Functions
    • 7.3.6. Create Email Links
    • 7.3.7. Send Email from Access
• A. Appendix
  • A.1. Installation Checklist
    • A.1.1. Keep Windows Up-to-Date
    • A.1.2. Keep Office Up-to-Date
    • A.1.3. Keep Jet 4.0 Up-to-Date
    • A.1.4. Install All Access Features
    • A.1.5. Re-Register DLLs
    • A.1.6. Install ODBC Drivers
  • A.2. Access Newsgroups
  • A.3. Visual Basic Functions
  • A.4. Macro Actions
  • A.5. Events
• Glossary
• About the Authors
• About the Authors
• Colophon
• Copyright

pokaż cały spis treści

ukryj recenzje