SQL Seven: Office 2000/SQL Server Integration

Microsoft Office has always had strong BackOffice connectivity. Office 2000 continues this trend with strong SQL Server connectivity. Here are the seven coolest SQL Server integration features in Office 2000.

7. MSDE

Microsoft included the Microsoft Database Engine (MSDE), the desktop version of the SQL Server database engine, in the latest version of Office 2000. Applications that you develop for MSDE are 100 percent compatible with SQL Server 7.0, and you can readily scale them upward to SQL Server. MSDE databases can also participate in SQL Server database replication.

6. Access Upgrade Wizard

You can use the Access Upgrade Wizard to upsize Access (.mdb) databases to SQL Server databases. The SQL Server 6.5 version of this tool is available as a separate download from Microsoft's Web site. Microsoft bundled the new Access 2000 version, which provides full support for SQL Server 7.0, with Office 2000.

5. Database Toolbar

Word's Database Toolbar lets you create custom data-entry forms for integration into standard Word 2000 documents. Using the Insert Database option from Word's Database Toolbar lets you create an ODBC-based connection to SQL Server via Microsoft Query.

4. Data Access Pages

With Access's Data Access Pages, you can quickly and easily create dynamic, data-driven Web pages that can connect to either Access or SQL Server databases. You build Data Access Pages graphically, but you save them as data-bound HTML pages that a standard Web browser can display. Data Access Pages provide the ability to both display and update the target data source.

3. PivotTable Wizard

Excel's PivotTable Wizard lets you use Excel as an OLAP data-analysis front end. It uses Microsoft's OLE DB for OLAP provider to connect to SQL Server's OLAP Services. The PivotTable Wizard guides you as you select the dimensions you'll use to analyze the data, then the measures you'll use as data. As you select dimensions and measures, the Wizard dynamically builds the PivotTable on the worksheet. PivotTable Services can talk to any ODBC source, not just Microsoft OLAP providers.

2. Offline Cube Files

Excel's Offline Cube Files facilitate mobile computing by copying data from SQL Server's OLAP Services into an offline cube file (.cub). You can use the .cub file in your laptop while you're traveling or disconnected from the network. Excel's PivotTable can work directly with the OLAP data in the .cub file as if it were directly connected to SQL Server's OLAP Services.

1. Access Projects

The Access project (.adp) is the coolest new SQL Server integration feature in Office 2000. (For information on using Access projects, see Rick Dobson, "Defining Databases and Tables with Access Projects," page 45.) Access projects let you use Access 2000 as a development and management front end to SQL Server. Unlike the old linked tables that previous versions of Access supported, .adp projects use OLE DB to establish a direct link to SQL Server's database objects.