ODE Overview

ODE delivers benefits in three main areas: Microsoft Visual Basic for Applications (VBA) productivity, data access, and deployment and management. ODE is for the millions of professional developers who build solutions with Office, VBA, and data access tools. Many of these solutions use the powerful data access capabilities of Access from within another Office application capabilities.

ODE include these Microsoft products:

• Microsoft Word 2000
• Microsoft Excel 2000
• Microsoft PowerPoint 2000
• Microsoft Access 2000
• Microsoft Outlook 2000
• Microsoft Publisher 2000
• Microsoft FrontPage 2000
• Microsoft PhotoDraw 2000
• Microsoft Small Business Tools

ODE includes documentation and samples that help you build solutions within and across these applications. VBA is available throughout the Office product line, but one difficulty in becoming productive with a new application is learning the object model. The ODE printed documentation includes a brochure with graphical representations of the object models for the above applications (except PhotoDraw, which does not have one). It documents 24 object models that are either for the major applications, shared by those applications, specialized for data access services, or targeted at Office Web technologies.

VBA Productivity Tools

The VBA productivity tools help you program faster in all the Office applications that support VBA. They range from a simple string editor for building SQL statements to the Component Object Model (COM) Add-In Designer, which simplifies the building of dynamic link libraries (DLLs) as add-ins in two or more Office applications.

The COM Add-In Designer offers an integrated approach to building and using add-ins across Office 2000 applications. Previous versions of Office required different approaches for building, storing, and invoking add-ins.

The Code Librarian is a database that shares code snippets, functions, and modules among members of a development team. An individual developer can also use it to retrieve previously built code. You can add your own custom code to the database and search it by keyword and other criteria. The Code Librarian comes with a starter kit of code for common programming tasks.

The VBA Code Commentator and VBA Error Handler automatically update your modules to perform standard tasks that are essential for many custom development efforts. The Code Commentator adds comments, such as your name, the date, and the parameters that you must send to procedures. It builds this information from a customizable template that lets developers within a large organization modify the basic process to meet their own requirements. The Error Handler builds standardized error-handling code for all the procedures in an application. Again, you can customize its template. Beyond that, of course, the Error Handler provides a framework for adding your own custom error-handling code.

The VBA String Editor is a dialog box for writing, editing, copying, and pasting custom SQL statements into VBA code. It is a simple tool that performs a single task that can become quite complicated.

ODE also simplifies team development by offering Microsoft Visual SourceSafe, whose check-out and check-in features let one member of a team work on one or more VBA modules while other members work on quality control or system integration features for the rest of the application. Visual SourceSafe also makes it easy to compare versions of VBA code as well as restore prior versions.

The VBA Multi-Code Import/Export add-in facilitates importing and exporting code from a module. You load this feature as a single add-in, but it has two separate user interfaces—one for importing code and one for exporting it.

The printed and online documentation available through the ODE helps you improve your development skills and explains cross-component application development techniques. In addition to the printed object models, there is a VBA Programmer's Guide. A special collection of Microsoft Developer Network (MSDN) CDs includes all the printed documentation as well as additional documents; all have search engine support.

Data Access

ODE offers a host of tools that support data access development. Perhaps the single, brightest jewel in this set is the redistributable MSDE (Microsoft Data Engine). You can ship a royalty-free, stand-alone version of your custom application that works with MSDE. This is especially appropriate for prototyping applications that might grow into full-scale SQL Server applications. The MSDE footprint is smaller than SQL Server's, so you can use it when SQL Server resource requirements are too demanding. You can also build custom forms and reports against SQL Server with the new Access Project. (See Chapter 12 for more on MSDE and Access Projects.)

For the best backward compatibility, you should build solutions with the Microsoft Access Run Time. This package emulates Access 2000 and Jet without the user interface. Your custom application is the only exposed interface. This royalty-free package lets you ship solutions that work at the department level and are unlikely to demand significantly more resources over time. For example, you can use the Access Run Time to build a solution for a utility plant. Utility plants have relatively long lifetimes, and they often operate at or near an optimal level. In this kind of environment, a system's requirements are unlikely to expand significantly over the lifetime of an application.

The ADO Data Control and the Data Environment Designer offer graphical access to ADO and ODBC data sources. The ADO Data Control is familiar to those who used the ActiveX User Connection Designer to create Remote Data Objects at design time. The ADO Data Control permits connection to one data source at a time, and you can navigate to the next, previous, last, and first records in its recordset. With the Data Environment designer, you can create multiple Connection objects, each of which has multiple Command objects with corresponding recordsets. The Data Environment Designer lets you drag and drop fields from a Command object to Microsoft Forms and to the Data Report Designer.

These graphical data access tools do not make ADO manual coding techniques obsolete. You can still use ADO code to build and manipulate data access connections to familiar data sources from the other Office applications. (Later in this chapter, you'll see a sample that does this with Excel).

You use the Data Report Designer in VBA applications along with the Data Environment Designer to build custom reports without any code. You can export these reports as Hypertext Markup Language (HTML) documents. While you can build reports in a graphical fashion, you get greater control with code by formatting, printing, previewing, and saving your hierarchical reports.

Deployment and Management

The Package And Deployment wizard in Office 2000 is a significant improvement over earlier setup and distribution wizards. It is completely menu driven. You can use it to produce a professional setup package that works like the one for Office 2000. One especially exciting feature is the ability to deploy custom solutions via the Internet so that remote clients can install your custom solutions without needing disks or CDs. You can even automate the packaging and deployment processes using custom scripts that eliminate the possibility of wrong selections in the wizard screens.

You can use the wizard to circulate solutions based on the redistributable MSDE or the Access Run Time. However, you should not overlook delivering solutions that use the full working version of Access 2000. One reason clients are motivated to use many custom solutions is that they are familiar with the Access implementation environment. Some clients want to participate in the maintenance of their systems to lower the total cost of ownership.

Microsoft Replication Manager, a graphical user interface that ships with ODE, lets you view and manage replicated Microsoft Jet databases over a network or the Internet. Replication Manager converts a database to a design master, creates additional replicas, and manages a replica set. You can use it to graphically manage the exchange of data and data structures across the members of a replica set. The Replication Manager permits three styles of synchronization: on demand, at the next connection, and on a regular schedule. In addition, it supports data exchange between Jet and SQL Server databases. (See Chapter 11 for more on database replication and for samples that illustrate how to manage replication.)

Any professional custom solution can benefit from some kind of help system or documentation. Users need a document that explains how to respond to system screens, and developers need flow charts and excerpts that illustrate how the code and database objects interact to support system performance. ODE ships with the HTML Help Workshop, which supports both of these functions. All of the screens shown in this chapter, as well as selected others throughout the book, were captured with the HTML Help Image Editor. This tool lets you display the cursor—a feature that many screen capture packages lack. It also supports capturing a whole screen as well as various windows within it based on a timer, mouse click, or keyboard event.