Access Hacks

Ken Bluttman has been developing custom business applications for PC platforms since the 1980s. He worked for more than a decade as a contract programmer specializing in Microsoft Office products and has worked with Microsoft Access since version 1 was released. Ken also has experience as an Office/VBA specialist for companies ranging from small businesses to Fortune 500 firms, developing solutions that combine Office with Oracle and SQL Server as back ends. For the last few years, he's worked on XML and web-based solutions in addition to traditional client/server applications.

• Credits; Preface; Chapter 1. Core Access; 1. Help Users Find the Objects They Need; 2. Personalize Your Access Application; 3. Work Fast and Avoid Typos; 4. Optimize Data Changes; 5. Transfer Data Between Versions of Access; 6. Organize and Enhance Your Macros; 7. Rid Your Database of Clutter; 8. Protect Valuable Information; 9. Work with Any Amount of Data; 10. Find Database Objects in a Snap; 11. Use a Junction Table; 12. Stop the Database from Growing; Chapter 2. Tables; 13. Create an AutoNumber Field with a Custom Value; 14. Copy Data Between Tables Without an Append Query; 15. Steer Clear of System Tables; 16. Hide Sensitive Information; 17. Simulate Table Triggers; 18. Create Tables Faster; Chapter 3. Entry and Navigation; 19. Help Users Navigate Through Long Forms; 20. Help Users Enter Additional Text; 21. Let Users Add Custom Items to Predesigned Lists; 22. Populate and Sort Lists with Flair; 23. Use Custom Controls on Your Forms; 24. Confirm Record Updates Before Saving; 25. Put a Clock on a Form; 26. Be Tab-Smart; 27. Highlight the Active Control; Chapter 4. Presentation; 28. Separate Alphabetically Sorted Records into Letter Groups; 29. Create Conditional Subtotals; 30. Use Conditional Formatting to Point Out Important Results; 31. Provide a Direct Link to a Report; 32. Protect Intellectual Property; 33. Create a Slideshow in Access; 34. Play Videos in Access Forms; 35. View Reports Embedded in Forms; 36. Put Line Numbers on a Report; 37. Shade Alternating Lines on a Report; 38. Save Paper by Reducing Whitespace; 39. Include the Date, Time, and Page Count; Chapter 5. Queries and SQL; 40. Return a Sample of Records; 41. Create Bulletproof Insert Operations; 42. Find Unmatched Records on Multiple Field Keys; 43. Place a Grand Total in a Query; 44. Sort Any Arbitrary String of Characters; 45. Summarize Complex Data; 46. Get All Combinations of Data; 47. Don't Let Nulls Ruin Data Summaries; 48. Use a Custom Function in a Query; 49. Create Access Tables with SQL Server Scripts; 50. Use Wildcards in Queries; 51. Get Cleaner Or-Based Criteria; 52. Get Cleaner And-Based Criteria; 53. Create an Outer Join; 54. Use Regular Expressions in Access Queries; Chapter 6. Multiuser Issues; 55. Test for Duplication; 56. Distribute a Split Database with Predefined Table Links; 57. Build a Time-Out Feature; 58. Implement Unique Usernames; Chapter 7. External Programs and Data; 59. Import Noncontiguous Ranges of Data from Excel; 60. Use Excel to Reorient Access Data; 61. Use Excel Functions Inside Access; 62. Use Word to Compare Data in Two Access Tables; 63. Import Varied XML Data into Access; 64. Export XML Data Sanely; 65. Break Through VBA's Transformation Barrier; 66. Leverage SQL Server Power by Calling Stored Procedures; 67. Manage Word Documents from Access; 68. Use Access as a Front End to MySQL; 69. Send Access Data Through Outlook Automatically; 70. Create Access Tables from Outside Access; 71. Write VBA with the Macro Recorder in Word and Excel; Chapter 8. Programming; 72. Store Initial Control Selections for Later Recall; 73. Write Code Faster by Turning Off Syntax-Checking; 74. Substitute Domain Aggregate Functions for SQL Aggregate Functions; 75. Shrink Your Code with Subroutines; 76. Shrink Your Code with Optional Arguments; 77. Protect Programming Code from Curious Users; 78. Build a Secret Developer Backdoor into Your Applications; 79. Help Users Drill Down to a Record; 80. Prevent Users from Disabling Your Startup Options; 81. Inform Users of a Long Process; 82. Allow Users to Choose a Back-End Database; 83. Override the Timeout Interval; 84. Save Values from Unbound Controls for Later Recall; 85. Sort Records Randomly; 86. Bulk-Update Controls on a Form; 87. Provide Complete XML Control to Any Version of Access; 88. Use Custom Enumerations; 89. Convert Text to the Desired Case; 90. Create a Code Library; 91. Automatically Check for Database Table Updates; Chapter 9. Third-Party Applications; 92. Document Your Database with Total Access Analyzer; 93. Build an Application Shell with EZ Application Generator; 94. Load Your Database with Test Data; 95. Use Access as an XML Database; Chapter 10. The Internet; 96. Export a Report as HTML; 97. Use a Browser Inside Access; 98. Pull the HTML Source Code from a Web Site; 99. Download Files Using the Web Browser Control; 100. Use a Smart Tag to Open a Web Page; Index