		microsoft.com Home

http://www.microsoft.com/office/ork

Server Tools and Technologies

Office 2000 and Database Servers

Microsoft Office 2000 support for access to data on enterprise database servers falls into three categories:

Support for querying, importing, and data binding in native Microsoft Access databases and Microsoft Excel spreadsheets.

Support for data binding from HTML documents that use Microsoft Office Web Components.

Data access by using Visual Basic® for Applications (VBA) and database connectivity standards.

The following sections provide an overview of the database server support in Access 2000, Excel 2000, and Office Web Components for data stored by using Microsoft SQL Server™ and other database servers.

Microsoft SQL Server

Microsoft SQL Server version 7.0 brings business advantage and improved decision making to all levels of an organization through scalable business solutions, powerful data warehousing, and integration with Microsoft Office 2000.

SQL Server 7.0 offers broad availability of tailored solutions for business operations, electronic commerce, and mobile computing. SQL Server 7.0 is scalable from a laptop running Microsoft Windows 95 or Windows 98 to multiprocessor clusters running Windows NT® Server Enterprise Edition. This flexibility is achieved by using a single code base that provides full application compatibility across all editions of SQL Server 7.0. SQL Server 7.0 also provides greatly simplified administration through automatic tuning, dynamic memory management, and wizards for common tasks.

The comprehensive platform provided by SQL Server 7.0 makes it easy for you to design, build, manage, and use data warehousing solutions, allowing your organization to make effective business decisions based on timely and accurate information. Easy, seamless access to data allows desktop multidimensional analysis, and increases overall productivity by using your organization’s skills and investment in Office 2000.

Microsoft Access features that work with SQL Server

Access 2000 provides a new file type called an Access project (ADP file) that lets you connect directly to database tables and other database objects stored in one of three back-end databases:

Microsoft Data Engine (MSDE), which is available in Office

SQL Server 7.0

SQL Server 6.5

An ADP file can contain the same application objects available in an Access database file (MDB file): forms, reports, data access pages, macros, and modules. These objects are created and modified using tools familiar to users of earlier versions of Access. An ADP file contains no tables or queries, but instead is connected directly to the back-end MSDE or SQL Server database that contains tables, stored procedures, views and database diagrams (multiple relationships windows).

Access 2000 provides new design tools that allow users to directly create and edit the tables, views, stored procedures and database diagrams stored in the back-end MSDE or SQL Server database. These tools make it easier for Access power users and developers to extend their database knowledge to the client/server environment. Access 2000 also allows users to perform and manage common administration tasks in Microsoft SQL Server, such as replication, backup and restore, and security.

Access 2000 also supports embedding Excel PivotTable reports in Access forms. PivotTable reports support connecting to Microsoft SQL Server OLAP Services data in Excel 2000. Additionally, Access 2000 data access pages can use Office Web Components to display and edit SQL Server data from an HTML document.

MDB files also support importing data and creating linked tables from ODBC data sources by using the Import and Link Tables commands (File menu, Get External Data submenu). Office 2000 installs ODBC drivers for SQL Server, Oracle, and other ODBC data sources.

Microsoft Excel features that work with SQL Server

Online Analytical Processing (OLAP) is an increasingly popular technology that can dramatically improve business analysis, but that has been characterized in the past by expensive tools, difficult implementation, and inflexible deployment. The OLAP features in Excel 2000 make multidimensional analysis accessible to a broader audience at a significantly lower cost of ownership.

Excel 2000 provides support for Microsoft SQL Server OLAP Services, a new feature of SQL Server 7.0 that allows users to perform sophisticated analysis on large volumes of data with exceptional performance. SQL Server OLAP Services provides server-side processing for multidimensional data sources. Excel 2000 users can gain access to these data sources through the OLE DB for OLAP interface.

For example, users can create dynamic PivotTable® reports and PivotChart™ reports from SQL Server data by using the SQL Server OLAP Services feature. This functionality provides a new method for high-performance data analysis of large amounts of data from within the familiar interface of Excel. The benefits of analyzing large data stores and data warehouses are available to a much broader audience.

Excel 2000 also supports displaying and retrieving data with installed ODBC drivers by using the New Database Query command (Data menu, Get External Data submenu). Office 2000 installs ODBC drivers for SQL Server, Oracle, and other ODBC data sources.

Office Web Components that work with SQL Server

Office Web Components are a set of ActiveX® controls that can be used to provide access to SQL Server data from HTML documents created by using Access 2000 and Excel 2000. The Office Web Components consist of the following ActiveX controls:

Microsoft PivotTable 9.0

Microsoft Chart 9.0

Microsoft Data Access Control 9.0

In Excel 2000, users can create HTML documents that use the Microsoft Office PivotTable and Microsoft Chart controls to display PivotTables and charts that use data from SQL Server 6.5 and 7.0 databases. The Microsoft Office PivotTable and Chart controls can also display OLAP data made available through the SQL Server OLAP Services feature of SQL Server 7.0.

In Access 2000, users can create HTML documents called data access pages that utilize the Office Web Components to display and edit data stored in SQL Server 6.5 or later. Data access pages can also use the Microsoft Office PivotTable and Microsoft Office Chart controls to display OLAP data from SQL Server 7.0.

Top

Other database servers

Access 2000 and Excel 2000 support retrieving data from other database servers by using ODBC drivers. Access database files (MDB files) support importing data and creating linked tables from ODBC data sources by using the Import and Link Tables commands (File menu, Get External Data submenu). Excel 2000 also supports retrieving data with installed ODBC drivers by using the New Database Query command (Data menu, Get External Data submenu). Office 2000 installs ODBC drivers for SQL Server, Oracle, and other ODBC data sources.

All Office 2000 applications can access server data from Visual Basic for Applications (VBA) code by using ODBC drivers and OLE DB data providers. The Data Access Objects (DAO) programming model can be used to access ODBC data sources. The ActiveX Data Objects (ADO) programming model can be used to access ODBC and OLE DB data sources.