- 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
About the Authors
Phil Mitchell is an Access wiz and instructor. He's been a software developer for a dozen years, focusing on data modeling and developing database applications. He also has a Ph.D. in cognitive psychology and a passion for design usability.
Evan Callahan is President of Callahan Software Solutions, a consultancy focusing on database and other software solutions and technical communications. Clients have included Microsoft, General Dynamics, Online Training Solutions, and a variety of accounting, real estate, and environmental firms. He is the author of "Troubleshooting Your Web Page" and "Microsoft Access Visual Basic for Applications Step by Step", published by Microsoft Press. Evan worked for Microsoft Corporation from 1989 to 1995, where he created documentation and sample applications for Microsoft Access and Visual Basic. His background includes extensive experience in database design, technical communications, web-based marketing, and management.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.