O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  


 
Buy the book!
Access Hacks
By Ken Bluttman
April 2005
More Info

How do these hacks stand up? Comment on a hack from the book by choosing the associated "Discuss" link below. You can also view the code from any of the hacks by clicking on the "Listing" or "Code" links. A number of hacks have been selected to be featured online in their entirety; you may view those hacks by clicking on the hack titles that are linked.

You can also download all the scripts and other files for this book here.

Jump to: Core Access  | Tables  | Entry and Navigation  | Presentation  | Queries and SQL  | Multiuser Issues  | External Programs and Data  | Programming  | Third-Party Applications  | The Internet

Core Access

HACK
#1

Help Users Find the Objects They Need
Place shortcuts to pertinent objects in custom groups so that users don't have to wade through all the database objects.
[Discuss (0) | Link to this hack]

HACK
#2

Personalize Your Access Application
Build personalization functionality so that users can set up the application in ways that work best for them.
[Discuss (0) | Link to this hack]

HACK
#3

Work Fast and Avoid Typos
Save time and avoid mistakes by using simple keystrokes for entering the date, time, or other commonly used entries.
[Discuss (0) | Link to this hack]

HACK
#4

Optimize Data Changes
Avoid having to propagate data changes manually throughout related tables by establishing cascading updates and deletes.
[Discuss (0) | Link to this hack]

HACK
#5

Transfer Data Between Versions of Access
Say goodbye to version incompatibility issues.
[Discuss (0) | Link to this hack]

HACK
#6

Organize and Enhance Your Macros
Optimize and reduce the number of macros using the optional name and condition columns.
[Discuss (0) | Link to this hack]

HACK
#7

Rid Your Database of Clutter
Implement an object-use log to clean up an overloaded database by analyzing user actions and then deleting never-used objects.
[Discuss (0) | Link to this hack]

HACK
#8

Protect Valuable Information
Protect your data using the read-only command-line switch so that users can't edit the data.
The Code
[Discuss (0) | Link to this hack]

HACK
#9

Work with Any Amount of Data
Plan a multiple-database architecture to house any amount of data—gigabytes, even terabytes!
[Discuss (0) | Link to this hack]

HACK
#10

Find Database Objects in a Snap
Use the description property to prevent users from being overwhelmed by sifting through cryptic-sounding forms, queries, and reports.
[Discuss (0) | Link to this hack]

HACK
#11

Use a Junction Table
Correctly model a many-to-many relationship.
[Discuss (0) | Link to this hack]

HACK
#12

Stop the Database from Growing
Use the Compact on Close option to keep a database from getting too big.
[Discuss (0) | Link to this hack]

Tables

HACK
#13

Create an AutoNumber Field with a Custom Value
The AutoNumber field doesn't need to begin with a value of 1. You can override Access's default autonumbering scheme to better suit your requirements.
[Discuss (0) | Link to this hack]

HACK
#14

Copy Data Between Tables Without an Append Query
Use Paste Append to easily copy data across tables.
[Discuss (0) | Link to this hack]

HACK
#15

Steer Clear of System Tables
Avoid incorrect results by leaving system tables out of your table count and definition routines.
The Code
[Discuss (0) | Link to this hack]

HACK
#17

Simulate Table Triggers
Incorporate the same functionality as SQL Server or Oracle in your Access application.
The Code
[Discuss (0) | Link to this hack]

Entry and Navigation

HACK
#19

Help Users Navigate Through Long Forms
Use Page Break controls and command buttons so that users won't have to scroll through long data-entry forms.
The Code
[Discuss (0) | Link to this hack]

HACK
#20

Help Users Enter Additional Text
Place the insertion point at the end of the text in a text box so that additional entries land just where they should.
[Discuss (0) | Link to this hack]

HACK
#21

Let Users Add Custom Items to Predesigned Lists
Avoid forcing choices to existing list items only by adding a procedure to handle new values.
The Code
[Discuss (0) | Link to this hack]

HACK
#22

Populate and Sort Lists with Flair
Use these three clever techniques to populate and sort listbox controls.
[Discuss (0) | Link to this hack]

HACK
#23

Use Custom Controls on Your Forms
Move past standard Access controls, and discover new design possibilities.
[Discuss (0) | Link to this hack]

HACK
#24

Confirm Record Updates Before Saving
Give users a chance to review their edits before they save a record.
[Discuss (0) | Link to this hack]

HACK
#25

Put a Clock on a Form
Give users the time and date, even for more than one time zone.
[Discuss (0) | Link to this hack]

HACK
#26

Be Tab-Smart
Override a form's tab order so that users get to the entry boxes they need.
[Discuss (0) | Link to this hack]

HACK
#27

Highlight the Active Control
Provide a visual aid on a busy form by emphasizing the control that has the focus.
[Discuss (0) | Link to this hack]

Presentation

HACK
#28

Separate Alphabetically Sorted Records into Letter Groups
Tap the Prefix Characters property to gain new layout possibilities.
[Discuss (0) | Link to this hack]

HACK
#29

Create Conditional Subtotals
Split a grand total into pertinent business summaries using running sums and expressions.
[Discuss (0) | Link to this hack]

HACK
#30

Use Conditional Formatting to Point Out Important Results
Not only can you use the built-in conditional formatting feature, but you also can roll your own with a little VBA!
[Discuss (0) | Link to this hack]

HACK
#31

Provide a Direct Link to a Report
Provide a desktop shortcut to a report so that users can completely skip the process of starting up the database.
[Discuss (0) | Link to this hack]

HACK
#32

Protect Intellectual Property
Prevent misuse of confidential and copyrighted material by printing watermarks on reports.
[Discuss (0) | Link to this hack]

HACK
#33

Create a Slideshow in Access
Use images and the OnTimer event to create a controllable visual show.
The Code
[Discuss (0) | Link to this hack]

HACK
#34

Play Videos in Access Forms
Deliver your message the multimedia way with the Windows Media Player.
[Discuss (0) | Link to this hack]

HACK
#35

View Reports Embedded in Forms
Preview reports, whether current or historical, directly on the form you are working on.
[Discuss (0) | Link to this hack]

HACK
#36

Put Line Numbers on a Report
Use the Running Sum property to include an incremental counter in your report.
[Discuss (0) | Link to this hack]

HACK
#37

Shade Alternating Lines on a Report
Go for the readability factor. Use alternating shaded lines to make a more pleasing presentation.
The Code
[Discuss (0) | Link to this hack]

HACK
#38

Save Paper by Reducing Whitespace
Use the Can Shrink property to condense your reports.
[Discuss (0) | Link to this hack]

HACK
#39

Include the Date, Time, and Page Count
Use common expressions to quickly insert necessary header and footer information.
[Discuss (0) | Link to this hack]

Queries and SQL

HACK
#40

Return a Sample of Records
Use the Top predicate to return a portion of your records without bias.
[Discuss (0) | Link to this hack]

HACK
#41

Create Bulletproof Insert Operations
Prevent failed append operations so that all the records make it into the table.
[Discuss (0) | Link to this hack]

HACK
#42

Find Unmatched Records on Multiple Field Keys
The Find Unmatched Query Wizard looks for unmatched records based on a single related field. You can adapt this query to work on more than one related field.
[Discuss (0) | Link to this hack]

HACK
#43

Place a Grand Total in a Query
Use a Union query to combine raw data records with the data total.
[Discuss (0) | Link to this hack]

HACK
#44

Sort Any Arbitrary String of Characters
The Access query grid is great for sorting your data, but you need to help it sort on characters in the middle of a field.
[Discuss (0) | Link to this hack]

HACK
#45

Summarize Complex Data
Take advantage of Crosstab queries to get a view on multifaceted data.
[Discuss (0) | Link to this hack]

HACK
#46

Get All Combinations of Data
Remove the Join clause in a SQL statement to return a Cartesian product (which returns all possible combinations).
[Discuss (3) | Link to this hack]

HACK
#47

Don't Let Nulls Ruin Data Summaries
When nulls are mixed in with valid data, incorrect results can occur. Here are some guidelines to tame the beast.
[Discuss (0) | Link to this hack]

HACK
#48

Use a Custom Function in a Query
Write a custom function to manipulate multiple data formats.
[Discuss (0) | Link to this hack]

HACK
#49

Create Access Tables with SQL Server Scripts
SQL Server writes scripts that create tables. With a little editing, you can put them to work in Access.
[Discuss (0) | Link to this hack]

HACK
#50

Use Wildcards in Queries
The Like operator comes in handy when you don't quite remember how to spell a data item.
[Discuss (0) | Link to this hack]

HACK
#51

Get Cleaner Or-Based Criteria
Avoid using multiple rows in the query grid by using the In operator.
[Discuss (0) | Link to this hack]

HACK
#52

Get Cleaner And-Based Criteria
Remove the need for multiple And statements by combining the In and Not operators.
[Discuss (0) | Link to this hack]

HACK
#53

Create an Outer Join
Access doesn't support making an outer join; here's a workaround.
[Discuss (1) | Link to this hack]

HACK
#54

Use Regular Expressions in Access Queries
Sometimes wildcards aren't enough. With a little hacking, you can use regular expressions in your queries.
[Discuss (0) | Link to this hack]

Multiuser Issues

HACK
#55

Test for Duplication
Before you insert multiple entries into master tables in a busy data-entry environment, you'll need a custom validation process to avoid duplicated data.
[Discuss (0) | Link to this hack]

HACK
#56

Distribute a Split Database with Predefined Table Links
If you follow this interesting distribution game plan, users will not have to link their local database files to the data tables on the system.
[Discuss (0) | Link to this hack]

HACK
#57

Build a Time-Out Feature
Make sure your data is saved and available to others. Lock the records when they're not being updated.
[Discuss (0) | Link to this hack]

HACK
#58

Implement Unique Usernames
Even when Access Security isn't active, you can implement unique usernames when all users are Admin.
[Discuss (0) | Link to this hack]

External Programs and Data

HACK
#59

Import Noncontiguous Ranges of Data from Excel
A standard import lets you get only one data range at a time. Here are a couple of workarounds to get you more.
[Discuss (0) | Link to this hack]

HACK
#60

Use Excel to Reorient Access Data
Use Excel's Paste Special Transpose feature to turn data on its ear.
[Discuss (0) | Link to this hack]

HACK
#61

Use Excel Functions Inside Access
Expose powerful functions available in Excel to your Access application.
[Discuss (0) | Link to this hack]

HACK
#62

Use Word to Compare Data in Two Access Tables
Look for discrepancies the easy way, using Word's Document Compare utility.
[Discuss (0) | Link to this hack]

HACK
#63

Import Varied XML Data into Access
Access is pretty good at importing simple XML data, but sometimes you want to import data that isn't precisely the way Access expects it to be.
[Discuss (0) | Link to this hack]

HACK
#64

Export XML Data Sanely
Working around the thorny issue of exporting related data to XML.
[Discuss (0) | Link to this hack]

HACK
#65

Break Through VBA's Transformation Barrier
Strange but true: Access supports XSLT transformation on input when you use the GUI, but not when you automate the process with VBA. The same goes for output. Fortunately, you can work around this by calling the MSXML parser directly.
[Discuss (0) | Link to this hack]

HACK
#66

Leverage SQL Server Power by Calling Stored Procedures
Get a leg up on performance when using SQL Server data.
[Discuss (0) | Link to this hack]

HACK
#67

Manage Word Documents from Access
Tap into the Word object library to copy Access data directly into a Word document.
The Code
[Discuss (0) | Link to this hack]

HACK
#68

Use Access as a Front End to MySQL
MySQL is a widely used open source database program that often runs on Linux web servers, and Access makes a great front end for data entry and reporting.
[Discuss (0) | Link to this hack]

HACK
#69

Send Access Data Through Outlook Automatically
Implement bulk emailing of your data by tapping into Outlook objects.
The Code
[Discuss (0) | Link to this hack]

HACK
#70

Create Access Tables from Outside Access
You don't have to be in Access to use Access.
The Code
[Discuss (0) | Link to this hack]

HACK
#71

Write VBA with the Macro Recorder in Word and Excel
Take advantage of autogenerated code to speed up your coding efforts.
[Discuss (0) | Link to this hack]

Programming

HACK
#72

Store Initial Control Selections for Later Recall
The Tag property is a great place to store data about controls. Here's how to put it to good use.
[Discuss (0) | Link to this hack]

HACK
#73

Write Code Faster by Turning Off Syntax-Checking
Make sure Access doesn't pester you with warning messages while you're writing code.
[Discuss (0) | Link to this hack]

HACK
#74

Substitute Domain Aggregate Functions for SQL Aggregate Functions
Reduce the amount of code you enter and still get the same results.
The Code
[Discuss (0) | Link to this hack]

HACK
#75

Shrink Your Code with Subroutines
Say goodbye to long and difficult-to-maintain code by placing repetitive processing into subroutines.
[Discuss (0) | Link to this hack]

HACK
#76

Shrink Your Code with Optional Arguments
Put subroutines to even more general use by accepting different numbers of arguments.
[Discuss (0) | Link to this hack]

HACK
#77

Protect Programming Code from Curious Users
Your code is valuable. Add password protection to your modules.
[Discuss (0) | Link to this hack]

HACK
#78

Build a Secret Developer Backdoor into Your Applications
Keep users out of your design while letting yourself in the easy way.
The Code
[Discuss (0) | Link to this hack]

HACK
#79

Help Users Drill Down to a Record
Facilitate browsing through a lengthy customer database by first grouping customers.
[Discuss (0) | Link to this hack]

HACK
#80

Prevent Users from Disabling Your Startup Options
Stop users from being able to hold down the Shift key to get to the database window.
[Discuss (0) | Link to this hack]

HACK
#81

Inform Users of a Long Process
While your code is conquering a long looping process, users might think their system has crashed, unless you provide some visual clue that a process is running.
The Code
[Discuss (0) | Link to this hack]

HACK
#82

Allow Users to Choose a Back-End Database
Store ODBC connection strings in a table so they are ready to go when needed.
[Discuss (0) | Link to this hack]

HACK
#83

Override the Timeout Interval
In a busy network environment, a little patience is sometimes necessary.
[Discuss (0) | Link to this hack]

HACK
#84

Save Values from Unbound Controls for Later Recall
Give users a way to automatically recreate the way a form was set up so that they don't have to reenter information
The Code
[Discuss (0) | Link to this hack]

HACK
#85

Sort Records Randomly
Get a unique sort of records whenever you need one.
[Discuss (0) | Link to this hack]

HACK
#86

Bulk-Update Controls on a Form
Tap the Controls property to make fast property changes.
[Discuss (0) | Link to this hack]

HACK
#87

Provide Complete XML Control to Any Version of Access
Use the MSXML Parser to make XML native to your applications.
The Code
[Discuss (0) | Link to this hack]

HACK
#88

Use Custom Enumerations
Use familiar names, instead of memorizing equivalent numbers, to avoid errors and speed up coding.
[Discuss (0) | Link to this hack]

HACK
#89

Convert Text to the Desired Case
Have any text string be returned in uppercase, lowercase, or proper case.
The Code
[Discuss (0) | Link to this hack]

HACK
#90

Create a Code Library
Make your favorite custom functions available in all your databases.
[Discuss (0) | Link to this hack]

HACK
#91

Automatically Check for Database Table Updates
Pull updated objects from a master database when your database opens.
The Code
[Discuss (0) | Link to this hack]

Third-Party Applications

HACK
#92

Document Your Database with Total Access Analyzer
Get the full nuts-and-bolts skinny on your database.
[Discuss (0) | Link to this hack]

HACK
#93

Build an Application Shell with EZ Application Generator
Let your fingers do the walking through the process of creating an Access application.
[Discuss (0) | Link to this hack]

HACK
#94

Load Your Database with Test Data
Use predesigned test data that matches your tables and fields.
[Discuss (0) | Link to this hack]

HACK
#95

Use Access as an XML Database
Have Access work as a front end to your XML data.
The Code
[Discuss (1) | Link to this hack]

The Internet

HACK
#96

Export a Report as HTML
Preview your reports on web pages to reach a larger audience.
[Discuss (0) | Link to this hack]

HACK
#97

Use a Browser Inside Access
Place the Microsoft web browser on a form to coordinate data and the Web.
[Discuss (0) | Link to this hack]

HACK
#98

Pull the HTML Source Code from a Web Site
Integrate web data into your application.
[Discuss (0) | Link to this hack]

HACK
#99

Download Files Using the Web Browser Control
FTP files without ever leaving your database.
[Discuss (0) | Link to this hack]

HACK
#100

Use a Smart Tag to Open a Web Page
Quickly review web information when you need to see it.
[Discuss (0) | Link to this hack]


O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.