Access Hacks. Tips & Tools for Wrangling Your Data

Spis treści książki

• Access Hacks
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • A Note Regarding Supplemental Files
  • Copyright
  • Credits
    • About the Author
    • Contributors
    • Acknowledgments
  • Preface
    • Why Access Hacks?
    • How to Use This Book
    • How This Book Is Organized
    • Conventions Used in This Book
    • Using Code Examples
    • Safari Enabled
    • How to Contact Us
    • Got a Hack?
  • 1. Core Access
    • 1.1. Hacks 112
    • Hack #1. Help Users Find the Objects They Need
      • 1.2.1. The Plain Database Window
      • 1.2.2. Using Groups
    • Hack #2. Personalize Your Access Application
      • 1.3.1. Storing Preferences
      • 1.3.2. Applying the Preferences
      • 1.3.3. Using the Hack
    • Hack #3. Work Fast and Avoid Typos
      • 1.4.1. Know Thy Shortcuts
      • 1.4.2. Remember Where to Reference Shortcuts
    • Hack #4. Optimize Data Changes
    • Hack #5. Transfer Data Between Versions of Access
      • 1.6.1. See Also
    • Hack #6. Organize and Enhance Your Macros
      • 1.7.1. Conditional Macro Actions
      • 1.7.2. Creating Macro Groups
    • Hack #7. Rid Your Database of Clutter
      • 1.8.1. Tracking Object Use
      • 1.8.2. Identifying Unused Objects
      • 1.8.3. Hacking the Hack
    • Hack #8. Protect Valuable Information
      • 1.9.1. But Just in Case
      • 1.9.2. The Code
    • Hack #9. Work with Any Amount of Data
      • 1.10.1. Splitting Up Data
      • 1.10.2. Working with Split Data
    • Hack #10. Find Database Objects in a Snap
    • Hack #11. Use a Junction Table
    • Hack #12. Stop the Database from Growing
  • 2. Tables
    • 2.1. Hacks 1318
    • Hack #13. Create an AutoNumber Field with a Custom Value
      • 2.2.1. Seeding AutoNumber with a Number of Your Choice
      • 2.2.2. Hacking the Hack
    • Hack #14. Copy Data Between Tables Without an Append Query
      • 2.3.1. Appending Across Tables
      • 2.3.2. Appending Across Databases
    • Hack #15. Steer Clear of System Tables
      • 2.4.1. The Code
      • 2.4.2. Running the Code
    • 16. Hide Sensitive Information
      • 2.5.1. An Alternative
      • 2.5.2. Hacking the Hack
    • Hack #17. Simulate Table Triggers
      • 2.6.1. Setting Up an Audit Log
      • 2.6.2. Checking Out the Form Events
      • 2.6.3. The Code
      • 2.6.4. Running the Code
      • 2.6.5. Hacking the Hack
    • 18. Create Tables Faster
      • 2.7.1. Setting Default Values
      • 2.7.2. The Code
  • 3. Entry and Navigation
    • 3.1. Hacks 1927
    • Hack #19. Help Users Navigate Through Long Forms
      • 3.2.1. Smart Navigation
      • 3.2.2. The Code
    • Hack #20. Help Users Enter Additional Text
    • Hack #21. Let Users Add Custom Items to Predesigned Lists
      • 3.4.1. The Code
      • 3.4.2. Hacking the Hack
    • Hack #22. Populate and Sort Lists with Flair
      • 3.5.1. The Form
      • 3.5.2. Populating a Listbox Alphabetically from Two Sources
      • 3.5.3. Controlling the Sort in a Listbox Populated from Multiple Sources
      • 3.5.4. Sorting List Items by Popularity
    • Hack #23. Use Custom Controls on Your Forms
      • 3.6.1. Adding a Custom Control to a Form
      • 3.6.2. See Also
    • Hack #24. Confirm Record Updates Before Saving
    • Hack #25. Put a Clock on a Form
      • 3.8.1. Creating the Clock
      • 3.8.2. Hacking the Hack
    • Hack #26. Be Tab-Smart
    • Hack #27. Highlight the Active Control
  • 4. Presentation
    • 4.1. Hacks 2839
    • Hack #28. Separate Alphabetically Sorted Records into Letter Groups
      • 4.2.1. Segregating by Letter
      • 4.2.2. Hacking the Hack
    • Hack #29. Create Conditional Subtotals
      • 4.3.1. Using Running Sums
      • 4.3.2. Hacking the Hack
      • 4.3.3. See Also
    • Hack #30. Use Conditional Formatting to Point Out Important Results
      • 4.4.1. Standard Conditional Formatting
      • 4.4.2. Conditional Formatting the VBA Way
    • Hack #31. Provide a Direct Link to a Report
      • 4.5.1. Creating a Shortcut
      • 4.5.2. Printing a Report and Closing the Database in One Click
    • Hack #32. Protect Intellectual Property
      • 4.6.1. Making the Watermark
      • 4.6.2. Using the Watermark
    • Hack #33. Create a Slideshow in Access
      • 4.7.1. The Graphics
      • 4.7.2. The Form Design
      • 4.7.3. The Code
      • 4.7.4. Hacking the Hack
    • Hack #34. Play Videos in Access Forms
      • 4.8.1. Putting the Player on the Form
      • 4.8.2. Playing a Movie
      • 4.8.3. See Also
    • Hack #35. View Reports Embedded in Forms
      • 4.9.1. Creating the Form
      • 4.9.2. Hacking the Hack
    • Hack #36. Put Line Numbers on a Report
    • Hack #37. Shade Alternating Lines on a Report
      • 4.11.1. The Code
      • 4.11.2. The Results
      • 4.11.3. Hacking the Hack
    • Hack #38. Save Paper by Reducing Whitespace
    • Hack #39. Include the Date, Time, and Page Count
  • 5. Queries and SQL
    • 5.1. Hacks 4054
    • Hack #40. Return a Sample of Records
      • 5.2.1. Using the Top Predicate
      • 5.2.2. Hacking the Hack
    • Hack #41. Create Bulletproof Insert Operations
      • 5.3.1. Handling Excessive Text Length
      • 5.3.2. Watching Out for Apostrophes
      • 5.3.3. Combining the Two Validations
    • Hack #42. Find Unmatched Records on Multiple Field Keys
      • 5.4.1. Reviewing the Query
      • 5.4.2. Changing the Query
    • Hack #43. Place a Grand Total in a Query
      • 5.5.1. Hacking the Hack
    • Hack #44. Sort Any Arbitrary String of Characters
      • 5.6.1. Sorting in the Middle
      • 5.6.2. Sorting on Mixed Fixed Positions
      • 5.6.3. Sorting on Characters When Their Position Is Unknown
      • 5.6.4. Hacking the Hack
      • 5.6.5. See Also
    • Hack #45. Summarize Complex Data
      • 5.7.1. Introducing the Crosstab
      • 5.7.2. See Also
    • Hack #46. Get All Combinations of Data
    • Hack #47. Dont Let Nulls Ruin Data Summaries
      • 5.9.1. Nulls in Number Fields
      • 5.9.2. Preventing Nulls
        • 5.9.2.1. Table design to prevent nulls and zero-length strings.
        • 5.9.2.2. Form design to prevent nulls and zero-length strings.
    • Hack #48. Use a Custom Function in a Query
      • 5.10.1. Creating a New Function
      • 5.10.2. Manipulating Dates
    • Hack #49. Create Access Tables with SQL Server Scripts
      • 5.11.1. Walking Through Enterprise Manager
      • 5.11.2. See Also
    • Hack #50. Use Wildcards in Queries
    • Hack #51. Get Cleaner Or-Based Criteria
    • Hack #52. Get Cleaner And-Based Criteria
    • Hack #53. Create an Outer Join
    • Hack #54. Use Regular Expressions in Access Queries
      • 5.16.1. Creating the Custom Function
      • 5.16.2. Creating an Example Query
      • 5.16.3. Hacking the Hack
  • 6. Multiuser Issues
    • 6.1. Hacks 5558
    • Hack #55. Test for Duplication
    • Hack #56. Distribute a Split Database with Predefined Table Links
      • 6.3.1. Copying the Network Drive to Your Development Machine
      • 6.3.2. Using UNC Instead
    • Hack #57. Build a Time-Out Feature
      • 6.4.1. It's About Time
      • 6.4.2. In Good Form
      • 6.4.3. Hacking the Hack
        • 6.4.3.1. Reset the time when the mouse is moved.
        • 6.4.3.2. Let the user decide the timer interval.
        • 6.4.3.3. Save the record but leave the form open.
        • 6.4.3.4. Close the form without saving the record.
    • Hack #58. Implement Unique Usernames
  • 7. External Programs and Data
    • 7.1. Hacks 5971
    • Hack #59. Import Noncontiguous Ranges of Data from Excel
      • 7.2.1. Using Macros for Multiple Imports
        • 7.2.1.1. Import Excel data into separate tables
        • 7.2.1.2. Import Excel data into a single table
      • 7.2.2. Importing Noncontiguous Data from Excel Without Using Ranges
    • Hack #60. Use Excel to Reorient Access Data
    • Hack #61. Use Excel Functions Inside Access
      • 7.4.1. A Simple Excel Function
      • 7.4.2. An Excel Function with an Array Parameter
      • 7.4.3. Other Excel Spreadsheet Functions
    • Hack #62. Use Word to Compare Data in Two Access Tables
    • Hack #63. Import Varied XML Data into Access
      • 7.6.1. See Also
    • Hack #64. Export XML Data Sanely
      • 7.7.1. Exporting from Tables in a One-to-Many Relationship
      • 7.7.2. Exporting from Tables in a Many-to-Many Relationship
      • 7.7.3. Using a Query to Tame the Export
      • 7.7.4. See Also
    • Hack #65. Break Through VBA's Transformation Barrier
      • 7.8.1. See Also
    • Hack #66. Leverage SQL Server Power by Calling Stored Procedures
      • 7.9.1. Hooking Up with ODBC
      • 7.9.2. Creating a Pass-Through Query
    • Hack #67. Manage Word Documents from Access
      • 7.10.1. Hooking into Word
      • 7.10.2. The Code
      • 7.10.3. The Data Has Landed Intact
    • Hack #68. Use Access as a Front End to MySQL
      • 7.11.1. Installing the MySQL Tools
      • 7.11.2. Linking to MySQL Tables
      • 7.11.3. Hacking the Hack
      • 7.11.4. See Also
    • Hack #69. Send Access Data Through Outlook Automatically
      • 7.12.1. The Code
      • 7.12.2. An Easier Way
      • 7.12.3. Macros in Outlook
    • Hack #70. Create Access Tables from Outside Access
      • 7.13.1. The Code
      • 7.13.2. Hacking the Hack
    • Hack #71. Write VBA with the Macro Recorder in Word and Excel
  • 8. Programming
    • 8.1. Hacks 7291
    • Hack #72. Store Initial Control Selections for Later Recall
    • Hack #73. Write Code Faster by Turning Off Syntax-Checking
    • Hack #74. Substitute Domain Aggregate Functions for SQL Aggregate Functions
      • 8.4.1. The Code
      • 8.4.2. Boiling Down the Code
      • 8.4.3. Domain Aggregate Functions
    • Hack #75. Shrink Your Code with Subroutines
    • Hack #76. Shrink Your Code with Optional Arguments
    • Hack #77. Protect Programming Code from Curious Users
    • Hack #78. Build a Secret Developer Backdoor into Your Applications
      • 8.8.1. The Code
    • Hack #79. Help Users Drill Down to a Record
    • Hack #80. Prevent Users from Disabling Your Startup Options
      • 8.10.1. Access MDB
      • 8.10.2. Access ADP
      • 8.10.3. Be Careful
    • Hack #81. Inform Users of a Long Process
      • 8.11.1. The Code
      • 8.11.2. Hacking the Hack
    • Hack #82. Allow Users to Choose a Back-End Database
    • Hack #83. Override the Timeout Interval
    • Hack #84. Save Values from Unbound Controls for Later Recall
      • 8.14.1. The Code
      • 8.14.2. Running the Code
      • 8.14.3. Hacking the Hack
    • Hack #85. Sort Records Randomly
    • Hack #86. Bulk-Update Controls on a Form
      • 8.16.1. Accessing Control Properties in Code
      • 8.16.2. Changing Properties the Easy Way
    • Hack #87. Provide Complete XML Control to Any Version of Access
      • 8.17.1. Referencing the Parser
      • 8.17.2. The Code
      • 8.17.3. See Also
    • Hack #88. Use Custom Enumerations
    • Hack #89. Convert Text to the Desired Case
      • 8.19.1. The Code
      • 8.19.2. Running the Code
    • Hack #90. Create a Code Library
    • Hack #91. Automatically Check for Database Table Updates
      • 8.21.1. Running Code at Startup
      • 8.21.2. The Code
      • 8.21.3. Running the Code
  • 9. Third-Party Applications
    • 9.1. Hacks 9295
    • Hack #92. Document Your Database with Total Access Analyzer
      • 9.2.1. Running the Analyzer
      • 9.2.2. Viewing the Documentation
      • 9.2.3. Errors and Suggestions
    • Hack #93. Build an Application Shell with EZ Application Generator
    • Hack #94. Load Your Database with Test Data
    • Hack #95. Use Access as an XML Database
      • 9.5.1. The Code
      • 9.5.2. Loading the XML File
      • 9.5.3. Browsing Records
      • 9.5.4. Updating a Record
      • 9.5.5. Deleting a Record
      • 9.5.6. Adding a New Record
      • 9.5.7. See Also
  • 10. The Internet
    • 10.1. Hacks 96100
    • Hack #96. Export a Report as HTML
    • Hack #97. Use a Browser Inside Access
    • Hack #98. Pull the HTML Source Code from a Web Site
    • Hack #99. Download Files Using the Web Browser Control
      • 10.5.1. Placing the Web Browser Control on a Form
      • 10.5.2. Uploading Files
    • Hack #100. Use a Smart Tag to Open a Web Page
  • About the Author
  • Colophon
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje