Cover image for Access cookbook
Access cookbook
Getz, Ken.
Personal Author:
Publication Information:
Sebastopol, CA : O'Reilly and associates, [2002]

Physical Description:
xxvii, 687 pages : illustrations ; 23 cm + 1 computer laser optical disk (4 3/4 in.)
General Note:
"Solutions to practical user interface and programming problems." -- cover.

Includes index.
Title Subject:
Subject Term:
Added Corporate Author:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D3 G489 2002 Adult Non-Fiction Central Closed Stacks

On Order



Access Cookbook contains a comprehensive collection of problems, solutions,and practical examples for the Access power user or programmer who is running up against some of the apparent limits of the Access user interfaceor of Access application development. The diverse problems and solutions presented here are of interest to Access power users and programmers at all levels, from the relatively inexperienced to the most sophisticated.Each of the book?s 14 chapters focuses on a particular topic area related to the Access environment or Access programming. Within each chapter, individual items follow a standardized format. The authors first pose the problem, and then present its solution. Finally, a discussion follows, which consists of extended analysis of the solution, an insightful examination of how Access works, or interesting programming techniques that are used in thesolution but that you can also apply elsewhere.The kinds of problems posed and solved in the book include the following:

Specifying query criteria at runtime, rather than at design time Restricting the user to a single row of a form Preventing a report from printing if it has no records Making slow forms run faster Insuring that user interface objects present a uniform, consistent appearance Controlling a printer's paper source programmatically Referencing data from multiple SQL Server databases in a single Access Data Project page Using contact information from an Access database when sending email using Outlook The accompanying CD-ROM includes Access databases that contain the solutions for all problems presented in the text.

Author Notes

Andy Baron is a senior consultant at MCW Technologies, a Microsoft MVP (Most Valuable Professional) since 1995, and a contributing editor for Advisor Media and Pinnacle Publications. Andy is co-author of the Microsoft Access Developers Guide to SQL Server (Sams), and he writes and presents courseware for Application Developers Training Co.

Ken Getz is a senior consultant with MCW Technologies and splits his time between programming, writing, and training. He specializes in tools and applications written in Visual Studio .NET and Visual Basic. Ken is co-author of best-selling several books, including Access 2002 Developer's Handbooks with Paul Litwin and Mike Gunderloy, Visual Basic Language Developer's Handbook with Mike Gilbert, and VBA Developer's Handbook with Mike Gilbert (Sybex). He co-wrote several training courses for Application Developer's Training Company (, including VB.NET, ASP.NET, Access 2000 and 97, Visual Basic 6, and Visual Basic 5 seminars.He has also recorded video training for AppDev covering VB.NET, ASP.NET, VB6, Access 2000, and Access 97. Ken is a frequent speaker at technical conferences and spoken often at Microsoft's Tech-Ed conference. Ken's also a technical editor for Access-VB-SQL Advisor magazine and a columnist for Informant Publications' asp.netPRO magazine.

Paul Litwin is the president of Litwin Consulting, providing development, mentoring, and training in ASP, ASP.NET, Visual Basic, SQL Server, XML, Microsoft Access, and related technologies. Paul was the founding editor of the Smart Access Newsletter and recently became the editor-in-chief of asp.netPRO Magazine( He is the author of a number of books, articles, and training materials on Access, VBA, Web development, ASP, and ASP.NET. Paul is one of the founders of Deep Training, a developer-owned training company providing training on Microsoft.NET ( He is the conference chair of Microsoft ASP.NET Connections ( and Office Developer Connections ( and speaks regularly at other industry events, including Microsoft TechEd and Microsoft Office Deployment and Development Conference.

Table of Contents

Prefacep. xi
1. Queriesp. 1
Specify Query Criteria at Runtimep. 2
Using a Form-Based Parameter Queryp. 4
Limit the Items in One Combo Box Based on the Selected Item in Anotherp. 7
Make Formatted Date Columns Sort Correctly in a Crosstab Queryp. 11
Group Mailing Labels by Addressp. 14
Use a Field in One Table to Update a Field in Another Tablep. 18
Use a VBA Variable to Filter a Queryp. 20
Use a Query to Retrieve a Random Set of Rowsp. 25
Create a Query That Will Show Aging of Receivablesp. 28
Create a Join That's Based on a Comparison Other than Equalityp. 32
Create a Query to Combine Data from Two Tables with Similar Structuresp. 35
Create a Combo Box That Allows a User to Select N/Ap. 38
Use a Query to Show the Relationship Between Employees and Supervisorsp. 42
Create a Query That Uses Case-Sensitive Criteriap. 47
Use a Query to Create a New Table Complete with Indexesp. 51
Save My Queries in a Table for Better Programmatic Access and Securityp. 54
Create a Recordset Based on a Parameter Query from VBA Codep. 56
2. Formsp. 61
Make Custom Templates for Forms and Reportsp. 61
Highlight the Current Field in Data-Entry Formsp. 65
Restrict the User to a Single Row on a Formp. 69
Use an Option Group to Collect and Display Textual Informationp. 71
Display Multiple Pages of Information on One Formp. 75
Provide Record Navigation Buttons on a Formp. 78
Size a Form's Controls to Match the Form's Sizep. 84
Make a Simple "Searching" List Boxp. 89
Create a Replacement for Access's InputBoxp. 93
Store the Sizes and Locations of Formsp. 100
Open Multiple Instances of a Formp. 104
3. Reportsp. 109
Create a Report with Line Numbersp. 110
Print the Value of a Parameter on a Reportp. 111
Create a Report with Multiple Columnsp. 115
Print a Message on a Report if Certain Conditions Are Metp. 119
Create a Page-Range Indicator on Each Pagep. 123
Create a Simple Bar Graph on a Reportp. 125
Create a Page Totalp. 129
Avoid Unwanted Blank Rows on Mailing Labelsp. 132
Suppress Printing a Report if There Are No Records to Printp. 136
Print Different Headers or Footers on Odd and Even Pagesp. 139
Make a Vertical Line the Same Height as a CanGrow/CanShrink Controlp. 142
Alternate Gray Bars on My Reportsp. 146
Print Only Records Matching a Form's Filterp. 149
Keep a Report from Breaking at an Inappropriate Placep. 154
Customize a Report's Grouping and Sorting at Runtimep. 158
4. Developing and Distributing Applicationsp. 169
Convert Queries into Embedded SQL Statementsp. 169
Build an Object Inventoryp. 172
Verify That Objects Use Consistent Settingsp. 179
Hide Access Screen Activityp. 189
Find out What Language Version of Access Is Installedp. 194
Internationalize Text in Your Applicationsp. 197
Change and Reset the Access Caption Barp. 203
Use the Windows File Open/Save Common Dialogsp. 207
Clean Test Data out of a Database When You're Ready to Ship Itp. 214
Secure Your Access Databasep. 217
5. Taming Your Printerp. 223
Retrieve a List of All the Installed Output Devicesp. 224
Set and Retrieve the Name of the Default Output Devicep. 227
Programmatically Change Margin and Column Settings for Reportsp. 229
Programmatically Change Printer Optionsp. 233
Programmatically Control the Paper Sourcep. 237
Retrieve Information About a Report or Form's Selected Printerp. 239
Choose an Output Device at Runtimep. 241
Find Which Reports Are Not Set to Print to the Default Printerp. 244
6. Managing Datap. 247
Save with Each Record the Name of the Last Person Who Edited It and the Date and Timep. 247
Determine if You're on a New Record in a Formp. 252
Find All Records with Names That Sound Alikep. 255
Find the Median Value for a Fieldp. 259
Quickly Find a Record in a Linked Tablep. 265
Get a Complete List of Field Properties from a Table or Queryp. 268
Create and Use Flexible AutoNumber Fieldsp. 273
Back Up Selected Objects to Another Databasep. 278
7. Exploring VBA in Microsoft Accessp. 288
Build Up String References with Embedded Quotesp. 289
Create a Global Procedure Stackp. 294
Create an Execution Time Profilerp. 299
Multitask Your Access Basic Codep. 304
Programmatically Add Items to a List or Combo Boxp. 308
Pass a Variable Number of Parameters to a Procedurep. 317
Sort an Array in Access Basicp. 322
Fill a List Box with a List of Filesp. 326
Handle Object Properties, in Generalp. 330
Detect Whether an Object Existsp. 336
8. Optimizing Your Applicationp. 341
Accelerate the Load Time of Formsp. 341
Make Slow Forms Run Fasterp. 349
Make Combo Boxes Load Fasterp. 358
Use Rushmore to Speed Up Queriesp. 359
Accelerate VBA Codep. 363
Test the Comparative Benefits of Various Optimization Techniquesp. 366
Accelerate Multiuser Applicationsp. 369
Accelerate Client/Server Applicationsp. 375
9. Making the Most of Your User Interfacep. 379
Create Context-Sensitive Keyboard Shortcutsp. 379
Create a Form with No Menu or Toolbarp. 385
Create a Geographical Map Interfacep. 388
Mark a Record on a Form and Return to It Laterp. 391
Carry Data Forward from Record to Recordp. 398
Create a Combo Box That Accepts New Entriesp. 402
Create Animated Buttonsp. 407
Create an Expanding Dialogp. 413
Use an ActiveX Controlp. 417
Create a Generic, Reusable Status Meterp. 426
10. Multiuser Applicationsp. 432
Properly Secure Your Databasep. 432
Maintain Multiple Synchronized Copies of the Same Databasep. 441
Create a Transaction Logp. 447
Send Messages to Other Users Without Using Emailp. 450
Programmatically Track Users and Groupsp. 458
Adjust an Application Based on Who's Logged Inp. 464
List All Users with Blank Passwordsp. 467
Track Which Users Have a Shared Database Openp. 472
Determine if a Record Is Locked and by Whomp. 474
Set a Maximum Locking Interval for a Recordp. 478
11. The Windows APIp. 485
Remove a Form's System Menu and Maximize/Minimize Buttonsp. 486
Flash a Window's Titlebar or Iconp. 490
Classify Keypresses in a Language-Independent Mannerp. 493
Restrict Mouse Movement to a Specific Regionp. 496
Run Another Program and Pause Until It's Donep. 499
Exit Windows Under Program Controlp. 502
Run the Application Associated with a Data Filep. 504
Check to See if an Application Is Already Runningp. 510
Retrieve a List of All Top-Level Windowsp. 513
Close a Running Windows Applicationp. 516
Set File Date and Time Stampsp. 519
Retrieve Information About Available Drivesp. 522
Collect and Display Information on the System and the Access Installationp. 526
Create and Cancel Network Connections Programmaticallyp. 534
12. Automationp. 543
Play an Embedded Sound File from Within an Applicationp. 543
Print an Access Report from Excelp. 548
Use Excel's Functions from Within Accessp. 554
Perform a Mail Merge from Access to Wordp. 563
Add an Item to the Startup Groupp. 567
Send Access Data to Excel and Create an Excel Chartp. 578
Create a PowerPoint Presentation from Access Datap. 584
Add a Contact and Send Email Through Outlookp. 598
13. Using Data Access Pagesp. 605
Replace Navigation Button Images with Your Own Imagesp. 606
Use Labels or Other Controls for Record Navigationp. 608
Change the Text Displayed with a Navigation Controlp. 610
Create a DAP That Allows You to Update Datap. 613
Create One File to Store Connection Information for All DAPs in an Applicationp. 615
Programmatically Change the Connection String for All Pages in a Databasep. 620
Change the Default Settings for New DAPsp. 625
Use Parameters Set in One DAP to Open Anotherp. 627
14. Working with SQL Server Datap. 633
Dynamically Link SQL Server Tables at Runtimep. 633
Dynamically Connect to SQL Server from an ADPp. 639
Share an ADP from a Shared Network Folderp. 642
Fill the Drop-Down Lists When Using ServerFilterByForm in an ADPp. 643
Pass Parameters to Stored Procedures from Pass-Through Queries in an MDBp. 647
Pass Parameters to Stored Procedures from an ADPp. 650
Use Controls as Parameters for the Row Source of Combo and List Boxes in an ADPp. 653
Reference Data from More than One SQL Server Database in an ADPp. 656
Use Views to Update Data in an ADP When Users Don't Have Permissions on Tablesp. 658
Indexp. 661