Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by Ken Bluttman
Access Hacks
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 1–12
1. Help Users Find the Objects They Need
1.2.1. The Plain Database Window
1.2.2. Using Groups
2. Personalize Your Access Application
1.3.1. Storing Preferences
1.3.2. Applying the Preferences
1.3.3. Using the Hack
3. Work Fast and Avoid Typos
1.4.1. Know Thy Shortcuts
1.4.2. Remember Where to Reference Shortcuts
4. Optimize Data Changes
5. Transfer Data Between Versions of Access
1.6.1. See Also
6. Organize and Enhance Your Macros
1.7.1. Conditional Macro Actions
1.7.2. Creating Macro Groups
7. Rid Your Database of Clutter
1.8.1. Tracking Object Use
1.8.2. Identifying Unused Objects
1.8.3. Hacking the Hack
8. Protect Valuable Information
1.9.1. But Just in Case
1.9.2. The Code
9. Work with Any Amount of Data
1.10.1. Splitting Up Data
1.10.2. Working with Split Data
10. Find Database Objects in a Snap
11. Use a Junction Table
12. Stop the Database from Growing
2. Tables
2.1. Hacks 13–18
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
14. Copy Data Between Tables Without an Append Query
2.3.1. Appending Across Tables
2.3.2. Appending Across Databases
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
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 19–27
19. Help Users Navigate Through Long Forms
3.2.1. Smart Navigation
3.2.2. The Code
20. Help Users Enter Additional Text
21. Let Users Add Custom Items to Predesigned Lists
3.4.1. The Code
3.4.2. Hacking the 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
23. Use Custom Controls on Your Forms
3.6.1. Adding a Custom Control to a Form
3.6.2. See Also
24. Confirm Record Updates Before Saving
25. Put a Clock on a Form
3.8.1. Creating the Clock
3.8.2. Hacking the Hack
26. Be Tab-Smart
27. Highlight the Active Control
4. Presentation
4.1. Hacks 28–39
28. Separate Alphabetically Sorted Records into Letter Groups
4.2.1. Segregating by Letter
4.2.2. Hacking the Hack
29. Create Conditional Subtotals
4.3.1. Using Running Sums
4.3.2. Hacking the Hack
4.3.3. See Also
30. Use Conditional Formatting to Point Out Important Results
4.4.1. Standard Conditional Formatting
4.4.2. Conditional Formatting the VBA Way
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
32. Protect Intellectual Property
4.6.1. Making the Watermark
4.6.2. Using the Watermark
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
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
35. View Reports Embedded in Forms
4.9.1. Creating the Form
4.9.2. Hacking the Hack
36. Put Line Numbers on a Report
37. Shade Alternating Lines on a Report
4.11.1. The Code
4.11.2. The Results
4.11.3. Hacking the Hack
38. Save Paper by Reducing Whitespace
39. Include the Date, Time, and Page Count
5. Queries and SQL
5.1. Hacks 40–54
40. Return a Sample of Records
5.2.1. Using the Top Predicate
5.2.2. Hacking the 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
42. Find Unmatched Records on Multiple Field Keys
5.4.1. Reviewing the Query
5.4.2. Changing the Query
43. Place a Grand Total in a Query
5.5.1. Hacking the 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
45. Summarize Complex Data
5.7.1. Introducing the Crosstab
5.7.2. See Also
46. Get All Combinations of Data
47. Don’t 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.
48. Use a Custom Function in a Query
5.10.1. Creating a New Function
5.10.2. Manipulating Dates
49. Create Access Tables with SQL Server Scripts
5.11.1. Walking Through Enterprise Manager
5.11.2. See Also
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
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 55–58
55. Test for Duplication
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
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.
58. Implement Unique Usernames
7. External Programs and Data
7.1. Hacks 59–71
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
60. Use Excel to Reorient Access Data
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
62. Use Word to Compare Data in Two Access Tables
63. Import Varied XML Data into Access
7.6.1. See Also
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
65. Break Through VBA’s Transformation Barrier
7.8.1. See Also
66. Leverage SQL Server Power by Calling Stored Procedures
7.9.1. Hooking Up with ODBC
7.9.2. Creating a Pass-Through Query
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
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
69. Send Access Data Through Outlook Automatically
7.12.1. The Code
7.12.2. An Easier Way
7.12.3. Macros in Outlook
70. Create Access Tables from Outside Access
7.13.1. The Code
7.13.2. Hacking the Hack
71. Write VBA with the Macro Recorder in Word and Excel
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
8.4.1. The Code
8.4.2. Boiling Down the Code
8.4.3. Domain 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
8.8.1. The Code
79. Help Users Drill Down to a Record
80. Prevent Users from Disabling Your Startup Options
8.10.1. Access MDB
8.10.2. Access ADP
8.10.3. Be Careful
81. Inform Users of a Long Process
8.11.1. The Code
8.11.2. Hacking the Hack
82. Allow Users to Choose a Back-End Database
83. Override the Timeout Interval
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
85. Sort Records Randomly
86. Bulk-Update Controls on a Form
8.16.1. Accessing Control Properties in Code
8.16.2. Changing Properties the Easy Way
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
88. Use Custom Enumerations
89. Convert Text to the Desired Case
8.19.1. The Code
8.19.2. Running the Code
90. Create a Code Library
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 92–95
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
93. Build an Application Shell with EZ Application Generator
94. Load Your Database with Test Data
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 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
10.5.1. Placing the Web Browser Control on a Form
10.5.2. Uploading Files
100. Use a Smart Tag to Open a Web Page
About the Author
Colophon
Copyright
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Access Hacks
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset