Who's the book for, what's covered, and what are the system requirements?

I cover three topics in this introduction. First I discuss you, the reader. Briefly, this book is written for experienced Microsoft Access developers with an intermediate or advanced proficiency. Of course, if you are an aspiring intermediate Access developer, this book may also help you on your way. Then I discuss the book's contents, summarizing its four parts and then summarizing each chapter in more detail, highlighting the new, nearly new, and traditional topics. I also discuss the book's companion CD and product support information. Finally I conclude with a description of system requirements necessary to run the code samples included in this book. The exact system requirements vary slightly from chapter to chapter; however, I successfully ran all the samples on a computer running Microsoft Windows 2000 and Microsoft Office XP Developer and one running Microsoft Windows NT and Office XP Developer.

Introduction to VBA

Data Access Models: Part I

The subject of data access models is vast. To make this discussion more digestible, I've broken it into two chapters. This chapter and Chapter 3, "Data Access Models: Part II," focus primarily on data access for Jet with the ADO object model. You’ll learn about the 2.6 and 2.7 versions of ADO that go beyond the 2.1 version introduced with Access 2000.  The two chapters also include some coverage of ADO with other database engines—particularly Microsoft SQL Server. Numerous programming samples show you how to accomplish typical database chores. This chapter begins with an overview of ActiveX Data Objects and then moves on to discuss the Connection, Recordset, and Field objects.

Data Access Models: Part II

This is the second of two chapters covering data access models. As I mentioned at the beginning of Chapter 2 ("Data Access Models: Part I"), the topic of data access models is extensive, which is why I've broken it into two chapters. Chapter 2 presented you with an overview of ActiveX Data Objects (ADO) and examined the Connection, Recordset, and Field objects. In this chapter, you'll learn about the Command and Parameter objects and the Errors collection. I'll also show you several techniques for managing access to the files on a Web server, demonstrate the features of the new Record and Stream ADO objects, and explore further the new functionality of the Recordset object. Furthermore, the end of this chapter presents a discussion of ADO event programming and provides code samples.

The ADOX Library and Tables

The main objective of this chapter is to equip you with the skills needed to start designing tables with the ADOX object model for Jet databases. This chapter assumes you have an intermediate to advanced knowledge of table design. However, the chapter also assumes you have a novice or intermediate level of experience with the ADOX library. This will be the case for all experienced Microsoft Access developers switching from Access 97 and earlier versions to Access 2002. Even if you have had moderate exposure to ADOX programmatic table design, such as that discussed in the preceding edition of this book, this chapter will reinforce your existing ADOX skills and prepare you for more advanced programmatic table design. The chapter concludes with a series of samples that illustrate how to populate tables with data after creating them with the ADOX model. The samples focus primarily on gathering data from sources outside the current Access project.

Jet SQL, the ADOX Library, and Queries

This chapter contains five major sections. The first section, "Jet SQL Syntax for Select Queries," will examine the most common Jet SQL formulations for returning records from tables. Learning Jet SQL liberates you from depending on the Microsoft Access Query Designer to develop queries. Mastering Jet SQL will also help you expand the roles that recordsets play in your applications.

The second section, "Enumerating ADOX Views and Procedures," demonstrates how to enumerate the members of the ADOX Views and Procedures collections and their most important properties, such as the SQL text behind them. These two collections contain what Access developers typically refer to as queries.

The third section, "Managing Row-Returning Queries with ADOX," will show you how to create new queries programmatically by adding members to the Views and Procedures collections. This section also demonstrates how to add or modify queries in a database even when you temporarily disconnect your workstation from the database.

"Creating and Running Action Queries with ADOX" drills down on the design and use of parameter queries. This section pays special attention to using parameters for inserting, deleting, and updating records from a record source.

This final section of this chapter, "Introduction to Jet SQL's DDL," discusses the use of Jet SQL for table definition tasks. By learning how to perform data definition with Jet SQL, you can take advantage of features not supported by the ADOX library, such as creating check constraints for columns.

Forms, Controls, and Data

This chapter discusses how to use Microsoft Access forms with data and offers a general introduction to working with forms and their controls.  The chapter's early samples illustrate techniques for developing and presenting application splash screens and switchboard forms. In the process, you'll get an introduction to the form's timer event and ways of using hyperlinks to invoke VBA procedures.

Next, the focus shifts to using forms with data. A progression of examples and samples demonstrate techniques building simple bound forms and linking them to programmatically controlled record sources. Particular attention is paid to how to use ADO recordsets as record sources for forms. After an introduction to using forms with record sources, the chapter switches its focus to main/subform design and data management issues. The presentation of this topic focuses on the parent-child relationship between the data behind the form. You'll learn about the relationships between main/subforms and subdatasheets for tables. The code samples demonstrate how to reference and manipulate the controls in subforms and their sub-subforms. Two additional collections of samples round out the treatment of data via forms. One of these sample collections demonstratesconditionally formatting the display of data values on forms. The other collection dwells on techniques for looking up and displaying data via Access forms.

The chapter concludes with two sections that treat programmatic issues related to form development. The first demonstrates techniques for enumerating and managing the status of forms in applications, including working with the forms in another Access database file. The second section introduces form classes.

Reports, Controls, and Data

The chapter begins with an introduction to manual techniques for building reports, but it also includes a demonstration of their value for creating reports programmatically. Using one of the many Access report wizards will often be a great first step to developing a custom report. Instead of building a default layout, you can spend your time customizing a wizard-generated report. This can include customizing the record source as well as making the report's content or formatting responsive to run-time specifications. If you're going to programmatically create new reports or edit existing ones, you'll also require a solid understanding of the Access report architecture and its Design view environment. The summary of manually created reports covers these topics as well.

The second collection of topics in this chapter deals mostly with programmatic issues. It starts out by covering approaches for creating and deploying snapshots of Access reports. Next, the chapter drills down into a variety of ways for making existing reports dynamic through programming. Finally, the chapterends with a case study that demonstrates how to work with a Microsoft FrontPage–generated guestbook. This case study starts out with cleaning and editing data and carries through to report generation.

Designing and Using PivotTables and PivotCharts

This chapter explores the capabilities of PivotTables and PivotCharts in Microsoft Access 2002.  PivotCharts appear for the first time in Access in the 2002 version. Moreover, Access 2002 substantially improves upon the PivotTable capabilities of prior versions of Access.

Both PivotTables and PivotCharts enable you to display data in an interactive manner. End users are likely to find PivotTables and PivotCharts appealing because of their easy-to-use, interactive interface. And programmers can readily compute the data depicted in PivotTables and PivotCharts based on their clients' record sources. The usefulness of programmatically constructed PivotTables and PivotCharts might render obsolete some traditional data analysis tools, such as crosstab queries and charts based on older technology. As the popularity of PivotTables and PivotCharts grows among end users, the demand for programmers who can readily create and edit these display devices will increase as well.

Class, Form, and Report Modules

This chapter first introduces standalone class modules and form and report class modules. Then it demonstrates simple ways to build classes into your Access applications and to develop custom properties and methods. Next comes a case study that uses three forms, a few custom Property Get and Property Let functions, and a couple of techniques based on ADO to build a custom password security application. The section after the case study shows the syntax for programming events into your custom classes and introduces the WithEvents keyword.

The focus then shifts to the containers for class, form, and report modules as we look at the All collections that were introduced in Access 2000. Just as there are AllForms and AllReports collections, there is an AllModules collection. (In fact, there are a total of 10 All collections.) The chapter wraps up by explaining how to combine the AllModules collection with the Modules collection to manage code in an application.

Microsoft Office Objects

This chapter starts with an overview of Office objects. After the overview, we'll examine the DocumentProperty object and related Access objects from Access database and Access project files. Within this discussion, I'll drill down on techniques for replacing the Database window with a custom startup form. Next we'll explore three specific objects: FileSearch, which you use to programmatically manage file searches; Assistant, which provides a programmatic interface to the Office Assistant; and CommandBars, which you use to create custom menus and toolbars.  The CommandBars commentary includes commentary and a code sample to secure your custom applications.

Integrating Access with Other Office Applications

This chapter explains how to programmatically integrate Access 2002 with the other Office applications using built-in Access features. For instance, your applications can tap installable indexed sequential access method (ISAM) drivers through the Connection object to work with the data in a Microsoft Excel spreadsheet. A Connection object based on an ISAM driver can serve as a two-way data-sharing channel between Access and Excel. An ISAM driver also enables Access developers to build solutions that retrieve data from Microsoft Outlook.

Furthermore, you can tap Access data sources programmatically with the Microsoft Word mail merge capability to facilitate creation of mailing labels, form letters, and product catalogs. Developers familiar with programming conventions for Word's rich formatting options can employ Word as a reporting vehicle for data otherwise managed by Access.

Using Automation, your applications can simultaneously exploit the object models from two or more Office applications. For example, an application can export names and addresses from an Access data store to an Outlook Contacts folder. Similarly, you can populate values to tables in a Word document from an Access data source. The samples in this chapter focus on Access, Outlook, and Word, but the general principles extend to other Office applications as well as third-party packages that expose their object models through Automation and that enable manipulation using Microsoft Visual Basic for Applications (VBA).

Working with Multiuser Databases

This chapter assumes you have a working knowledge of basic multiuser and security issues so that it can focus on the programmatic issues. For example, the chapter includes samples that compare row-level locking with the more traditional page-level locking for recordset manipulation, as well as samples that show how to control security programmatically with the ADOX library (as opposed to using Jet SQL and the ADODB library). The chapter closes with a sample demonstrating transactions in a multiuser environment.

Replicating Databases

Access 2002 is the fourth version of Access to offer database replication, so this feature is mature and highly integrated. Database replication changed significantly with Access 2000, and Microsoft left those changes intact in Access 2002 without introducing any new ones. Therefore, this chapter presents an overview of replication, as well as the new replication features introduced with Access 2000 for those migrating to Access 2002 from Access 97 or an earlier version. The chapter also explains how to manage replication using the JRO 2.5 (or 2.6) library. (JRO stands for Jet and Replication Objects.) Microsoft introduced the JRO library with Access 2000 and then updated it in Access 2002 for compatibility with the other ActiveX Data Objects (ADO) components. The code samples in this chapter will benefit those who are just starting to program replication solutions. They also will help those who already know how to program replication with Data Access Objects (DAO) to make the transition to ADO. The JRO discussion in this chapter also includes advanced topics, such as processing system and hidden tables, as well as areas that do not explicitly relate to replication.

Using Access to Build SQL Server Solutions: Part I

Access 2002 offers tight integration with SQL Server 2000, and Access 2000 has a feature set tailored for SQL Server 7. You can obtain upgrades and patches to make Access 2000 compatible with SQL Server 2000. This chapter focuses almost exclusively on using Access 2002 with either SQL Server 2000 or the Microsoft SQL Server 2000 Desktop Engine.

The SQL Server 2000 Desktop Engine ships with any version of Microsoft Office XP that includes Access 2002. This engine version—abbreviated as MSDE 2000 throughout the chapter—is an upgrade to the Microsoft Date Engine (MSDE) that shipped with Office 2000. MSDE 2000 is compatible with SQL Server 2000, while its predecessor—MSDE—is compatible with SQL Server 7. Besides its upgraded version of MSDE, Access 2002 ships with other enhancements that make it better suited than Access 2000 for developing SQL Server 2000 solutions. This chapter highlights these enhancements while explaining the basics of developing SQL Server solutions with Access.

Using Access to Build SQL Server Solutions: Part II

This chapter continues the saga of how to build client/server solutions with Access projects. However, this chapter switches the focus, at least partially, to the client-side tools. Several early sections in the chapter examine how to use forms with SQL Server record sources. These are followed by another section on building solutions with reports. Do not feel that you are limited to the form and report techniques covered in this chapter. In fact, most techniques for applying forms and reports in Access database files also apply in Access projects; this chapter describes selected differences. This capability to use forms and reports means that you can refer back to the coverage of these topics in earlier chapters for more ways to create SQL Server solutions with Access.

Another focus of this chapter is how to program SQL Server solutions with Microsoft Visual Basic for Applications (VBA) procedures in modules within Access projects. This chapter pays particular attention to programming SQL-DMO, the SQL Server Distributed Management Objects model. With this model, you can control any aspect of SQL Server administration and data definition. After introducing the basics of SQL-DMO programming, the chapter presents several samples of how to apply SQL-DMO.

The last focus of the chapter is programming SQL Server security. Programming SQL Server security is substantially more important with Access 2002 than with Access 2000 because the graphical UI for SQL Server security from Access 2000 is dropped in Access 2002. This chapter's presentation of the topic builds on the SQL-DMO programming skills developed earlier in the chapter. Use the samples in this chapter as a foundation for learning even more about programmatically specifying security for your multiuser Access project solutions.

Chapter 16 - Access Does the Web: Part I

After providing a Web development overview targeting the special needs of Access developers, this chapter gives a quick introduction to Microsoft FrontPage 2002. Because FrontPage 2002 ships with Office XP, it's a natural tool for managing the Web sites that make your solutions available to users. The chapter next presents some step-by-step examples of creating forms and reports on Web pages using graphical design techniques. These forms and reports bind directly to the Access database files and Microsoft SQL Server databases described throughout this book. I'll show you how to make these forms and reports dynamic and interactive by reviewing the new (since Access 2000) integrated development environment (IDE) behind them. You'll also see several samples in the chapter that illustrate how to build event procedures. Furthermore, you'll learn some easy ways to tackle advanced topics, such as programmatically creating Web-based forms and reports. Access 2002 enables you to create new Web-based forms and reports on the Access forms and reports you previously created.

Access Does the Web: Part II

This chapter is divided into three major sections. The first section explores Access-based techniques for publishing datasheets over webs, both statically and dynamically. Despite being Access based, these techniques permit you to share any data source to which Access can connect. Furthermore, these techniques enable users to view datasheets published with them from any browser that reads HTML. The second section in the chapter explores Microsoft FrontPage–based techniques for publishing datasheets. The carefully constructed examples and instructions in this section show the flexibility and ease of using the FrontPage Database Wizard. This section also discusses how to create forms for searching a database, including a datasheet to display search results. The chapter's third section introduces programmatic techniques for creating forms that dynamically display the contents of a database and permit users to interact with that database in ways not possible with a standard Web-based datasheet.

Microsoft Office XP Developer

The MOD XP version of Office is a significant upgrade that offers exceptional value for professional or aspiring Access developers. This appendix provides an overview of MOD XP, paying special attention to how it serves the needs of Access developers.

There are at least three database-related reasons Access developers should acquire MOD XP instead of the other editions of Office XP. The first reason is that MOD XP makes it easy to deploy solutions built on a developer's workstation to one or more client workstations. The second reason is that this edition offers special resources for building workflow solutions. The third reason is its graphical tool for facilitating the management of Jet-based replica sets. In addition, some excellent developer productivity add-ins ship with MOD XP, further enhancing the value of the package for Access programmers.