Microsoft's Data-Access Directions

A practical look at Microsoft's data-access strategy.

Rick Dobson

Data is good. Getting at it can be bad, however, particularly when you need to get at data in different formats from one application. Microsoft's ODBC has solved many data-access problems, and today Microsoft offers an array of database products and technologies that respond to the needs of different data-access scenarios.

But in any given situation, it's not always clear which is the right product or technology for the job. To further complicate matters, Microsoft is working toward a radically new data-access techno logy, called Universal Data Access (UDA), that will eventually make the company's traditional approaches obsolete. What follows is a look at Microsoft's current and future data-access technologies. This will help you to select the right ones for your particular data-access requirements.

The DAO of Pooh

Currently, Microsoft offers at least seven data-access technologies that you can choose from: Open Database Connectivity (ODBC), Data Access Objects (DAO), Remote Data Objects (RDO), ODBCDirect, ActiveX Data Objects (ADO), OLE DB, and the upcoming UDA. (For a brief overview of these technologies, see the sidebar "The Seven Data Samurai".) DAO and RDO are the two main Microsoft data-access technologies today. They are stable, and many developers have experience applying them.

When you're working with indexed sequential-access method (ISAM) files, Jet files, or ODBC data sources, DAO is the right choice (and Microsoft recommends you access DAO through ADO). I ts hierarchical object model (see the figure "Comparing Data Access" ) offers complete control over the Microsoft Jet database engine.

DAO actually performs two different kinds of functions. First, it permits the management of databases and their relationships to users. Second, it allows access to and maintenance of data. For ease of use, DAO collections and objects include properties and methods that mimic the Access user interface. However, DAO's programmatic interface to Jet also permits functions that aren't possible with the GUI; for instance, the Databases collection accommodates the concurrent availability of two or more separate databases.

Introduced with Office 97, ODBCDirect is an RDO wrapper inside DAO. It delivers data access without the kind of data management that DAO delivers, so an ODBCDirect workspace contains fewer collections and objects; for instance, it has no collections or objects for security.

As its name implies, RDO explicitly targets remote ODB C data sources. RDO is not a simple extension of the traditional DAO object model, and therefore it takes more time to learn than ODBCDirect does. RDO removes the need to learn ODBC API calls while still delivering comparable performance.

The figure shows the RDO object model. RDO performs the basic functions of submitting queries, creating cursors that are based on the result set, and processing the return set with database-independent code. The technology can also interact effectively with stored procedures.

The current version of RDO, RDO2 (found in Visual Basic [VB] Enterprise Edition), offers event notification for common functions. ODBCDirect, on the other hand, does not provide this capability. The availability of events makes it possible to make better use of RDO's asynchronous connection capability.

Directions

As mentioned earlier, Microsoft calls its emerging data-access strategy Universal Data Access, or UDA. The UDA technology encompasses some of Mi crosoft's traditional data-access technologies, but it features some significant additions. Its scope includes relational data sources (through ODBC) as well as nonrelational data stores, such as media archives, hierarchical DBMSes, mail servers, file servers, and spreadsheets. UDA distinguishes itself by not requiring data to enter a common store -- it makes data available from wherever it is already.

UDA builds on three component technologies that are items in the Microsoft Data Access Components (MDAC) version 1.5. These components are ADO, including RDS; OLE DB; and ODBC. They are available for downloading at http://www.microsoft.com/data/mdac15.htm . What follows is a closer look at the two key parts of UDA: ADO and OLE DB.

ADO will eventually replace all current data-access techno logies for typical users, such as those currently using DAO, ODBCDirect, and RDO. ADO's object model ( see the figure ) is substantially sparser than that of DAO, ODBCDirect, or RDO. All three of the earlier models are strictly hierarchical, while the ADO model includes nonhierarchical object relations.

There are only three elements necessary for a typical ADO application: the Connection object, the Recordset object, and the Field object. The Connection object interfaces with the data source. If an Execute command on the Connection object returns rows, ADO creates and returns a default Recordset object. The Field object supports the return of values from a recordset and their modification. The Field object also provides column metadata about a recordset.

The RDS element within ADO currently works with Microsoft Visual InterDev and Microsoft Internet Explorer (MSIE) 4.0's Dynamic HTML (DHTML). At the time of this writing, RDS serves a unique role as a data source object (DSO). I n addition to storing server-based data in a local cache, it can also update the server from edited data in a local cache.

OLE DB targets data-access providers, such as those constructing ODBC drivers today. OLE DB extends and embraces ODBC. Just like ODBC, OLE DB is a low-level development environment that provides data for a higher-level development environment -- in this case, ADO. OLE DB can work with existing ODBC data sources without adding a new layer. What makes OLE DB special is that it can also work with non-ODBC data sources, such as e-mail and file-system stores, ISAM and hierarchical databases, and eventually even spreadsheets.

Microsoft asserts in numerous documents that OLE DB will become the fundamental COM element for data access. OLE DB encapsulates core database-management services. A data-provider component contains and exposes data. The data-consumer component uses data. Various service components process and transport data. Microsoft explicitly mentions query processors and c ursor engines as sample service components. This development promises more powerful data-access capabilities to non-SQL data sources that currently do not natively support these features.

For today, you should use DAO to control Jet and other ISAM sources. Use ODBCDirect for remote ODBC sources. Use RDO to access the events model that the ODBCDirect wrapper doesn't expose. And look for ways to apply the up-and-coming ADO technology.

Promises, Promises

UDA promises much in the future (i.e., two to four years from now). Right now, though, most of what we have is just a promise. For example, the support to non-ODBC data sources is still incomplete; an OLE DB provider for Excel is still to come. And Microsoft does not currently support UDA as much as it does traditional data-access technologies: The capability to surface RDO events is missing from ADO 1.5, although Microsoft promises it for version 2.0.

It's important to realize that there is a huge amount of database development in Acc ess and the rest of the Office family using DAO; there are many fewer VB/RDO developers than there are Access developers. Furthermore, much VB database development relies on DAO technology. VB/RDO developers are more prevalent than SQL Server, C++, and ODBC API developers.

Microsoft needs to target UDA initially to this bulk of database developers. These developers are familiar with some nontraditional data stores, such as spreadsheets, and they have simpler needs that will not stress the emerging technology as severely as the enterprise applications of SQL Server developers. Introducing the emerging technology with low-end developers will enable both to mature as the technology grows up.

---

Where to Find

Microsoft Corp.
Redmond, WA
Phone:    800-426-9400
Phone:    206-882-8080
Internet: http://www.microsoft.com/

---

Product Placement

Product	Department solution	Enterprise solution	Web-server-based solution	Web-client-based solution	Comments
Access	****		**		Targets departmental solutions with a combination of ease of use and power; flexible static/dynamic Web publishing wizard for data sheets, reports, and forms.
Of fice (excluding Access)	***				Can bring a local ISAM database and a remote ODBC database into familiar Office applications.
Visual Basic	**	****			Can build Office solutions, but its relative strength is back-end solutions.
Visual InterDev	**	***	****	**	FrontPage on steroids; offers excellent database connectivity over the Web.
MSIE 4.0 (data binding)	*	**		****	Allows the creation of a local data cache on a browser workstation; solutions require VBScript or JScript code.
SQL Server		****	*		Multithreaded, striped data access; client/server design facilitates large communities of users over WANs; SQL Server Web Wizard publishes static pages only (but developers can s chedule regular updates).
Key: **** Excellent, *** Good, ** Fair, * Nominal

---

Comparing Data Access

illustration_link (12 Kbytes)

When you put together all of Microsoft's data-access technologies, it looks like this.

---

Rick Dobson (Louisville, KY) is president of CAB, Inc., a database and Internet development consultancy. You can reach him at his firm's Web site, http://www.cabinc.win.net.