Who's the book for, and what's New with Access 2000?

This book is for database developers who want to use Microsoft Access 2000 to build custom applications. It covers the features that make Access a perennial favorite with developers, as well as the innovations in Access 2000 that you are most likely to use in your programming projects. Microsoft has created a profoundly new product that still feels like the Access you know. This book highlights five specific areas of innovation: ActiveX Data Objects (ADO), enhanced SQL Server interoperability, Visual Basic for Applications (VBA) and packaging enhancements, Microsoft Jet engine improvements, and improved Web interoperability.

An Introduction to VBA

This chapter will cover seven aspects of VBA in Access. These are: (1) Collections, objects, properties, methods, and events; (2) Procedures and modules; (3) The VBE interface; (4) Jet, data types, and declarations; (5) Conditional logic and looping constructs; (6) Built-in functions; (7) Debugging and error trapping.

Data Access Models

This chapter will focus primarily on the ADO object models for Jet in Access and the ADODB and ADOX libraries. Extensive programming examples will show you how to accomplish typical database tasks. Other chapters in this book will build on the information in this chapter and will cover additional ADO topics, such as database replication, remote database access, and multi-user security.

Designing Tables

This chapter starts by briefly examining general table design issues. Next, it explores how to create tables with Microsoft Access 2000 manually and by using the Access wizards. It also briefly reviews indexes, primary keys, and how to form relationships among tables. This chapter also describes how to create and populate tables in Access programmatically for constructing dynamic data-based custom solutions. The companion CD includes many ActiveX Data Objects (ADO) samples that illustrate the creating and populating of Access tables. These samples build on the introductory ADO coverage in Chapter 2. One set of samples shows how to combine ADO and the easy-to-use DoCmd object to dynamically interact with Indexed Sequential Access Method (ISAM) and open database connectivity (ODBC) data sources.

Manipulating Data Using Queries in Access 2000

Queries are the workhorses of database applications. One major reason why queries are so potent is that there are so many different varieties of them. This chapter explores queries within Access 2000 at multiple levels. First, it gives a general overview of the different types of queries. Second, the chapter describes manual techniques for building queries. It reinforces the general discussion of query types with the steps to generate each kind. Third, the chapter explores ways for developers to use VBA with select and action queries to perform typical database chores. The fourth section illustrates more advanced programmatic techniques that integrate SQL. This section complements the ADO-centric discussion of views and stored procedures from Chapter 2. The final section addresses processing techniques for remote databases. Access 2000 is much more adept at working with remote data sources than earlier versions. Learning how to use queries to easily tap into remote data sources can dramatically enhance your solutions and your value as a developer.

Building User Interfaces with Forms

This chapter begins by explaining how to create an exciting first impression with a splash screen form. It then explores how to make forms (particularly data-bound forms) interact with data. You'll also learn about conditional formatting and performing lookup operations with forms. Finally, you'll learn how to programmatically reference forms and toggle their visibility, as well as how to work with form class modules.

Creating Reports

This chapter explores some pragmatic design and display issues for reports. It also covers programming for dynamic content in a report as well as the enumeration and manipulation of reports and their controls. The chapter presents a case study of a Microsoft FrontPage guestbook that addresses the typical small business and departmental needs that Access often serves. Two more sections demonstrate report design issues. Another short section explores presenting Access reports via the snapshot file format. This format makes it easy to share Access reports through email and over the web even on workstations that do not have Access installed. The chapter closes with three sections that demonstrate specific programmatic issues. The first two deal with dynamic content on a report. The final section explores the programmatic manipulation of reports and their controls. This last section illustrates techniques for processing different types of AccessObject objects, such as members of the AllReports and AllForms collections.

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 ActiveX Data Objects (ADO) to start building an 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 are new to Access 2000. Just as there are AllForms and AllReports collections, there is an AllModules collection. (In fact, there are ten All collections altogether.) 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 and then focuses on three specific objects: FileSearch, which you use to programmatically manage file searches; Assistant, which provides a programmatic interface to the Office feature called the Office Assistant; and CommandBars, which you use to create custom menus and toolbars.

Integrating Access with Other Office Applications

This chapter explains how to programmatically integrate Access 2000 with the other Office applications using built-in Access features. Your applications can tap installable 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. 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. 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 a Microsoft 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 VBA.

Working with Multi-User Databases

This chapter discusses some core multi-user issues: sharing files and controlling security. In reviewing file sharing, this chapter explicitly examines techniques and issues for sharing forms and recordsets. Subsequent chapters explore technologies for sharing Access databases, such as database replication, using Microsoft SQL Server, and using Access databases over the World Wide Web. This chapter assumes that you have a working knowledge of basic multi-user and security issues and focuses on new programmatic developments. For example, it includes samples that compare the new row-level locking with the more traditional page-level locking for recordset manipulation and samples that show how to control security programmatically with the new ADODB and ADOX libraries. The chapter closes with a sample demonstrating transactions in a multi-user environment.

Replicating Databases

This chapter offers an overview of replication and introduces the new replication features in Access 2000. It also explains how to manage replication using the JRO 2.1 library. The code samples will benefit those who are just starting to program replication solutions and will help those who already know how to program replication with Data Access Objects (DAO) to make the transition to ActiveX Data Objects (ADO). The JRO discussion in this chapter also extends to areas that do not explicitly relate to replication.

Building Solutions with MSDE and Access Projects

In this chapter, you'll learn how to install and manage MSDE and how to develop custom applications with Access Projects. You'll learn how to work with tables, views, stored procedures, reports, and forms. The code samples will illustrate how to automate your Access Project applications with ActiveX Data Objects (ADO) and Microsoft Visual Basic for Applications (VBA). (Chapters 3 and 4 cover OLE DB routes to other remote database formats besides SQL Server.)

Access 2000 Does the Web

Access 2000 offers data access pages, fresh ActiveX controls, improved database connectivity, and enhanced Dynamic HTML (DHTML). These new capabilities mark the passing of the Publish To The Web wizard from Access 97, which packaged an interface for the HTML Layout control. This control was never more than an interim technology awaiting a standards-based DHTML.

This chapter will review traditional Access Web technologies and then cover hyperlink technology, including the Hyperlink object and the Hyperlink data type. The bulk of the chapter is devoted to data access pages, which facilitate reporting on and interacting with databases over the Web. The development environment for data access pages can also serve as a host for Microsoft Office 2000 Web Components.

The Office 2000 Developer Edition

This chapter offers a brief overview of the ODE feature set and explores topics of interest to Access developers (and developers generally, in the case of digital signing). Code samples illustrate the use of ADO in Excel and demonstrate how to detect digital signatures for projects with VBA.  Other sections examine the new Code LIbrarian and the improved Package and Deployment wizard.

Third-Party Sample Applications and Demos on the Companion CD

This book's companion CD includes content from three third-party vendors—Database Creations, FMS, and Visio—all of whom provide quality support for Microsoft Access developers.

The CD's content can expand your prospective development opportunities in two major directions. First, the sample applications and demos include accounting and statistics applications that you, as an Access developer, can leverage in expanding your Access development business into new markets. Accounting and statistics vertical market opportunities offer rich rewards for those who pursue them successfully. Second, the CD contains tools for Access and Microsoft Visual Basic developers that can enrich and simplify developing classic custom database applications.