Integrating Office 2000 and BackOffice

Use Office 2000's new features to capitalize on BackOffice products' functionality

Most businesses use Microsoft Office, in part because Office closely integrates with the various Microsoft BackOffice products. Office 2000 adds several features to each of the main products—including Access, data access pages, offline cube files, Word, and Outlook—to continue to take advantage of BackOffice products' functionality. The Office 2000 enhancements primarily involve Web integration, but Microsoft also added numerous BackOffice-related enhancements, including several that the company oriented toward SQL Server 7.0 and its new OLAP Services. (For information about Office 2000 Web-oriented enhancements, see Paula Sharick, "Use Office 2000 to Grow Your Own Web Site," page 69.) After you learn about the most important Office 2000 features and how they integrate with BackOffice, you'll see that Microsoft intends to stay in first place with Office 2000.

Access 2000
Without question, BackOffice integration enhancements benefit Access 2000 the most of all the Office 2000 products. Access 2000 includes support for the new Access database projects, which use OLE DB to connect Access to SQL Server 7.0, SQL Server 6.5, or Microsoft Database Engine (MSDE). Unlike standard Access databases ending in the extension .mdb, Access database projects end in the extension .adp. Access database projects connect to a SQL Server database and therefore don't contain any local data. With Access database projects, you can use Access 2000 as both a management and development front-end-to-back-end database such as SQL Server.

To create a new Access database project, select File, New from the Access menu and select Project (Existing Database) to open an existing SQL Server database, or Project (New Database) to create a new database. Access will display the Data Link Properties dialog box, in which you specify the target database you want to connect to. The previous version of Access relied on ODBC to connect to the database. However, with the new Access database project, Access 2000 uses the OLE DB-based Data Link dialog box, which Screen 1 shows, to connect to external data sources.

The Data Link dialog box lets you easily connect the various Office suite members to OLE DB data sources. Previous ODBC connections required you to create a data source, but the Data Link dialog box lets you simply specify three basic OLE DB connection attributes: the name of the database server, the user ID you'll use to connect to the server, and the name of the database you want as the target data source. Screen 1 shows that the user specified TECAALPHA as the name of the server, chose NT Integrated to log on to the server, and specified the NW2 (a copy of Northwind) database as the data source.

After you provide the appropriate server, logon, and database information, you establish the connection to SQL Server and create a new Access database project. You can use the Access database project to work with SQL Server database objects similarly to how you work with local Access database objects. The built-in Access designers let you design new tables and create queries and reports.

Because the new Access database project is connected to SQL Server, options for SQL Server database objects replace the standard Access object types that Access' Objects pane lists. Screen 2 shows a new Access database project connected to the sample NW2 database that SQL Server 7.0 supplies.

The Access database project includes icons for Views, Database Diagrams, and Stored Procedures in addition to the standard Access objects. By selecting these buttons, you get lists of the corresponding objects on the connected SQL Server system. In the example in Screen 2, you can view the stored procedures that SQL Server's NW2 database contains.

Data Access Pages
You can use Access 2000's database projects with another new Office 2000 Access feature—data access pages. Data access pages let you create data-bound HTML pages that a standard Web browser can display. You save data access pages (unlike standard Access database objects) separately from the main Access database project in HTML files. You can then incorporate these files into your Web projects to provide updates and read access to the target database.

To create data access pages, you use the data access page wizard or the data access page designer. Select Pages on the Access database objects to create a new data access page. Then, select Create data access page in Design View or Create data access page by Using Wizard. Screen 3 shows the Data Access Page design window.

You don't need to manually perform any HTML or Visual Basic for Applications (VBA) coding to create data access pages. Instead, you can simply drag controls from the Access toolbox onto the page and set their properties, similarly to how you design a standard Access form. The data control properties at the bottom of Screen 3 in the NavigationSection of the Data Access Page design window describe the data source that the data access page is connected to. The Web client can scroll through the database at runtime by moving forward and backward through the rows in the target table. Each of the text box fields that Screen 3 shows is bound to a column in the target table.

In addition to the OLE DB connectivity the new Access projects use, Access 2000 still provides linked tables from a standard Access Microsoft database to support connectivity with SQL Server and other ODBC-compliant databases. As is the procedure in earlier releases, you select File, Get External Data, Link Table to create linked tables. Access 2000 contains ODBC drivers for SQL Server and Oracle.

Another BackOffice integration tool that Access 2000 includes is the Upsizing Wizard, which was formerly available as an add-on that you could download from Microsoft's Web site. You use the Upsizing Wizard to migrate standard Access .mdb databases to SQL Server databases.

Excel 2000
Other than Access, Excel 2000 benefits the most from BackOffice integration enhancements. Excel continues to use ODBC to connect to external relational data, unlike Access 2000, which uses OLE DB as its primary method for external database connectivity. ODBC, in conjunction with Microsoft Query (MSQuery), provides Excel's data-selection mechanism. You use MSQuery, which is a graphical query builder, to connect to external databases and enter selected data into an Excel spreadsheet. When you select Data, External Data, the Excel menu prompts you to use a saved query definition or create a new Web or database query. When you use the new database query, you retrieve data from an external database such as SQL Server. This option lets you select an existing data source or create a new data source that identifies the target database. After you specify the data source, Excel launches the MSQuery application, which Screen 4 shows.

The example in Screen 4 uses four SQL Server database tables to construct a Customer Service query. MSQuery lets you graphically build a database query by selecting tables and columns from drop-down lists and entering selection criteria. If you select the Automatic Query option, you dynamically retrieve the data from the data source and display the data in MSQuery's data grid. You need to turn this option off when you're working with large tables because you might have to wait a long time between each query-building step. If you turn this option off, you simply execute the end query once after fully defining it and save the query definition. You select File, Return Data to Excel to transfer the query data to an Excel spreadsheet.

In addition to letting you use Excel to access external databases with MSQuery, Office 2000 lets you build PivotTables based on SQL Server 7.0's new OLAP Services. Excel uses OLAP Services to execute high-performance, multidimensional data analysis on large-volume data-warehousing types of implementations. You use Excel 2000's OLAP data-analysis capabilities in the same manner that you retrieve standard relational data. However, instead of using regular OLE DB, Excel 2000 uses the OLE DB for OLAP provider to access OLAP Services. To use Excel 2000 to create an OLAP-based PivotTable, you select Data, External Data from the menu, then select the OLAP Cube tab from the Data Sources dialog box. You can then select an existing OLAP data source to create a new OLAP data source. After you specify the data source, Excel launches the PivotTable Wizard, which guides you through building the PivotTable based on the cubes, dimensions, and measures in the OLAP database. Screen 5 illustrates building a PivotTable based on the example FoodMart data warehouse that SQL Server 7.0's OLAP Services provides.

You use the PivotTable toolbar to add dimensions and measures to the PivotTable. For OLAP cubes, an icon with a dark gray heading and border identifies the dimension fields in the toolbar. You can use these fields only in the row and column fields of the PivotTable. In Screen 5, Time, Warehouse, and Yearly are examples of different OLAP dimensions. In the PivotTable toolbar, a light gray heading and border identifies measures, which you can use only in the data fields section of the PivotTable. Screen 5 shows that the user chose Units Ordered and Units Shipped as the measures in the example.

You can use Excel 2000's PivotChart with OLAP data the same way you use PivotTable. In fact, Excel 2000's PivotTable lets you click the PivotChart icon on the toolbar to create a new PivotChart based on the PivotTable data.

Offline Cube Files
In addition to working directly with SQL Server's OLAP Services, you can copy data from an OLAP data source to use in an offline cube file. Offline cube files, which end in the extension .cub, facilitate mobile computing. An offline cube file lets you continue to work with OLAP data even when you're not connected to the network. For example, you might create an offline cube file on your laptop so that you can work without connecting to the network. To make an offline cube file, you must first create a PivotTable report based on an OLAP Services data source. After you produce the OLAP report, you can run the Offline Cube Wizard to create the offline cube file. To start the Offline Cube Wizard, select the Client-Server Settings option from the PivotTable toolbox. After the Client-Server Settings dialog box opens, click Create Local Data File to start the Offline Cube Wizard and begin building the offline cube file. After you create the offline cube file, enable the local data file option in the Client-Server Settings dialog box. If you select the server-based data option, you revert to using the direct OLAP link.

As is the case with Access' data access pages, you can save the spreadsheets containing Excel PivotTables as Web pages, which lets you use a standard Web browser to display the OLAP PivotTable. Excel 2000 also provides PivotTable and PivotChart ActiveX controls to create Web applications that dynamically access SQL Server and OLAP Services data sources. Unlike the simple read-only Web pages that the Save As Web Page option creates, the more powerful ActiveX controls let the Web client use the Web browsers to interactively access the data source and manipulate the PivotTable.

Word 2000
Like Excel, Word 2000 uses a combination of ODBC and MSQuery to access SQL Server and other ODBC-compliant databases. The most common application for this feature is printing envelopes and performing mail-merge operations based on the database and external data source. Word 2000's Mail Merge feature on the Tools menu provides a wizard interface to guide you through the process of setting a mail-merge document. In step 2 of the mail-merge wizard, you specify the data to use in the mail-merge operation. When you select Get, you access a pop-up menu that lets you create a new data source or use an existing data source. After you select an option, start MSQuery and interactively create a query. As in Excel, selecting File, Return Data to Microsoft Word closes MSQuery and provides the data to Word.

In Word 2000, you use the Database toolbar to create custom data entry forms or perform mail-merge operations. Select View, Toolbars, Database from the standard Word 2000 menu to display the Database toolbar. From the toolbar, you select the Insert Database icon to connect your document to an external data source. To connect to external ODBC data sources such as SQL Server, you need to use MSQuery, as you did for mail-merge operations.

You can also use Word 2000 as an Outlook email client. For example, you use Word's standard text-editing window to compose an email message. When you're ready to send the message, select File, Send To to send the document through Outlook 2000 as an email message. Outlook's Sent Items folder then archives the outgoing message.

Outlook 2000
Microsoft has integrated Outlook and Microsoft Exchange Server since their initial introductions, so it isn't surprising that Outlook 2000 tightly integrates with Exchange Server 5.5 and the upcoming Exchange 2000 Server (formerly code-named Platinum). As do previous versions of Outlook, Outlook 2000 uses multiple protocols to function as an email client and provide support for messaging, message recall, voting, and group scheduling. Because Outlook 2000 supports offline folders and the Out of Office Assistant, you can also manage email responses when you're traveling.

Office 2000's enhancements let you use Outlook 2000 to email documents that you create in any Office 2000 program, including Word, Excel, Access, and PowerPoint. For example, you select File, Send To to email a PowerPoint presentation directly from PowerPoint. When you select the Send To option, Outlook prompts you to enter the intended recipient. Similarly, to email an Access report, you retrieve the report in the Access report designer and select the Send To option. As it does with standard email messages, Outlook's Sent Items folder archives the outgoing message.

Office 2000: The Pinnacle for Office and BackOffice Integration
Office 2000 continues to set the standard for Office and BackOffice integration with several new features that capitalize on the enhanced functionality that most current BackOffice products provide. In addition to having a simplified user interface (UI), Office 2000 products support VBA 6.0 scripting. VBA 6.0 also takes advantage of ADO and OLE DB for more extensive custom database and directory integration. By coupling the new Office 2000 Web-based collaboration features with its tight BackOffice integration, Microsoft positioned Office 2000 as the leader among office productivity suites.