Read an Excerpt
Chapter 7: Working with DataOne of the biggest changes in Visual Basic 6 is the way in which you access data (or the way in which Microsoft wants you to access data). Microsoft is making a conscious effort to create a paradigm in which data is accessed through a common interface, regardless of the data's underlying format. In the past, developers often used a number of data access techniques, including the two common data access object models, data access objects (DAOs) and remote data objects (RDOs), as well as directly using APIs such as the ODBC API. Usually, code used to access data with one method is incompatible with code that accesses data using another method, which creates a number of drawbacks. For instance, scaling a database (such as migrating from a Jet database to a SQL database) often means rewriting the majority of data access code. In theory, if the mechanism used to communicate with different data sources remained consistent, using different back-end data sources would require little or no code modifications.
The Technologies and the ExamsAlthough ActiveX Data Objects (ADO) is covered on both exams, the Desktop exam only covers ADO at a superficial level, focusing mostly on the ADO Data control. The Distributed exam, however, takes off the kid gloves and delves into more hard-core subject matter. If you are taking only the Desktop exam, use Table 7-1 as a guideline to which material to focus on.
Universal Data AccessOLE DB is a low-level interface that introduces Microsoft's strategy of universal data access, or UDA. OLE DB is designed to be a high-performance mechanism for accessing data. Unlike previous technologies, OLE DB is not restricted to accessing Jet or ISAM databases, nor is it even restricted to accessing relational databases. Instead, OLE DB is capable of accessing data that resides in text files, mail servers, spreadsheets, and nonrelational data, in addition to the standard relational database formats. OLE DB makes this possible by way of OLE DB providers. An OLE DB provider is a component that exposes data through an OLE DB interface. The mechanics of how the data is retrieved, as well as the underlying format of the data, are of no consequence to OLE DB or to components accessing the data through OLE DB, as long as the provider makes the data available by way of the expected OLE DB interface.
Microsoft's UDA strategy creates an extended, layered approach to data access. At the lowest level, the OLE DB provider is designed to access some form of data and expose that data through a standard OLE DB interface. Using this interface, OLE DB itself is able to make requests and execute commands on the data. Just as OLE DB doesn't communicate directly with data (it communicates through one or more providers), client applications don't communicate directly with OLE DB. Instead, client applications use ADO to manipulate data. ADO provides a common application interface that simplifies the process of communicating with OLE DB. Figure 7-1 shows the layers of this architecture and how they communicate.
Visual Basic is capable of creating OLE DB providers. Creating OLE DB providers is beyond the scope of the exams and therefore of this book as well.
Manipulating Data Using ActiveX Data ObjectsADO is a set of objects that act as a client interface to OLE DB. Table 7-2 lists the two primary data access methods that ADO is designed to replace: DAO and RDO. In theory, ADO takes the best of both of these data access methods and makes them better. There are differing opinions on how successful Microsoft has been in this endeavor, but one thing is for sure: ADO will replace DAO and RDO--it's only a matter of time.
Microsoft wants everyone one to believe, unequivocally, that ADO is superior to all previous data access methods. In many ways, this is true. The following lists some of the reasons you might choose ADO over DAO or RDO:
- ADO can access many types of data in addition to relational and nonrelational databases.
- ADO has a smaller footprint than the other methods of data access.
- ADO has a simplified, or "flattened," object model.
- ADO is the standard data access object model in Visual Studio.
- ADO offers improved performance in most situations.
- Microsoft is phasing out DAO and RDO.
Although Microsoft would like you to believe that DAO and RDO are dead, in reality, there are times when a data access method other than ADO makes sense. For example, DAO is significantly faster at accessing native Jet databases, and ADO doesn't yet support the complete feature set of DAO when interfacing with Jet. DAO and RDO may be dying, but they're far from dead. Remember, however, that Microsoft is pushing ADO hard. If you encounter a question about which data access method would best fit a given situation, and ADO is one of the options, chances are that it's the "correct" choice.
In order to use ADO in a Visual Basic project, the Microsoft ActiveX Data Objects Library must be referenced, using the References dialog box accessed by choosing References from the Tools menu. Once ADO is referenced, its object model is made available to the project.
One of the benefits of ADO over DAO and RDO is that it supports a simplified (flattened) object model. This means that ADO uses fewer objects, yet more properties, methods, and events than other data access methods. If you're new to programming databases, this may make it easier to learn. However, if you're one of the many experienced developers using other data access methods such as DAO (which has a rather large object model), using ADO necessitates some rethinking. In particular, if you're a DAO developer, it may be more difficult to transition to ADO than it would be for an RDO developer, because ADO is more similar to RDO than to DAO. Figure 7-2 shows the ADO object model, while Table 7-3 explains each component of the ADO hierarchy.
Each of the ADO objects is described in the following sections.