Access Cookbook, 2/e (Paperback)

Not a reference book, and not a tutorial either, the new second edition of the highly regarded Access Cookbook is an uncommonly useful collection of solutions to problems that Access users and developers are likely to face as they attempt to build increasingly complex applications.

Although using any single "recipe" in the book will more than pay back the cost of the book in terms of both hours saved and frustration thwarted, Access Cookbook, Second Edition is much more than a handy assortment of cut-and-paste code.

Each of the "recipes" examine a particular problem--problems that commonly occur when you push the upper limits of Access, or ones that are likely to trip up a developer attempting to design a more elegant Access application--even some things you never knew Access could do. The authors then, in a clear, accessible, step-by-step style, present the problems' solution. Following each "recipe" are insights on how Access works, potential pitfalls, interesting programming techniques that are used in the solution, and how and why the solution works, so you can adapt the problem-solving techniques to other similar situations.

Fully updated for Access 2003, Access Cookbook, Second Edition is also one of the first books to thoroughly explore new support for .NET managed code and XML. All of the practical, real-world examples have been tested for compatibility with Access 2003, Windows XP, and Windows Server 2003. This updated new edition also covers Access and SharePoint, Access and SmartTags, Access and .NET; and Access and XML.

Access power users and programmers at all levels, from the relatively inexperienced to the most sophisticated, will rely on the Access Cookbook for quick solutions to gnarly problems. With a dog-eared copy of Access Cookbook at your side, you can spend your time and energy where it matters most: working on the interesting facets of your Access application, not just the time-consuming ones.

Table of Contents：

Preface 

1. Queries

       1.1 Specify Query Criteria at Runtime   

       1.2 Using a Form-Based Parameter Query   

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

       1.4 Make Formatted Date Columns Sort Correctly in a Crosstab Query   

       1.5 Group Mailing Labels by Address   

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

       1.7 Use a VBA Variable to Filter a Query   

       1.8 Use a Query to Retrieve a Random Set of Rows   

       1.9 Create a Query That Will Show Aging of Receivables   

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

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

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

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

       1.14 Create a Query That Uses Case-Sensitive Criteria   

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

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

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

2. Forms

       2.1 Make Custom Templates for Forms and Reports   

       2.2 Highlight the Current Field in Data-Entry Forms   

       2.3 Restrict the User to a Single Row on a Form   

       2.4 Use an Option Group to Collect and Display Textual Information   

       2.5 Display Multiple Pages of Information on One Form   

       2.6 Provide Record Navigation Buttons on a Form   

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

       2.8 Make a Simple "Searching" List Box   

       2.9 Create a Replacement for Access's InputBox   

       2.10 Store the Sizes and Locations of Forms   

       2.11 Open Multiple Instances of a Form   

3. Reports

       3.1 Create a Report with Line Numbers   

       3.2 Print the Value of a Parameter on a Report   

       3.3 Create a Report with Multiple Columns   

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

       3.5 Create a Page-Range Indicator on Each Page   

       3.6 Create a Simple Bar Graph on a Report   

       3.7 Create a Page Total   

       3.8 Avoid Unwanted Blank Rows on Mailing Labels   

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

       3.10 Print Different Headers or Footers on Odd and Even Pages   

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

       3.12 Alternate Gray Bars on My Reports   

       3.13 Print Only Records Matching a Form's Filter   

       3.14 Keep a Report from Breaking at an Inappropriate Place   

       3.15 Customize a Report's Grouping and Sorting at Runtime   

4. Applications

       4.1 Convert Queries into Embedded SQL Statements   

       4.2 Build an Object Inventory   

       4.3 Verify That Objects Use Consistent Settings   

       4.4 Hide Access Screen Activity   

       4.5 Find out What Language Version of Access Is Installed   

       4.6 Internationalize Text in Your Applications   

       4.7 Change and Reset the Access Caption Bar   

       4.8 Use the Windows File Open/Save Common Dialogs   

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

       4.10 Secure Your Access Database   

5. Printers

       5.1 Retrieve a List of All the Installed Output Devices   

       5.2 Set and Retrieve the Name of the Default Output Device   

       5.3 Programmatically Change Margin and Column Settings for Reports   

       5.4 Programmatically Change Printer Options   

       5.5 Programmatically Control the Paper Source   

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

       5.7 Choose an Output Device at Runtime   

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

6. Data

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

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

       6.3 Find All Records with Names That Sound Alike   

       6.4 Find the Median Value for a Field   

       6.5 Quickly Find a Record in a Linked Table   

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

       6.7 Create and Use Flexible AutoNumber Fields   

       6.8 Back Up Selected Objects to Another Database   

7. VBA

       7.1 Build Up String References with Embedded Quotes   

       7.2 Create a Global Procedure Stack   

       7.3 Create an Execution Time Profiler   

       7.4 Multitask Your VBA Code   

       7.5 Programmatically Add Items to a List or Combo Box   

       7.6 Pass a Variable Number of Parameters to a Procedure   

       7.7 Sort an Array in VBA   

       7.8 Fill a List Box with a List of Files   

       7.9 Handle Object Properties, in General   

       7.10 Detect Whether an Object Exists   

8. Optimization

       8.1 Accelerate the Load Time of Forms   

       8.2 Make Slow Forms Run Faster   

       8.3 Make Combo Boxes Load Faster   

       8.4 Use Jet Engine Optimizations to Speed Up Queries   

       8.5 Accelerate VBA Code   

       8.6 Test the Comparative Benefits of Various Optimization Techniques   

       8.7 Accelerate Multiuser Applications   

       8.8 Accelerate Client/Server Applications   

9. User Interface

       9.1 Create Context-Sensitive Keyboard Shortcuts   

       9.2 Create a Form with No Menu or Toolbar   

       9.3 Create a Geographical Map Interface   

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

       9.5 Carry Data Forward from Record to Record   

       9.6 Create a Combo Box That Accepts New Entries   

       9.7 Create Animated Buttons   

       9.8 Create an Expanding Dialog   

       9.9 Use an ActiveX Control   

       9.10 Create a Generic, Reusable Status Meter   

10. Multiuser Applications

       10.1 Properly Secure Your Database   

       10.2 Maintain Multiple Synchronized Copies of the Same Database   

       10.3 Create a Transaction Log   

       10.4 Send Messages to Other Users Without Using Email   

       10.5 Programmatically Track Users and Groups   

       10.6 Adjust an Application Based on Who's Logged In   

       10.7 List All Users with Blank Passwords   

       10.8 Track Which Users Have a Shared Database Open   

       10.9 Determine if a Record Is Locked and by Whom   

       10.10 Set a Maximum Locking Interval for a Record   

11. Windows APIs

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

       11.2 Flash a Window's Titlebar or Icon   

       11.3 Classify Keypresses in a Language-Independent Manner   

       11.4 Restrict Mouse Movement to a Specific Region   

       11.5 Run Another Program and Pause Until It's Done   

       11.6 Exit Windows Under Program Control   

       11.7 Run the Application Associated with a Data File   

       11.8 Check to See if an Application Is Already Running   

       11.9 Retrieve a List of All Top-Level Windows   

       11.10 Close a Running Windows Application   

       11.11 Set File Date and Time Stamps   

       11.12 Retrieve Information About Available Drives   

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

       11.14 Create and Cancel Network Connections Programmatically   

12. Automation

       12.1 Play an Embedded Sound File from Within an Application   

       12.2 Print an Access Report from Excel   

       12.3 Use Excel's Functions from Within Access   

       12.4 Perform a Mail Merge from Access to Word   

       12.5 Add an Item to the Startup Group   

       12.6 Send Access Data to Excel and Create an Excel Chart   

       12.7 Create a PowerPoint Presentation from Access Data   

       12.8 Add a Contact and Send Email Through Outlook   

13. Data Access Pages

       13.1 Replace Navigation Button Images with Your Own Images   

       13.2 Use Labels or Other Controls for Record Navigation   

       13.3 Change the Text Displayed with a Navigation Control   

       13.4 Create a DAP that Allows You to Update Data   

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

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

       13.7 Change the Default Settings for New DAPs   

       13.8 Use Parameters Set in One DAP to Open Another   

14. SQL Server

       14.1 Dynamically Link SQL Server Tables at Runtime   

       14.2 Dynamically Connect to SQL Server from an ADP   

       14.3 Share an ADP from a Shared Network Folder   

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

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

       14.6 Pass Parameters to Stored Procedures from an ADP   

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

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

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

15. Office Web and SharePoint

       15.1 Work with SharePoint Data from Access   

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

       15.3 Conditionally Format a Data View Web Part   

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

       15.5 Post Web Form Data to an Access Database   

       15.6 Create a Web Frontend to an Access Table Using FrontPage   

16. Smart Tags

       16.1 Use the Built-in Smart Tags   

       16.2 Display Multiple Smart Tags on Multiple Forms   

       16.3 Display Smart Tags when Application Starts   

       16.4 Execute a Smart Tag Action Without Displaying the Smart Tag   

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

       16.6 Create a Custom Smart Tag to Get a Weather Report   

       16.7 Create a Custom Smart Tag DLL   

17. .NET

       17.1 Call a .NET Component from Access   

       17.2 Call a .NET Component Containing a Parameterized Constructor   

       17.3 Retrieve Access Data from an ASP.NET Application   

       17.4 Call a Web Service from Access   

       17.5 Work with a Web Service that Returns a DataSet   

       17.6 Print an Access Report from .NET Windows Form Application   

18. XML

       18.1 Import XML Structured as Elements   

       18.2 Import XML into Existing Tables   

       18.3 Import XML Using a Schema (XSD)   

       18.4 Export XML   

       18.5 Exporting Selected Columns to an HTML Table   

       18.6 Export Unrelated Tables   

       18.7 Export Using a Where Clause   

       18.8 Export a Report   

Index