Data Centers

ADO delivers high-performance data access

Microsoft would like you to use ADO to access your data, but is it the right solution for you? Follow along with our step-by-step example to learn more about this technology and see what advantages there are to using it.

ActiveX Data Objects (ADO) is Microsoft's premier data access technology. ADO and its partner, OLE DB, represent Microsoft's recommended solution for all data access. ADO is designed to be an easy-to-use application-level interface to any OLE DB data provider, including relational and nonrelational databases, e-mail and file systems, text and graphics, custom business objects, and existing ODBC data sources. Virtually all of the data available throughout the enterprise is accessible using the ADO technology, which makes ADO a preferred choice for a large number of applications.

ADO also offers advanced Recordset cache management with Remote Data Services (RDS). RDS provides optional data caching on the client workstation, reducing the number of requests for data from the client-side application to the server, thereby improving performance. With ADO, you can open and populate a disconnected Recordset object asynchronously, which improves performance by leaving the client free to execute other tasks while records are still being returned.

ADO object model
To understand ADO better, you should first look at the ADO objects (Table A) and object model (Figure A).

Table A
Names and descriptions of ADO objects

Figure A
ADO object model (Source: Microsoft)

To connect to a database, you establish a physical connection to the data source represented by the Connection object. ADO accesses the data through OLE DB. The Connection object is used to specify a provider to be used.

A Command object is used to specify the actual command that is to be executed on a data source. The command is usually used to execute a stored procedure or the actual SQL statement to add, delete, modify, or retrieve data.

Whenever the command that is to be run requires a parameter, a Parameter object is used.

ARecordset object in ADO (and DAO) is similar to a Resultset object in RDO. It represents the data returned by the command that was run against the data source.

A Field represents a column in the Recordset. If you want to modify the data, you modify the value of a Field object, which results in data being updated in the data source itself.

Since there are always several errors that can arise from database operations, an Error object is useful to find out what kind of error occurred.

ADO object properties may be built-in (they're part of the object model and available at any time) or dynamic (they exist depending on the provider used and only while the provider is used).

ADO has four types of collections:
  1. 1.      The Connection object has the Errors collection.
  2. 2.      The Command object has the Parameters collection.
  3. 3.      The Recordset has the Fields collection.
  4. 4.      Connection, Command, Recordset, and Field objects all have a Properties collection.

Microsoft introduced events into the ADO programming model starting with version 2.0. There are two types of events: ConnectionEvents and RecordsetEvents. The ConnectionEvents are issued when Connections are started, when transactions on a Connection object begin, when transactions are committed, when Commands are executed, and when the Connections end. The RecordsetEvents are issued when you navigate through the Recordset object, when a field in a Recordset is modified, when a row is changed in a Recordset, or when the entire recordset is modified.

The events are implemented through event handlers that represent the events.

The newest version of ADO, ADO.NET, is available with Visual Studio.NET. ADO.NET offers a number of advanced and improved features. It uses XML as the format for transmitting data and automatically converts its data into and from XML. Being based on XML, ADO.NET also introduces a much-improved scalability since it offers disconnected data access and interoperability.

Advantages and disadvantages of using ADO
One key advantage of ADO is its universality. ADO can be used with both relational and nonrelational databases, as well as file systems, text, and other sources. It's easy to use and language-independent. It uses minimal network traffic and has few layers between the client application and the data source. All of these features provide lightweight and high-performance data access.

ADO allows doing all the things you can do with RDO and many that you can do with DAO. So basically, if you are working with ADO, you may never need to know how RDO and DAO work. ADO is also the most recent addition to the data access options offered by Microsoft. Its object model is more compact than those of DAO and RDO.

When ADO first came out, many developers were complaining about bugs and speed. However, ADO is improving, and with ADO.NET, developers will get even more flexibility, scalability, and options.

If you're considering migration to ADO, you have to decide if its characteristics and benefits justify the cost of converting existing software. Older code written in RDO and DAO will not automatically convert to ADO code, so you'll have to make a number of manual changes. But upgrading to ADO is something you should definitely consider.

Comparing dataaccess options
If you have a large project base using DAO and RDO, you might think you don't need to learn ADO now. But to keep up with the technology, you should definitely look at how ADO works.
Developers have used DAO for various projects, but it’s best suited for smaller projects, where speed is not the main concern. RDO was specifically designed to work with large remote databases, and it's popular for older projects that access remote databases, as well.
ADO represents Microsoft’s aspiration to create a universal data access object model. It's easy to use because you don’t have so many objects to work with, and it’s being updated and improved all the time.

Jumping in
Let’s create a simple VB project to access the data stored in a remote database.
  1. 1.      Fire up VB and start a new project.
  2. 2.      Go to Project References and select Microsoft ActiveX Data Objects 2.0 Library (Figure B).

Figure B
ActiveX Data Objects 2.0 selected in Project References

  1. 3.      Add a combo box to your form and call it cboLastName.
  2. 4.      Your screen should look like Figure C.

Figure C
Here’s the combo box

  1. 5.      Add the code in Listing A to the Form Load event.
  2. 6.      Modify the connection string in:
sConnect = "provider=SQLOLEDB; Data Source=sqlserver; Initial Catalog=pubs; " _
        & "User Id=; Password=;"

to reflect the server name, database name, user ID, and password.
  1. 7.      Modify the SQL query in:
sSQL = "SELECT au_lname FROM authors"

to reflect the actual query you want to execute.
  1. 8.      Press [Ctrl][F5] to run the project. You should see a screen similar to the one in Figure D.

Figure D
Program output

In this example, we used ADO to get data from a remote database and display the list of last names from table AUTHORS. We had to use just three objects: Connection (to establish physical connection to a remote data source), Command (to specify the actual command we wanted to execute), and Recordset (to store the data returned by our query).

As you can see, ADO is very easy to use. Now that you've gotten your feet wet, go ahead and try using ADO for your next project.

Editor's Picks