How This Book Is Organized | Access Data Analysis Cookbook (Cookbooks)

This book is organized into 18 chapters, each of which focuses on a particular Access programming topic.

Chapter 1: This chapter covers the many types of queries and the power you have over the Access environment through the use of queries. From simple select queries through parameter, crosstab, totals, and Data Definition Language (DDL) queries, this chapter will show many different ways to use queries in your applications. Queries are the real heart of Access, and learning to use them intelligently will make your work in Access go much more smoothly.
Chapter 2: Most database applications require some sort of user interface, and in Access, that user interface is almost always centered around forms. This chapter demonstrates some useful ways to make forms do your bidding, whether in terms of controlling data or making forms do things you didn't think were possible. We demonstrate how to create multipaged forms and how to create an incremental search list box. We also show how to create your own pop-up forms, with a technique you can use in many situations. Forms can do much more than you might have imagined, and this chapter is a good place to look for some new ideas.
Chapter 3: It seems as though reports ought to be simple: just place some data on the design surface and "let her rip!" That's true for simple reports, but Access's report writer is incredibly flexible and allows a great deal of customization. In addition, the report writer is quite subtle in its use of properties and events. The topics in this chapter will advance your understanding of Access's report writer, from creating snaking column reports to printing alternating gray bars. Some of the solutions in the chapter will require programming, but many don't. If you need to create attractive reports (and everyone working with Access does, sooner or later), the topics in this chapter will make your work a lot easier.
Chapter 4: This chapter is a compendium of tips and suggestions for making your application development go more smoothly, more professionally, and more internationally. Rather than focusing on specific topics, this chapter brings up a number of issues that many developers run across as they ready their applications for distribution. How do you build a list of objects? How do you make sure all your objects' settings are similar? How do you translate text in your application? How do you use the common Windows dialogs? All these questions, and more, make up this group of tips for the application developer.
Chapter 5: Many developers need to gain tight control over printed output, but earlier versions of Access made this quite difficult. Starting with Access 2002, you'll find direct support for selecting a specific printer device, changing print layout settings, and more. This chapter introduces the Printer object and its properties, allowing you to perform tricks that were difficult, if not impossible, in earlier versions. (Although many of the chapter databases will work in Access 2000, this chapter's examples will not. Because the functionality presented here was new in Access 2002, the samples simply won't do anything useful in Access 2000.)
Chapter 6: This chapter concentrates on working with data in ways that traditional database operations don't support. You'll learn how to filter your data, back it up, locate it on the filesystem, calculate a median, perform sound-alike searches, save housekeeping information, and more. Most examples in this chapter use some form of VBA, but they are clearly explained, and "testbed" applications are supplied to show you how each technique works.
Chapter 7: The solutions in this chapter cover some of the details of VBA that you might not find in the Access online help. We've included topics on several issues that plague many Access developers, from handling embedded quotes in strings and creating procedure stacks and code profilers, to programmatically filling list boxes, to working with objects and properties. We've included code to sort an array and solutions that combine several of the previous topics, such as filling a list box with a sorted list of filenames. If you're an intermediate VBA programmer, this chapter is a good place to expand your skills. If you're already an expert, this chapter can add some new tools to your toolbox.
Chapter 8: Access is a big application, and when designing applications you have a number of choices to make, each of which can affect the application's performance. Unless you're creating only the most trivial of applications, you'll have to spend some time optimizing your applications. This chapter's topics work through several different areas of optimization steps you can take to make your databases work as smoothly as possible. The topics range from optimizing queries, forms, and VBA, to testing the speed of various optimization techniques, to accelerating client/server applications. If you want your applications to run as quickly as possible, this chapter is a good place to look for tips.
Chapter 9: This chapter presents a compendium of user interface tips and techniques. By implementing the ideas and techniques in this chapter, you'll be able to create a user interface that stands out and works well. You'll find some simple, but not obvious, techniques for controlling the Access environment, such as altering your global keyboard mappings as you move from one component of your application to another and creating forms that hide the menus and toolbars when they're active. The chapter shows how to create combo boxes that accept new entries and how to provide animated images on buttons. You'll also find useful tips on working with data on your forms, using an ActiveX control to improve your interface.
Chapter 10: Few modern database applications run on standalone machines; most must be able to coordinate with multiple users. This chapter offers solutions to some of the common problems of networking and coordinating multiple simultaneous users. The most important issues are security and locking, and this chapter has examples that cover each. In addition, the topics in this chapter focus on replication, transaction logging, password control, and keeping users from holding locks on data. If you're working in a shared environment, you won't want to miss this chapter!
Chapter 11: No matter how much you've avoided using the Windows API in Access applications, in this chapter you'll discover that it's really not a major hurdle. We'll present some interesting uses of the Windows API, with example forms and modules for each solution. In most cases, using these examples in your own applications takes little more work than importing a module or two and calling some functions. You'll learn how to restrict the mouse movement to a specific area on the screen, how to run another program from your VBA code, and how to wait until that program is done before continuing. We'll demonstrate a method for exiting Windows under program control and how to retrieve information about your Access installation and the current Windows environment. The possibilities are endless once you start diving into the Windows API, and this chapter is an excellent place to start.
Chapter 12: This chapter gives you examples of using Automation to interact with most of the Microsoft Office applications. One solution uses the statistical, analytical, and financial prowess of the Excel function libraries, directly from Access; another shows how to programmatically create an Excel chart. You'll learn how to retrieve document summary information for any selected Word document and how to perform mail merges using Access data. Other examples demonstrate how to use Access to control PowerPoint and how to add contacts in Outlook.
Chapter 13: Distributing Access applications normally means that your users have to install Access (or the Access runtime version, available as part of Microsoft Office XP Developer) on their local machines. What if users could run your applications over a corporate intranet, without requiring Access to be installed? That's the goal of DAPs. This chapter introduces some of the concepts you'll need to understand in order to take advantage of this feature, which was added in Access 2000 and significantly improved in Access 2002. You'll learn how to customize the navigation controls and how to use your own controls for navigation. You'll find tips on creating pages that allow users to update data and valuable techniques for managing your data connections, and you'll learn how to adjust the default settings for the different sections of new DAPs to give your applications a consistent look.
Chapter 14: This chapter shows you how to take advantage of the new data options available in Access Data Projects, which connect directly to a SQL Server database, and provides solutions that address traditional MDB databases linked to SQL Server data. You'll learn how to dynamically connect to SQL Server at runtime, whether you are using an ADP or an MDB, and you'll learn how to allow multiple users to share a single ADP. You'll see how to make the most of the Server Filter By Form feature in ADPs and how to pass parameters to stored procedures in both ADPs and MDBs. You'll also discover how you can use an ADP to connect to multiple SQL Server databases at once, even though the ADP seems to force you to select a single one.
Chapter 15: As powerful as Data Access Pages are, they only represent one way to gain access to your Access data from a browser. This chapter introduces you to other Microsoft Office web technologies you can use to "webify" your Access databases. You'll learn how to use Microsoft FrontPage to create a web form that posts its data to an Access database. You'll also learn how to use the FrontPage Database Interface Wizard to create an ASP or ASP.NET front end to an Access database. You'll learn how to use Windows SharePoint Services along with FrontPage to create web pages that draw data from Access databases without writing any code. You will also learn how to use Access as a frontend for managing SharePoint lists.
Chapter 16: Smart Tags were introduced in Office XP, but they weren't available in Access until now. This chapter shows you how to use the built-in smart tags in your applications, attaching them to form controls or to fields in a table. You'll learn to configure smart tags interactively or by writing code. You'll also learn how to extend smart tag functionality by creating your own custom smart tags.
Chapter 17: Microsoft .NET and Access live in two different programming worlds, but you can use a set of interoperability tools to bridge the two worlds. This chapter shows you how to take advantage of these tools to call a .NET component from an Access application. You'll also learn how to call a .NET web service from Access, and how to manipulate the .NET objects returned by some web services. You'll learn how to retrieve data from an Access database using ADO.NET. And you'll learn how to automate an Access report from a .NET application.
Chapter 18: One of the strengths of Access is its ability to work with data from many disparate sources. XML has emerged as a dominant standard for exchanging data between applications, and Access now enables you to work with this data. In this chapter you'll learn how to import and export XML data and schema, and how you can use XSLT to reformat XML data. For example, you'll see how to use XML technologies to export a report to an HTML or ASP Web page, preserving the look and feel of the original Access report.