Access Cookbook

by Getz, Ken

• ISBN13: 9780596006785
• ISBN10: 0596006780
• Edition: 2nd
• Format: Paperback
• Copyright: 2004-03-01
• Publisher: Oreilly & Associates Inc

Summary

Full updated for Access 2003 Access Cookbook 2E provides solutions to problems commonly encountered by users and developers as they attempt to build increasingly complex Access applications. This book is also one of the first to explore new support for .NET managed code and XML. The book contains a comprehensive collection of problems, solutions, and practical examples for Access developers and users at all levels. Four new chapters include: Access and SharePoint, Access and SmartTags, Access and .NET; and Access and XML.

Author Biography

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 (www.appdev.com), 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(www.aspnetpro.com). 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 (www.deeptraining.com). He is the conference chair of Microsoft ASP.NET Connections (www.asp-connections.com) and Office Developer Connections (www.msofficeconnections.com) and speaks regularly at other industry events, including Microsoft TechEd and Microsoft Office Deployment and Development Conference.

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.

Table of Contents

Preface	ix
1. Queries	1	(60)
1.1 Specify Query Criteria at Runtime	2	(2)
1.2 Using a Form-Based Parameter Query	4	(3)
1.3 Limit the Items in One Combo Box Based on the Selected Item in Another	7	(5)
1.4 Make Formatted Date Columns Sort Correctly in a Crosstab Query	12	(3)
1.5 Group Mailing Labels by Address	15	(3)
1.6 Use a Field in One Table to Update a Field in Another Table	18	(4)
1.7 Use a VBA Variable to Filter a Query	22	(4)
1.8 Use a Query to Retrieve a Random Set of Rows	26	(2)
1.9 Create a Query That Will Show Aging of Receivables	28	(5)
1.10 Create a Join That's Based on a Comparison Other than Equality	33	(4)
1.11 Create a Query to Combine Data from Two Tables with Similar Structures	37	(3)
1.12 Create a Combo Box That Allows a User to Select N/A	40	(4)
1.13 Use a Query to Show the Relationship Between Employees and Supervisors	44	(5)
1.14 Create a Query That Uses Case-Sensitive Criteria	49	(2)
1.15 Use a Query to Create a New Table Complete with Indexes	51	(3)
1.16 Save My Queries in a Table for Better Programmatic Access and Security	54	(3)
1.17 Create a Recordset Based on a Parameter Query from VBA Code	57	(4)
2. Forms	61	(49)
2.1 Make Custom Templates for Forms and Reports	61	(4)
2.2 Highlight the Current Field in Data-Entry Forms	65	(4)
2.3 Restrict the User to a Single Row on a Form	69	(3)
2.4 Use an Option Group to Collect and Display Textual Information	72	(4)
2.5 Display Multiple Pages of Information on One Form	76	(3)
2.6 Provide Record Navigation Buttons on a Form	79	(6)
2.7 Size a Form's Controls to Match the Form's Size	85	(5)
2.8 Make a Simple "Searching" List Box	90	(4)
2.9 Create a Replacement for Access's InputBox	94	(7)
2.10 Store the Sizes and Locations of Forms	101	(4)
2.11 Open Multiple Instances of a Form	105	(5)
3. Reports	110	(61)
3.1 Create a Report with Line Numbers	111	(1)
3.2 Print the Value of a Parameter on a Report	112	(4)
3.3 Create a Report with Multiple Columns	116	(4)
3.4 Print a Message on a Report if Certain Conditions Are Met	120	(5)
3.5 Create a Page-Range Indicator on Each Page	125	(3)
3.6 Create a Simple Bar Graph on a Report	128	(3)
3.7 Create a Page Total	131	(3)
3.8 Avoid Unwanted Blank Rows on Mailing Labels	134	(4)
3.9 Suppress Printing a Report if There Are No Records to Print	138	(2)
3.10 Print Different Headers or Footers on Odd and Even Pages	140	(4)
3.11 Make a Vertical Line the Same Height as a CanGrow/CanShrink Control	144	(4)
3.12 Alternate Gray Bars on My Reports	148	(3)
3.13 Print Only Records Matching a Form's Filter	151	(5)
3.14 Keep a Report from Breaking at an Inappropriate Place	156	(4)
3.15 Customize a Report's Grouping and Sorting at Runtime	160	(11)
4. Applications	171	(56)
4.1 Convert Queries into Embedded SQL Statements	171	(3)
4.2 Build an Object Inventory	174	(7)
4.3 Verify That Objects Use Consistent Settings	181	(11)
4.4 Hide Access Screen Activity	192	(5)
4.5 Find out What Language Version of Access Is Installed	197	(3)
4.6 Internationalize Text in Your Applications	200	(6)
4.7 Change and Reset the Access Caption Bar	206	(4)
4.8 Use the Windows File Open/Save Common Dialogs	210	(8)
4.9 Clean Test Data out of a Database When You're Ready to Ship It	218	(3)
4.10 Secure Your Access Database	221	(6)
5. Printers	227	(25)
5.1 Retrieve a List of All the Installed Output Devices	228	(3)
5.2 Set and Retrieve the Name of the Default Output Device	231	(2)
5.3 Programmatically Change Margin and Column Settings for Reports	233	(4)
5.4 Programmatically Change Printer Options	237	(4)
5.5 Programmatically Control the Paper Source	241	(3)
5.6 Retrieve Information About a Report or Form's Selected Printer	244	(2)
5.7 Choose an Output Device at Runtime	246	(2)
5.8 Find Which Reports Are Not Set to Print to the Default Printer	248	(4)
6. Data	252	(41)
6.1 Save with Each Record the Name of the Last Person Who Edited It and the Date and Time	252	(5)
6.2 Determine if You're on a New Record in a Form	257	(3)
6.3 Find All Records with Names That Sound Alike	260	(4)
6.4 Find the Median Value for a Field	264	(6)
6.5 Quickly Find a Record in a Linked Table	270	(4)
6.6 Get a Complete List of Field Properties from a Table or Query	274	(4)
6.7 Create and Use Flexible AutoNumber Fields	278	(5)
6.8 Back Up Selected Objects to Another Database	283	(10)
7. VBA	293	(56)
7.1 Build Up String References with Embedded Quotes	293	(5)
7.2 Create a Global Procedure Stack	298	(6)
7.3 Create an Execution Time Profiler	304	(5)
7.4 Multitask Your VBA Code	309	(4)
7.5 Programmatically Add Items to a List or Combo Box	313	(12)
7.6 Pass a Variable Number of Parameters to a Procedure	325	(5)
7.7 Sort an Array in VBA	330	(4)
7.8 Fill a List Box with a List of Files	334	(4)
7.9 Handle Object Properties, in General	338	(7)
7.10 Detect Whether an Object Exists	345	(4)
8. Optimization	349	(38)
8.1 Accelerate the Load Time of Forms	349	(8)
8.2 Make Slow Forms Run Faster	357	(9)
8.3 Make Combo Boxes Load Faster	366	(1)
8.4 Use Jet Engine Optimizations to Speed Up Queries	367	(4)
8.5 Accelerate VBA Code	371	(4)
8.6 Test the Comparative Benefits of Various Optimization Techniques	375	(2)
8.7 Accelerate Multiuser Applications	377	(6)
8.8 Accelerate Client/Server Applications	383	(4)
9. User Interface	387	(54)
9.1 Create Context-Sensitive Keyboard Shortcuts	388	(5)
9.2 Create a Form with No Menu or Toolbar	393	(3)
9.3 Create a Geographical Map Interface	396	(4)
9.4 Mark a Record on a Form and Return to It Later	400	(7)
9.5 Carry Data Forward from Record to Record	407	(4)
9.6 Create a Combo Box That Accepts New Entries	411	(5)
9.7 Create Animated Buttons	416	(5)
9.8 Create an Expanding Dialog	421	(5)
9.9 Use an ActiveX Control	426	(8)
9.10 Create a Generic, Reusable Status Meter	434	(7)
10. Multiuser Applications	441	(53)
10.1 Properly Secure Your Database	441	(10)
10.2 Maintain Multiple Synchronized Copies of the Same Database	451	(5)
10.3 Create a Transaction Log	456	(3)
10.4 Send Messages to Other Users Without Using Email	459	(9)
10.5 Programmatically Track Users and Groups	468	(6)
10.6 Adjust an Application Based on Who's Logged In	474	(3)
10.7 List All Users with Blank Passwords	477	(4)
10.8 Track Which Users Have a Shared Database Open	481	(3)
10.9 Determine if a Record Is Locked and by Whom	484	(4)
10.10 Set a Maximum Locking Interval for a Record	488	(6)
11. Windows APIs	494	(59)
11.1 Remove a Form's System Menu and Maximize/Minimize Buttons	495	(4)
11.2 Flash a Window's Titlebar or Icon	499	(3)
11.3 Classify Keypresses in a Language-Independent Manner	502	(3)
11.4 Restrict Mouse Movement to a Specific Region	505	(2)
11.5 Run Another Program and Pause Until It's Done	507	(4)
11.6 Exit Windows Under Program Control	511	(3)
11.7 Run the Application Associated with a Data File	514	(5)
11.8 Check to See if an Application Is Already Running	519	(4)
11.9 Retrieve a List of All Top-Level Windows	523	(3)
11.10 Close a Running Windows Application	526	(2)
11.11 Set File Date and Time Stamps	528	(3)
11.12 Retrieve Information About Available Drives	531	(4)
11.13 Collect and Display Information on the System and the Access Installation	535	(9)
11.14 Create and Cancel Network Connections Programmatically	544	(9)
12. Automation	553	(63)
12.1 Play an Embedded Sound File from Within an Application	554	(5)
12.2 Print an Access Report from Excel	559	(5)
12.3 Use Excel's Functions from Within Access	564	(9)
12.4 Perform a Mail Merge from Access to Word	573	(4)
12.5 Add an Item to the Startup Group	577	(11)
12.6 Send Access Data to Excel and Create an Excel Chart	588	(7)
12.7 Create a PowerPoint Presentation from Access Data	595	(14)
12.8 Add a Contact and Send Email Through Outlook	609	(7)
13. Data Access Pages	616	(29)
13.1 Replace Navigation Button Images with Your Own Images	617	(3)
13.2 Use Labels or Other Controls for Record Navigation	620	(2)
13.3 Change the Text Displayed with a Navigation Control	622	(2)
13.4 Create a DAP that Allows You to Update Data	624	(2)
13.5 Create One File to Store Connection Information for All DAPS in an Application	626	(6)
13.6 Programmatically Change the Connection String for All Pages in a Database	632	(4)
13.7 Change the Default Settings for New DAPs	636	(3)
13.8 Use Parameters Set in One DAP to Open Another	639	(6)
14. SQL Server	645	(29)
14.1 Dynamically Link SQL Server Tables at Runtime	645	(6)
14.2 Dynamically Connect to SQL Server from an ADP	651	(3)
14.3 Share an ADP from a Shared Network Folder	654	(2)
14.4 Fill the Drop-Down Lists When Using ServerFilterByForm in an ADP	656	(3)
14.5 Pass Parameters to Stored Procedures from Pass-Through Queries in an MDB	659	(4)
14.6 Pass Parameters to Stored Procedures from an ADP	663	(2)
14.7 Use Controls as Parameters for the Row Source of Combo and List Boxes in an ADP	665	(3)
14.8 Reference Data from More than One SQL Server Database in an ADP	668	(2)
14.9 Use Views to Update Data in an ADP When Users Don't Have Permissions on Tables	670	(4)
15. Office Web and SharePoint	674	(24)
15.1 Work with SharePoint Data from Access	674	(3)
15.2 Create a SharePoint Data View Web Part Based on Access Data	677	(3)
15.3 Conditionally Format a Data View Web Part	680	(2)
15.4 Create a Master/Detail Page using Data View Web Parts	682	(5)
15.5 Post Web Form Data to an Access Database	687	(5)
15.6 Create a Web Frontend to an Access Table Using FrontPage	692	(6)
16. Smart Tags	698	(25)
16.1 Use the Built-in Smart Tags	699	(3)
16.2 Display Multiple Smart Tags on Multiple Forms	702	(2)
16.3 Display Smart Tags when Application Starts	704	(2)
16.4 Execute a Smart Tag Action Without Displaying the Smart Tag	706	(3)
16.5 Create a Smart Tag on a Table in an Access Project	709	(2)
16.6 Create a Custom Smart Tag to Get a Weather Report	711	(3)
16.7 Create a Custom Smart Tag DLL	714	(9)
17. .NET	723	(28)
17.1 Call a .NET Component from Access	724	(4)
17.2 Call a .NET Component Containing a Parameterized Constructor	728	(3)
17.3 Retrieve Access Data from an ASP.NET Application	731	(3)
17.4 Call a Web Service from Access	734	(4)
17.5 Work with a Web Service that Returns a DataSet	738	(6)
17.6 Print an Access Report from .NET Windows Form Application	744	(7)
18. XML	751	(32)
18.1 Import XML Structured as Elements	755	(2)
18.2 Import XML into Existing Tables	757	(3)
18.3 Import XML Using a Schema (XSD)	760	(4)
18.4 Export XML	764	(3)
18.5 Exporting Selected Columns to an HTML Table	767	(4)
18.6 Export Unrelated Tables	771	(2)
18.7 Export Using a Where Clause	773	(2)
18.8 Export a Report	775	(8)
Index	783