Access Hacks

Access Hacks

by Ken Bluttman
Released April 2005
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596552954

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

As part of the Microsoft Office suite, Access has become the industry's leading desktop database management program for organizing, accessing, and sharing information. But taking advantage of this product to build increasingly complex Access applications requires something more than your typical how-to book. What it calls for is Access Hacks from O'Reilly.This valuable guide provides direct, hands-on solutions that can help relieve the frustrations felt by users struggling to master the program's various complexities. For experienced users, Access Hacks offers a unique collection of proven techniques and tools that enable them to take their database skills and productivity to the next level. For Access beginners, it helps them acquire a firm grasp of the program's most productive features.A smart collection of insider tips and tricks, Access Hacks covers all of the program's finer points. Among the multitude of topics addressed, it shows users how to:

  • work with Access in multi-user environments
  • utilize SQL queries
  • work with external data and programs
  • integrate Access with third-party products
Just imagine: a learning process without the angst. Well, Access Hacks delivers it with ease, thanks to these down-and-dirty techniques not collected together anywhere else.Part of O'Reilly's best-selling Hacks series, Access Hacks is based on author Ken Bluttman's two decades of real-world experience in database programming and business application building. It's because of his vast experiences that the book is able to offer such a deep understanding of the program's expanding possibilities.

Publisher resources

View/Submit Errata

Table of contents

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

Product information

  • Title: Access Hacks
  • Author(s): Ken Bluttman
  • Release date: April 2005
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596552954