Access Cookbook 2nd Edition



•	Table of Contents
•	Index
•	Reviews
•	Reader Reviews
•	Errata
•	Academic

Access Cookbook, 2nd Edition
By Andy Baron, Ken Getz, Paul Litwin

Publisher	: O'Reilly
Pub Date	: March 2004
ISBN	: 0-596-00678-0
Pages	: 840

Preface

What This Book Is About

Who This Book Is For

What You Need to Use This Book

How This Book Is Organized

What We Left Out

Conventions Used in This Book

Comments and Questions

Acknowledgments

Chapter 1. Queries

Section 1.1. Specify Query Criteria at Runtime

Section 1.2. Using a Form-Based Parameter Query

Section 1.3. Limit the Items in One Combo Box Based on the Selected Item in Another

Section 1.4. Make Formatted Date Columns Sort Correctly in a Crosstab Query

Section 1.5. Group Mailing Labels by Address

Section 1.6. Use a Field in One Table to Update a Field in Another Table

Section 1.7. Use a VBA Variable to Filter a Query

Section 1.8. Use a Query to Retrieve a Random Set of Rows

Section 1.9. Create a Query That Will Show Aging of Receivables

Section 1.10. Create a Join That's Based on a Comparison Other than Equality

Section 1.11. Create a Query to Combine Data from Two Tables with Similar Structures

Section 1.12. Create a Combo Box That Allows a User to Select N/A

Section 1.13. Use a Query to Show the Relationship Between Employees and Supervisors

Section 1.14. Create a Query That Uses Case-Sensitive Criteria

Section 1.15. Use a Query to Create a New Table Complete with Indexes

Section 1.16. Save My Queries in a Table for Better Programmatic Access and Security

Section 1.17. Create a Recordset Based on a Parameter Query from VBA Code

Chapter 2. Forms

Section 2.1. Make Custom Templates for Forms and Reports

Section 2.2. Highlight the Current Field in Data-Entry Forms

Section 2.3. Restrict the User to a Single Row on a Form

Section 2.4. Use an Option Group to Collect and Display Textual Information

Section 2.5. Display Multiple Pages of Information on One Form

Section 2.6. Provide Record Navigation Buttons on a Form

Section 2.7. Size a Form's Controls to Match the Form's Size

Section 2.8. Make a Simple "Searching" List Box

Section 2.9. Create a Replacement for Access's InputBox

Section 2.10. Store the Sizes and Locations of Forms

Section 2.11. Open Multiple Instances of a Form

Chapter 3. Reports

Section 3.1. Create a Report with Line Numbers

Section 3.2. Print the Value of a Parameter on a Report

Section 3.3. Create a Report with Multiple Columns

Section 3.4. Print a Message on a Report if Certain Conditions Are Met

Section 3.5. Create a Page-Range Indicator on Each Page

Section 3.6. Create a Simple Bar Graph on a Report

Section 3.7. Create a Page Total

Section 3.8. Avoid Unwanted Blank Rows on Mailing Labels

Section 3.9. Suppress Printing a Report if There Are No Records to Print

Section 3.10. Print Different Headers or Footers on Odd and Even Pages

Section 3.11. Make a Vertical Line the Same Height as a CanGrow/CanShrink Control

Section 3.12. Alternate Gray Bars on My Reports

Section 3.13. Print Only Records Matching a Form's Filter

Section 3.14. Keep a Report from Breaking at an Inappropriate Place

Section 3.15. Customize a Report's Grouping and Sorting at Runtime

Chapter 4. Applications

Section 4.1. Convert Queries into Embedded SQL Statements

Section 4.2. Build an Object Inventory

Section 4.3. Verify That Objects Use Consistent Settings

Section 4.4. Hide Access Screen Activity

Section 4.5. Find out What Language Version of Access Is Installed

Section 4.6. Internationalize Text in Your Applications

Section 4.7. Change and Reset the Access Caption Bar

Section 4.8. Use the Windows File Open/Save Common Dialogs

Section 4.9. Clean Test Data out of a Database When You're Ready to Ship It

Section 4.10. Secure Your Access Database

Chapter 5. Printers

Section 5.1. Retrieve a List of All the Installed Output Devices

Section 5.2. Set and Retrieve the Name of the Default Output Device

Section 5.3. Programmatically Change Margin and Column Settings for Reports

Section 5.4. Programmatically Change Printer Options

Section 5.5. Programmatically Control the Paper Source

Section 5.6. Retrieve Information About a Report or Form's Selected Printer

Section 5.7. Choose an Output Device at Runtime

Section 5.8. Find Which Reports Are Not Set to Print to the Default Printer

Chapter 6. Data

Section 6.1. Save with Each Record the Name of the Last Person Who Edited It and the Date and Time

Section 6.2. Determine if You're on a New Record in a Form

Section 6.3. Find All Records with Names That Sound Alike

Section 6.4. Find the Median Value for a Field

Section 6.5. Quickly Find a Record in a Linked Table

Section 6.6. Get a Complete List of Field Properties from a Table or Query

Section 6.7. Create and Use Flexible AutoNumber Fields

Section 6.8. Back Up Selected Objects to Another Database

Chapter 7. VBA

Section 7.1. Build Up String References with Embedded Quotes

Section 7.2. Create a Global Procedure Stack

Section 7.3. Create an Execution Time Profiler

Section 7.4. Multitask Your VBA Code

Section 7.5. Programmatically Add Items to a List or Combo Box

Section 7.6. Pass a Variable Number of Parameters to a Procedure

Section 7.7. Sort an Array in VBA

Section 7.8. Fill a List Box with a List of Files

Section 7.9. Handle Object Properties, in General

Section 7.10. Detect Whether an Object Exists

Chapter 8. Optimization

Section 8.1. Accelerate the Load Time of Forms

Section 8.2. Make Slow Forms Run Faster

Section 8.3. Make Combo Boxes Load Faster

Section 8.4. Use Jet Engine Optimizations to Speed Up Queries

Section 8.5. Accelerate VBA Code

Section 8.6. Test the Comparative Benefits of Various Optimization Techniques

Section 8.7. Accelerate Multiuser Applications

Section 8.8. Accelerate Client/Server Applications

Chapter 9. User Interface

Section 9.1. Create Context-Sensitive Keyboard Shortcuts

Section 9.2. Create a Form with No Menu or Toolbar

Section 9.3. Create a Geographical Map Interface

Section 9.4. Mark a Record on a Form and Return to It Later

Section 9.5. Carry Data Forward from Record to Record

Section 9.6. Create a Combo Box That Accepts New Entries

Section 9.7. Create Animated Buttons

Section 9.8. Create an Expanding Dialog

Section 9.9. Use an ActiveX Control

Section 9.10. Create a Generic, Reusable Status Meter

Chapter 10. Multiuser Applications

Section 10.1. Properly Secure Your Database

Section 10.2. Maintain Multiple Synchronized Copies of the Same Database

Section 10.3. Create a Transaction Log

Section 10.4. Send Messages to Other Users Without Using Email

Section 10.5. Programmatically Track Users and Groups

Section 10.6. Adjust an Application Based on Who's Logged In

Section 10.7. List All Users with Blank Passwords

Section 10.8. Track Which Users Have a Shared Database Open

Section 10.9. Determine if a Record Is Locked and by Whom

Section 10.10. Set a Maximum Locking Interval for a Record

Chapter 11. Windows APIs

Section 11.1. Remove a Form's System Menu and Maximize/Minimize Buttons

Section 11.2. Flash a Window's Titlebar or Icon

Section 11.3. Classify Keypresses in a Language-Independent Manner

Section 11.4. Restrict Mouse Movement to a Specific Region

Section 11.5. Run Another Program and Pause Until It's Done

Section 11.6. Exit Windows Under Program Control

Section 11.7. Run the Application Associated with a Data File

Section 11.8. Check to See if an Application Is Already Running

Section 11.9. Retrieve a List of All Top-Level Windows

Section 11.10. Close a Running Windows Application

Section 11.11. Set File Date and Time Stamps

Section 11.12. Retrieve Information About Available Drives

Section 11.13. Collect and Display Information on the System and the Access Installation

Section 11.14. Create and Cancel Network Connections Programmatically

Chapter 12. Automation

Section 12.1. Play an Embedded Sound File from Within an Application

Section 12.2. Print an Access Report from Excel

Section 12.3. Use Excel's Functions from Within Access

Section 12.4. Perform a Mail Merge from Access to Word

Section 12.5. Add an Item to the Startup Group

Section 12.6. Send Access Data to Excel and Create an Excel Chart

Section 12.7. Create a PowerPoint Presentation from Access Data

Section 12.8. Add a Contact and Send Email Through Outlook

Chapter 13. Data Access Pages

Section 13.1. Replace Navigation Button Images with Your Own Images

Section 13.2. Use Labels or Other Controls for Record Navigation

Section 13.3. Change the Text Displayed with a Navigation Control

Section 13.4. Create a DAP that Allows You to Update Data

Section 13.5. Create One File to Store Connection Information for All DAPs in an Application

Section 13.6. Programmatically Change the Connection String for All Pages in a Database

Section 13.7. Change the Default Settings for New DAPs

Section 13.8. Use Parameters Set in One DAP to Open Another

Chapter 14. SQL Server

Section 14.1. Dynamically Link SQL Server Tables at Runtime

Section 14.2. Dynamically Connect to SQL Server from an ADP

Section 14.3. Share an ADP from a Shared Network Folder

Section 14.4. Fill the Drop-Down Lists When Using ServerFilterByForm in an ADP

Section 14.5. Pass Parameters to Stored Procedures from Pass-Through Queries in an MDB

Section 14.6. Pass Parameters to Stored Procedures from an ADP

Section 14.7. Use Controls as Parameters for the Row Source of Combo and List Boxes in an ADP

Section 14.8. Reference Data from More than One SQL Server Database in an ADP

Section 14.9. Use Views to Update Data in an ADP When Users Don't Have Permissions on Tables

Chapter 15. Office Web and SharePoint

Section 15.1. Work with SharePoint Data from Access

Section 15.2. Create a SharePoint Data View Web Part Based on Access Data

Section 15.3. Conditionally Format a Data View Web Part

Section 15.4. Create a Master/Detail Page using Data View Web Parts

Section 15.5. Post Web Form Data to an Access Database

Section 15.6. Create a Web Frontend to an Access Table Using FrontPage

Chapter 16. Smart Tags

Section 16.1. Use the Built-in Smart Tags

Section 16.2. Display Multiple Smart Tags on Multiple Forms

Section 16.3. Display Smart Tags when Application Starts

Section 16.4. Execute a Smart Tag Action Without Displaying the Smart Tag

Section 16.5. Create a Smart Tag on a Table in an Access Project

Section 16.6. Create a Custom Smart Tag to Get a Weather Report

Section 16.7. Create a Custom Smart Tag DLL

Chapter 17. .NET

Section 17.1. Call a .NET Component from Access

Section 17.2. Call a .NET Component Containing a Parameterized Constructor

Section 17.3. Retrieve Access Data from an ASP.NET Application

Section 17.4. Call a Web Service from Access

Section 17.5. Work with a Web Service that Returns a DataSet

Section 17.6. Print an Access Report from .NET Windows Form Application

Chapter 18. XML

XML Overview

Section 18.1. Import XML Structured as Elements

Section 18.2. Import XML into Existing Tables

Section 18.3. Import XML Using a Schema (XSD)

Section 18.4. Export XML

Section 18.5. Exporting Selected Columns to an HTML Table

Section 18.6. Export Unrelated Tables

Section 18.7. Export Using a Where Clause

Section 18.8. Export a Report

Colophon

Index

[ Team LiB ]