Access Hacks

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?;
Chapter 1: Core Access;
1.1 Hacks 1–12;
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;
2.1 Hacks 13–18;
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;
3.1 Hacks 19–27;
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;
4.1 Hacks 28–39;
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;
5.1 Hacks 40–54;
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;
6.1 Hacks 55–58;
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;
7.1 Hacks 59–71;
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;
8.1 Hacks 72–91;
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;
9.1 Hacks 92–95;
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;
10.1 Hacks 96–100;
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;
Colophon;