Data Access Technologies

Office 2000 and Data Connectivity Technologies

In the past, Microsoft Office applications have supported a broad variety of data formats and data access technologies. Microsoft Office 2000 is no exception to this trend. However, all Microsoft products that support data access are converging on a new data access strategy called Universal Data Access.

The primary technologies that are used to implement Universal Data Access are the low-level data access component architecture, called OLE DB, and the higher level programming interface to OLE DB, ActiveX Data Objects (ADO). ADO can be used from any programming language that complies with the Component Object Model (COM). For Office solution development, COM compliance includes Visual Basic for Applications (VBA), Visual Basic Scripting Edition (VBScript), Microsoft JScript®, Microsoft Visual C++® and Microsoft Visual J++®.

You can install the OLE DB components and ADO 2.1 with Office 2000. ADO supports a broader array of data sources than the Data Access Objects (DAO) programming model. However, Office 2000 applications continue to provide support for DAO through the Microsoft DAO 3.6 object library, so users can continue to run existing solutions that were developed by using DAO, and developers can continue to create new solutions that use DAO as well. You can also use both ADO and DAO code in your solution if you want.

Top

OLE DB

OLE DB is an interface with an open specification designed to build on the success of Open Database Connectivity (ODBC) by providing an open standard for accessing an even broader variety of data. Whereas ODBC was created to access only relational databases, OLE DB is designed for both relational and nonrelational data sources, including mainframe and hierarchical databases; e-mail and file system stores; text, graphical, and geographical data; custom business objects; and more.

OLE DB consists of a collection of COM interfaces to various database management system services. OLE DB provides access to a particular data source by using a COM component called a data provider, which is often referred to as an OLE DB provider. If the system an Office solution is running on has the appropriate OLE DB provider installed (as well as the core ADO and OLE DB components, which you install with Office), that solution can use ADO code to work with the data exposed by that provider.

The primary providers used by Office solution developers are the Microsoft Jet 4.0 OLE DB Provider and the Microsoft OLE DB Provider for SQL Server.

Microsoft Jet 4.0 OLE DB Provider   Works with the Microsoft Jet database engine, which provides access to data in Microsoft Access databases; also provides database access to the installable Indexed Sequential Access Method (I-ISAM) data supported by Jet tabular data stored in Microsoft Excel workbooks, Microsoft Outlook or Microsoft Exchange mail stores, dBASE tables, Paradox tables, Lotus 1-2-3 spreadsheets, text, and HTML files.

Microsoft OLE DB Provider for SQL Server   Provides access to databases stored on Microsoft SQL Server versions 6.5 and 7.0.

Toolbox   For data sources that do not have OLE DB providers, you can use the Microsoft OLE DB Provider for ODBC. The Office Resource Kit includes information about the data formats and drivers that are supported by the Jet database engine, as well as other data access components that are installed by Office, in a Word document named Formats.doc. For more information about installing this document, see Office Information.

Top

ActiveX Data Objects

ADO is an easy-to-use, application-level programming interface to the new and powerful data access technology, OLE DB. The ADO programming model supports key features for building desktop, client/server, and Web-based solutions, including the following:

• Support for independently created objects. Unlike DAO or Remote Data Objects (RDO), you no longer have to navigate through a hierarchy to create objects because most ADO objects can be independently created. This allows you to create and track only the objects you need and also results in fewer ADO objects and thus a smaller memory footprint.
• Batch updating, which helps improve performance by locally caching changes to data, and then writing all the changes to the server in a single update.

  Note   Although the Microsoft Jet 4.0 OLE DB Provider supports batch updating, there is no need to use batch updating with Access databases to improve performance because the Jet database engine runs locally. However, you are probably going to have a performance gain when performing batch updates against a SQL Server database.

• Support for stored procedures with in/out parameters and return values against a Microsoft SQL Server database.
• Different cursor types, including the potential for support of cursors specific to back ends.
• Support for limits on the number of returned records and other query goals for performance tuning.

  Tip   The ADO MaxRecords property of a Recordset object, which is designed to limit the number of returned records, is not supported by the Microsoft Jet 4.0 OLE DB Provider or the Microsoft Access ODBC driver. However, if you require this functionality, you can use the TOP n predicate in a Microsoft Jet SQL statement, or you can set the TopValues property of a query that is saved in an Access database.

• Support for multiple Recordset objects returned from stored procedures or batch statements.

  Note   Multiple Recordset objects can be returned for SQL Server databases. Access databases cannot return multiple Recordset objects because Microsoft Jet SQL statements do not support multiple SELECT statements.

• Free-threaded objects for efficient Web server applications.

Top

Support for older data access technologies

Office 2000 and Access 2000 continue to support data access solutions that use the older DAO programming model and ODBC drivers.

Data Access Objects

When you install Office 2000 or Access 2000, you get DAO version 3.6. When an Access database (MDB file) is converted from an earlier version of Access to Access 2000 format, a reference is established to the DAO 3.6 Object Library, which is used to work with any DAO code in the database. DAO code written in previous versions of Access that use the DAO 3.5 programming model continue to work in a database converted to Access 2000 format. However, DAO code written by using DAO 3.0 or earlier might need to be recompiled or rewritten to work in Access 2000.

Apart from conversion issues, there are a number of reasons to continue to use DAO when writing Visual Basic for Applications code to work with new Access databases. Although ADO provides access to a broader variety of data sources than DAO, and even exposes some features of the Microsoft Jet 4.0 database engine that are not available from DAO, there are a number of limitations to using ADO against Access databases:

• In the Access object model, the new Recordset property of a Form object can be used to request or specify a Recordset object for the data being browsed in a form. If you request the Recordset object for the current form in an Access database, Access always returns a DAO Recordset object. Therefore, you must continue to use DAO code to work with the Recordset object that is returned.
• When you use the Recordset property to set the Recordset object of a Form object to a Recordset object you created, if you set the Form object to an ADO Recordset object, the data is read-only. If you want the data to be writable, you must set the Form object to a DAO Recordset object.
• To read and set database properties in an Access database, and to read and set certain table properties, such as the Description and Filter properties, you must continue to use DAO code.
• It is not possible to exchange information between ADO and DAO code. For example, if a DAO procedure returns a Recordset object, there is no way to translate or pass that DAO Recordset object to ADO code, and vice versa — an ADO Recordset object cannot be read by or translated to DAO. However, this does not mean that ADO cannot work with saved database objects, such as tables and queries, that were created with DAO, and vice versa. But it does mean that although ADO and DAO can coexist in the same project, you cannot use ADO code to work with objects returned by preexisting DAO code. You must continue to use DAO code, or you must rewrite those procedures by using ADO code.

If you are updating an existing DAO data access component, or developing new data access components that work only with Access databases or other data sources supported by the I-ISAM drivers of the Jet database engine, you can continue to use DAO by establishing a reference to the Microsoft DAO 3.6 object library. All DAO code written for DAO 3.5 (with the exception of code that defines user-level security for code modules in Access 2000 databases) will continue to work with DAO 3.6.

Only the following Jet database engine features require ADO:

New Jet SQL commands and syntax   Additional SQL commands and syntax were added to make Jet SQL conform more closely to the ANSI SQL-92 specification.

Connection control   A setting that allows you to exclude all new connections and exclude current users after they close the database.

User list   Programmatically displaying a list of information about all the users who are currently logged on to the database.

Programmatic control over page-level or record-level locking   You can use ADO to control whether the Jet database engine uses page-level or record-level locking when records are being added, deleted, or modified from Visual Basic for Applications code.

If you do not require access to these Jet database engine features and do not require other ADO-specific features, you can continue to write code that uses DAO until you encounter these requirements.

Open Database Connectivity

Versions of Office earlier than Office 2000 included features that use ODBC drivers and components to connect to data, such as viewing and importing data from ODBC data sources in Access and Excel. Office 2000 continues to support features that use ODBC data sources. Office 2000 installs updated versions of ODBC drivers for Access, Excel, Microsoft Visual FoxPro®, Microsoft FoxPro®, dBASE, Paradox, and text files; and also installs ODBC drivers for Microsoft SQL Server and Oracle databases.

In most cases, DAO code written to use ODBC data sources in versions of Office earlier than Office 2000 do continue to work. Code that uses 16-bit versions of ODBC drivers do not work until you recreate data source names by using the ODBC Data Sources (32bit) icon in Control Panel. ODBC data sources can also be used from code written by using the new ADO programming model.

Top

Sharing databases by using Web technologies

Office 2000 provides features to share databases from Web browsers by using Microsoft Office Web Components, which can be used in HTML documents displayed in Microsoft Internet Explorer 5 or later. Office 2000 also continues to support other technologies for sharing databases, such as Active Server Pages (ASP) pages, which can be used from Internet Explorer as well as from other Web browsers, such as Netscape Navigator.

Using Office Web Components

Office 2000 installs Microsoft Office Web Components to enable publishing Office documents to the Web while preserving the interactivity that the documents have when they are viewed in their native applications. The Office Web Components are a collection of ActiveX controls designed to let you publish fully interactive worksheets, charts, PivotTable reports, and databases to the Web.

When users view a Web page that contains an Office Web Component, they can interact with the data displayed while in Internet Explorer. Users can sort, filter, add, or change data, expand and collapse detail views, work with PivotTable lists, and chart the results of their changes. In addition, the Office Web Components are fully programmable by using Visual Basic for Applications within an Office application or by using Visual Basic Scripting Edition or Microsoft JScript code in a Web page.

Office Web Components include the Microsoft Office Data Source Control, which manages communication between other controls on the page and the source of data for the page. The Microsoft Office Data Source Control can be used to work with either Access or Microsoft SQL Server databases. The Microsoft Office Data Source Control uses OLE DB and ADO components to work with the source of data for the page. HTML documents authored by using the Microsoft Office Data Source Control in conjunction with other Office Web Components are created in Access and are called data access pages.

Important   Office Web Components work only in Internet Explorer 4.01 or later. Office Web Components on Access data access pages work only in Internet Explorer 5 or later. You get the most complete functionality with all of the Office Web Component controls in Internet Explorer 5 or later. To view and work with any of the Office Web Components, either users must have Office 2000 installed or you can set up Office Web Components so that they can be installed from your corporate intranet (a site license is required).

Using other Web data access technologies

Access 2000 continues to support the following features for sharing databases from Web pages that can be viewed in Internet Explorer, as well as in other Web browsers:

• Saving data from tables, queries, reports, and form datasheets as static HTML documents.
• Creating Internet Database Connector/HTML extension (IDC/HTX) files to query data from a table, query, or form datasheet in a database on a Web server and display it in a Web page.
• Saving an Access form as an ASP page that allows users to view, enter, and update information in a database on a Web server.
• Creating an ASP page to query data from a table, query, or form datasheet in a database on a Web server and display it in a Web page.
• Saving Access reports to a special graphics format called a snapshot file, and publishing them from Web pages.

Top

See also

When converting databases created in earlier versions of Access, in some cases DAO code must be recompiled or altered before conversion is successful. For information about troubleshooting code problems in converted databases, see Upgrading to Access 2000.

For more information about the Microsoft Universal Data Access strategy, OLE DB components, and the ADO programming model, see the Microsoft Universal Data Access Web site at http://www.microsoft.com/data/.

For more information about working with Microsoft Office Web components, see Adding Interactive Web Controls to Office Documents and Managing Office Web Components.