Access Hacks

Larger Cover

Access Hacks

Tips & Tools for Wrangling Your Data

By Ken Bluttman

Publisher: O'Reilly Media

Released: April 2005

Pages: 352

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.

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

Colophon

Title:

Access Hacks

By:

Ken Bluttman

Publisher:

O'Reilly Media

Formats:

Print
Ebook
Safari Books Online

Print:

April 2005

Ebook:

February 2009

Pages:

352

Print ISBN:

978-0-596-00924-3

| ISBN 10:

0-596-00924-0

Ebook ISBN:

978-0-596-10498-6

| ISBN 10:

0-596-10498-7

Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The tool on the cover of Access Hacks is a flour sifter. Ever since humans first produced flour, they have constructed sifters to refine it. The Vikings used round, cup-shaped sieves utilizing horsehair fiber. In preindustrial times,whole wheat was ground in a mill,then sifted through successively finer bolting cloths to get various grades of flour, from dark whole-wheat to almost white for the wealthy.The bran removed while bolting was used by the miller to feed his livestock or was sold to others as feed.

Something similar to this process is still used in modern mills. Wheat first gets broken and separated by plain sifters and purifiers--two advanced sifting mechanisms. During this process the course outer bran skins are sifted from the inner white portions, called endosperm or semolina. Gradually the semolina is milled down into smooth, powdery flour. The clean bran, wheat feed, and flour are each collected in separate channels by a large number of different machines. No hand even touches the wheat until it leaves the mill.

Today flour is generally presifted, but sifting with a hand rotary crank or electric sifter is recommended before baking to remove lumps and further aerate flour, making it livelier for kneading. Mary Anne Weeks Mayo was the production editor and proofreader, and Audrey Doyle was the copyeditor for Access Hacks. Darren Kelly provided quality control. Johnna Dinse wrote the index.

Hanna Dyer designed the cover of this book, based on a series design by Edie Freedman. The cover image is an original photograph by PhotoSpin Power Photos. Karen Montgomery produced the cover layout with Adobe InDesign CS using Adobe's Helvetica Neue and ITC Garamond fonts.

David Futato designed the interior layout. This book was converted by Keith Fahlgren to FrameMaker 5.5.6 with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The text font is Linotype Birka; the heading font is Adobe Helvetica Neue Condensed; and the code font is LucasFont's TheSans Mono Condensed. The illustrations that appear in the book were produced by Robert Romano and Jessamyn Read using Macromedia FreeHand MX and Adobe Photoshop CS. This colophon was written by Lydia Onofrei.

Description

Table of Contents

Product Details

About the Author

Colophon

Recommended for You

Chapter 1 Core Access

Hacks 1–12

Help Users Find the Objects They Need

Personalize Your Access Application

Work Fast and Avoid Typos

Optimize Data Changes

Transfer Data Between Versions of Access

Organize and Enhance Your Macros

Rid Your Database of Clutter

Protect Valuable Information

Work with Any Amount of Data

Find Database Objects in a Snap

Use a Junction Table

Stop the Database from Growing

Chapter 2 Tables

Hacks 13–18

Create an AutoNumber Field with a Custom Value

Copy Data Between Tables Without an Append Query

Steer Clear of System Tables

Hide Sensitive Information

Simulate Table Triggers

Create Tables Faster

Chapter 3 Entry and Navigation

Hacks 19–27

Help Users Navigate Through Long Forms

Help Users Enter Additional Text

Let Users Add Custom Items to Predesigned Lists

Populate and Sort Lists with Flair

Use Custom Controls on Your Forms

Confirm Record Updates Before Saving

Put a Clock on a Form

Be Tab-Smart

Highlight the Active Control

Chapter 4 Presentation

Hacks 28–39

Separate Alphabetically Sorted Records into Letter Groups

Create Conditional Subtotals

Use Conditional Formatting to Point Out Important Results

Provide a Direct Link to a Report

Protect Intellectual Property

Create a Slideshow in Access

Play Videos in Access Forms

View Reports Embedded in Forms

Put Line Numbers on a Report

Shade Alternating Lines on a Report

Save Paper by Reducing Whitespace

Include the Date, Time, and Page Count

Chapter 5 Queries and SQL

Hacks 40–54

Return a Sample of Records

Create Bulletproof Insert Operations

Find Unmatched Records on Multiple Field Keys

Place a Grand Total in a Query

Sort Any Arbitrary String of Characters

Summarize Complex Data

Get All Combinations of Data

Don't Let Nulls Ruin Data Summaries

Use a Custom Function in a Query

Create Access Tables with SQL Server Scripts

Use Wildcards in Queries

Get Cleaner Or-Based Criteria

Get Cleaner And-Based Criteria

Create an Outer Join

Use Regular Expressions in Access Queries

Chapter 6 Multiuser Issues

Hacks 55–58

Test for Duplication

Distribute a Split Database with Predefined Table Links

Build a Time-Out Feature

Implement Unique Usernames

Chapter 7 External Programs and Data

Hacks 59–71

Import Noncontiguous Ranges of Data from Excel

Use Excel to Reorient Access Data

Use Excel Functions Inside Access

Use Word to Compare Data in Two Access Tables

Import Varied XML Data into Access

Export XML Data Sanely

Break Through VBA's Transformation Barrier

Leverage SQL Server Power by Calling Stored Procedures