ADO versus RDO

i've already tried to search the above topic in MSDN but still have no idea on choosing a suitable data model for our project (client/server, not web project).

Our project:
Frontend - VB 6 on Win32 environment.
Backend - IBM DB2 on AIX
Adopt COM (Active-X DLL) to write some classes to implement common logic

i need some references which cover:
- Differences between ADO and RDO
- Clear and strong reasons on choosing ADO instead of RDO
- Any Pos and Cons on chossing either one data model?
Who is Participating?
Exploring ActiveX Data Objects from an RDO Point of View

Leveraging existing software designs with the new Universal Data Access interface from Microsoft.


No consultant worth his or her salt will tell you to fix something that isn’t broken. This paper is not about "fixing" existing Remote Data Objects (RDO) applications that are doing the job they were designed to do. It is about knowing what benefits can be achieved by using ActiveX® Data Objects (ADO) when it makes sense. ADO adds a number of new choices when you need to access data. Specifically, ADO is designed to better address the special needs of Internet developers, developers needing to access special types of data—in addition to traditional relational sources, and in the process, permit creation of faster, smaller and more efficient applications. With the release of version 1.5, ADO is well on its way to becoming the de facto standard for data access with the Visual Basic® development system and the rest of the Visual Studio development system languages including Visual C++®, Visual J++™, Visual Basic Scripting Edition, Visual InterDev™ and Microsoft® Office applications.

Question: Are developers going have to re-write their RDO code now that ADO is here? The answer to this questions is no—not unless the ADO’s expanded features offer real solutions to problems that RDO can’t solve.

This paper discusses why Microsoft has chosen to create a new all-encompassing data access interface and how your applications can take advantage of its new features. Does ADO replace RDO? No, ADO is implemented alongside of RDO and DAO as both of these established programming interfaces are still supported. Does ADO 1.5 functionality replace RDO 2.0? No, today ADO does not implement everything that RDO supports. However, ADO 2.0 will support a superset of RDO’s functionality.

This information is presented from the point of view of an RDO developer. After a brief outline of how we got to this point, we walk through a number of typical scenarios in RDO and show how to implement the same functionality in ADO. This paper by no means covers all of ADO’s functionality. For example, ADO implements a number of new features that makes it far better suited for code run remotely on an IIS server, thus making development of Microsoft® Transaction Server or Visual Basic Scripting Edition components easier. However, that’s the meat of another paper.

How Did We Get Here?

Over the last six years or so, Microsoft has been driven by customer demand to release an ever-expanding series of data access interfaces. Beginning with the DB-Library interface for Visual Basic ("VBSQL"), and ending with the most recent interface to OLE DB—ADO. Each of these interfaces addressed broadening customer requirements. As Visual Basic applications grew more sophisticated and data-centric, so did the data access interfaces provided:

•VBSQL – an API-interface to Microsoft® SQL Server™ built around the C-based DB-Library interface. VBSQL provided the high-speed, lightweight interface Visual Basic developers needed to access databases on both Microsoft and Sybase SQL Server. VBSQL cannot access any data source other than a SQL Server—and today VBSQL (and DB-Library) are limited to just Microsoft SQL Server version 6.5 and older. While VBSQL will work with the upcoming version of Microsoft SQL Server, it won’t be upgraded to support Microsoft SQL Server 7.0’s new features.

•ODBC—a widely accepted standard data access interface designed to access any relational database with an ODBC driver. By placing all DBMS-dependent code in the driver, developers could access a common API interface regardless of the back-end database. This gave developers the flexibility they needed, but often at the price of more complex applications. While the ODBC API had fewer calls than VBSQL (and DB-Library), these calls required more and more complex arguments. Because the ODBC API also did not include an interface layer like VBSQL, Visual Basic-based applications were exposed to the expense and hazards of direct API development. In general, ODBC as an interface has been widely accepted, but only when accessed by an object-based interface like DAO, ODBCDirect, or RDO—or even ADO. When the goal was to create a single application that could access more than one database (for example, Microsoft® Access/ Microsoft® Jet and SQL Server), applications often had to add back-end-specific code to deal with special cases or complex data types. This meant the dream of writing a single application to reference more than one type of DBMS was often frustrated by reality. Over the years, ODBC has evolved to be the most widely accepted means to access relational data sources—and some non-relational data sources. The following diagram shows the basic architecture of an ODBC application.

ODBC Application Architecture


•DAO—The first object-based interface that exposed the Microsoft Jet database engine to access native Jet databases, a number of popular ISAM databases, and ODBC data sources. DAO was also coupled into the Visual Basic-based Data control which exposed an entirely new paradigm of "bound" or data-aware controls. This made it easier than ever to create data access applications. However, this ease of use also meant that developers had to be extra-mindful of techniques that made scaling more difficult. DAO is one of the most widely used object-based data access interfaces in use today as millions of Visual Basic and Microsoft Access developers have used it to build applications of all kinds. ODBCDirect is a variation on DAO that exposes many RDO functions.

The evolution of object-based data access interfaces.

•RDO—Remote Data Objects is another object-based data access interface created to provide a tight, lightweight interface to ODBC, while still retaining the ease of development provided by DAO. By patterning its object model after DAO, RDO provided an easy-to-program interface that exposed virtually all of ODBC’s low-level power and flexibility. RDO is somewhat limited in that it did not access Jet or ISAM databases particularly well, and could only access relational databases through existing ODBC drivers. However, RDO has proven to be the interface of choice for a large number of SQL Server, Oracle and other large relational DBMS customers. RDO provides the objects, properties and methods needed to access the more complex aspects of stored procedures and complex result sets.

•OLE DB—This new low-level object-based interface introduces a "universal" data access paradigm. That is, OLE DB is not restricted to ISAM, Jet, or even relational data sources, but is capable of dealing with any type of data—information—regardless of its format or storage method. OLE DB is not designed to be accessed directly from Visual Basic due to its complex interfaces but ADO exposes virtually all of OLE DB’s functionality—so this is really not a requirement for Visual Basic developers.

•ADO—ActiveX Data Objects is the object-based interface to OLE DB. By using a DAO-like data access interface, developers can now access an even broader variety of data sources—using both OLE DB service providers and existing ODBC drivers through its OLE DB for ODBC intermediate interface. ADO’s most serious shortcoming at this point is its scope. Currently, ADO only implements part of RDO’s broad range of functionality—but ADO version 2.0 is expected to implement a superset of both RDO and DAO. This paper will outline the current state of ADO development and briefly outline how RDO’s object model maps to ADO.

•RDS-Remote Data Service ADC has now been integrated with ActiveX Data Objects (ADO) to provide data remoting within the same programming model as ADO. This makes it easier to design, code, and deploy both web-based and LAN-based applications. To clarify the relationship of ADC to ADO, ADC is now known as the Remote Data Service (RDS), a feature of ADO. RDS goes beyond the current generation of web data access tools by allowing clients to update the data they see.

A Closer Look at ADO

Microsoft ActiveX Data Objects (ADO) enable your client applications to access and manipulate data in a database server and other data stores through any OLE DB provider. The primary benefits of ADO are ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and web-based applications, including the following:

•Independently-created objects. Unlike Data Access Objects (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. For example, you don’t have to open a connection to create a Recordset—you simply have to declare it. This allows you to create and track only the objects you need, and also results in fewer ADO objects and thus a smaller working set. This technology also enables an entirely new data access paradigm—that of dissociate result sets managed independently of connections and specific data sources.

•Batch updating helps improve performance by locally caching changes to data, then writing them all to the server in a single update.

•Support for stored procedures with in/out parameters, return values and multiple recordsets.

•Different cursor types, including the potential for support of back-end–specific cursors.

•Support for limits on number of returned rows and other query goals for performance tuning.

•Automatic or manual parameters management for better performance and more control over parameters.

•Free-threaded objects for efficient web server applications.

Note: While ADO supports these features, all of the underlying providers and drivers called by ADO may not. Check the topic, Using OLE DB Providers with ADO located on as well as the documentation for the underlying providers and drivers to determine what functionality they support. Unless otherwise noted in the language reference topics, all of the ADO 1.5 Objects, Methods, and Properties described in this document are available when used with the Microsoft OLE DB Provider for ODBC and Microsoft SQL Server version 6.5.

Remote Data Service

ADO’s Remote Data Service (RDS) allows data remoting. This means you can move data from a server to a client application or web page, manipulate the data on the client, and return updates to the server in a single round trip. Previously released as Advanced Data Connector 1.0, RDS has been combined with the ADO programming model to simplify client-side data remoting. For more information, see the Remote Data Service documentation.

Installing ADO

As part of the Microsoft Data Access Components (MDAC), ADO and RDS are automatically installed and registered by a host product, such as Microsoft® Internet Information Server. The host product setup program may require that you specifically request the Microsoft Data Access Components in order to have ADO and RDS installed. In any case, you can find ADO and MDAC on the web at

ADO is a dual-interface COM type library. The filename is msado15.dll. The program ID (ProgID) is "ADODB." In 2 and 3 tier database applications, ADO code that is to execute on the client uses the ProgID "ADOR" instead.

To use ADO with Microsoft Visual Basic or Microsoft Office, you also need to establish a reference to the ADO type library. Select References from the Project menu, check the box for "Microsoft ActiveX Data Objects 1.5 Library," and then click "OK." ADO objects, methods, and properties will then be accessible through the Visual Basic for Applications(VBA) Object Browser and the IDE Editor.

To use ADO with Microsoft Visual J++, you also need to create a set of class files using the "Java Type Library Wizard". Select the Java Type Library Wizard from the Tools menu, check the box for "Microsoft ActiveX Data Objects 1.5 Library," and then click "OK." The wizard will then create the necessary Java class files for ADO.

The New ADO Object Model

ADO "flattens" the rather complex RDO (or DAO) object model to facilitate understanding and use of the object model. Shown below is a diagram showing the ADO interfaces and how they relate to one another:

ADO Interfaces


Although ADO objects can be created outside the scope of a hierarchy, the objects exist within hierarchical relationships, as shown in the ADO Object Model. Notice that this model seems considerably simpler than RDO’s, so don’t expect a 1:1 correspondence with ADO objects, properties and methods. There will be lots of documentation and helpful hints on how to map the DAO and RDO model to ADO.

There are 7 objects in the ADO object model. In some ways these objects are similar to RDO objects but often combine functionality into a flatter model.

•Command - Maintains information about a command, such as a query string, parameter definitions, etc. You can execute a command string on a Connection object or a query string as part of opening a Recordset object, without defining a Command object. The Command object is useful where you want to define query parameters, or execute a stored procedure that returns output parameters. The Command object is similar to RDO’s rdoQuery object. It supports a number of new properties used to describe the type and purpose of the query—these help ADO optimize the operation being performed.

•Connection - Maintains connection information with the data provider. This object is similar to the rdoConnection object in most respects. The connection maintains properties to indicate the cursor type, connect string, query (command) timeout, connection timeout, and other ADO-specific properties such as Default Database and Isolation level (and more).

•Error - Contains extended error information about an error condition raised by the provider. Since a single statement can generate two or more errors, the Errors collection can contain more than one Error object at a time, all of which result from the same incident.

•Field - Contains information about a single column of data within a Recordset. The Recordset object features a Fields collection to contain all of its Field objects. This is equivalent to RDO’s rdoColumn object but it has far more information about the field than RDO such as precision and numeric scale. Richer data type support is also available in ADO.

•Parameter - A single parameter for a parameterized Command. The Command object features a Parameters collection to contain all of its Parameter objects. This is equivalent to RDO’s rdoParameter object but again has more detail than provided in RDO. Like RDO’s rdoParameters collection, ADO’s Parameters object can be created automatically by sending queries to the DBMS. However, you can also build this collection yourself to improve performance at runtime—something RDO does not support.

•Property - A provider-defined characteristic of an ADO object. There is no RDO equivalent to this object, but DAO has an object something like this. ADO objects have two types of properties: built-in, and dynamic. This is one of ADO’s neatest features in that it lets the ADO service provider author expand ADO to address special interfaces that only this driver supports.
•Built-in properties are those properties implemented in ADO and immediately available to any new object, using the familiar MyObject.Property syntax. Built-in properties do not appear as Property objects in an object’s Properties collection, so while you can change their values, you cannot modify their characteristics or delete them.

•Dynamic properties are defined by the underlying data provider, and appear in the Properties collection for the appropriate ADO object. For example, a property specific to the provider may indicate if a Recordset object supports transactions or updating.

These additional properties appear as Property objects in that Recordset object’s Properties collection. Dynamic properties can be referenced only through the collection, using the MyObject.Properties(0) or MyObject.Properties("Name") syntax. You will find that each data provider (might) implement one or more special properties to deal with special provider-specific operations.

•Recordset - A set of records returned from a query and a cursor into those records. You can open a Recordset (i.e., execute a query) without explicitly opening a Connection object. However, if you do first create a Connection object, you can open multiple Recordset objects on the same connection. Generally, the ADO Recordset is equivalent to RDO’s rdoResultset. Again, the ADO object supports a number of new (or changed) properties and methods. We will be discussing these as we go.

The ADO Object Model from an RDO Perspective

Generally, the ADO object model is flatter (has fewer objects) but more properties, methods and method arguments. For example, there are no equivalents to the rdoEngine or rdoEnvironment objects—these expose the ODBC driver manager and hEnv interfaces. The InfoMessage event supplied by the rdoEngine object is not supported in ADO at this time (nor are any of the events). Nor can you create ODBC data sources from ADO—despite the fact that your interface might be through the ODBC OLE DB service provider—but this is coming in a later version. As we work through the rest of the ADO interface, we will find other dissimilarities. Some of these differences are covered by new approaches to the problem (and are addressed), some were left behind for one good reason or another, and some of which are yet to be implemented.

Migrating to ADO

With the introduction of ADO, developers can create applications that perform all of the fundamental data-access operations that all client/server and multi-tier applications require. ADO 2.0 promises to implement a superset of RDO’s and DAO’s functionality. No, that does not mean that the code you have been writing in RDO and DAO will automatically morph over to equivalent ADO code when the time comes to convert. However, it does mean the problems you have solved with these older object models in the past will be solvable with ADO—and more--ADO implements a number of new data access strategies never before available to address new problems.

When deciding to migrate to ADO, you have to decide if ADO’s additional capabilities are enough to justify converting existing software. Another consideration is how your existing developers can leverage their existing RDO development skills. Although RDO will be available indefinitely, it is always a good idea to consider ADO for applications currently under design.

Because ADO 2.x is expected to exceed RDO’s functionality, ADO work done now can be leveraged even more easily when ADO 2.0 is released. However, if you must use the features available in RDO 2.0 today, be sure to study this paper for code that might not be easy to convert or for programming paradigms that are most like ADO’s. This will make the eventual transition easier.

Which Data Access Interface Is the "Right" One?

To get an in-depth understanding of the issues, see the paper "Choosing the Right VB5 Data Access Interface" on This paper concludes that if ADO will address your current needs, use it. Otherwise, for new designs, use RDO for client/server systems and DAO for ISAM or small departmental systems. There are other factors to consider when choosing a data access interface, so this simplistic statement should not be taken without considering the other factors discussed in the aforementioned paper.

Using RDO to Solve Basic Data Access Problems

The following sections discuss how to solve some basic data access problems using RDO. This section is followed by a parallel discussion on how to perform the same operations with ADO. Each of these "problems" addresses a SQL Server database—a variation of the Biblio database installed with Visual Basic 5.0.

RDO: Establishing a Connection

Getting a connection open requires a connect string and a decision or two about how ODBC is to create the connection. Note that a connection is not required by RDO to create an rdoQuery object, but is required to initially create an rdoResultset object.

Dim cn As New rdoConnection
Dim cnB As New rdoConnectionConst ConnectString = "uid=billva;pwd=bv;" _
driver={SQL Server};server=sequel2;" _

This connect string accesses a specific SQL Server and permits ODBC to open a "DSN-less" connection. Note that this is a typical ODBC connect string with all of the standard arguments.

Private Sub Form_Load()
With cn
cn.Connect = ConnectString
cn.LoginTimeout = 10
cn.CursorDriver = rdUseNone
cn.EstablishConnection rdDriverNoPrompt
End With

Establish a connection specifying the type of cursor driver and login timeout. By default, RDO uses rdUseIfNeeded, which invokes server-side cursors on SQL Server—we override this in our example. We also choose to generate an error if the user-id and password do not match. The second connection performs the client-batch updates. Notice that the connect string used in the first operation.

With cnB
cnB.Connect = ConnectString
cnB.CursorDriver = rdUseClientBatch
End With
End Sub

Handle any errors that occur when the connection is opened. This event fires when the connection operation is completed so we can test to see if it worked and enable any buttons that rely on an open connection.

Private Sub cn_Connect(ByVal ErrorOccurred As Boolean)
If ErrorOccurred Then
MsgBox "Could not open connection", vbCritical
RunOKFrame.Enabled = True
End If
End Sub

RDO: Run a Basic Query

To return a basic result set based on an SQL statement. In this case we execute a restricted query and pass the result set to a control that simply dumps the result to the MSFlexGrid. Building a result set requires use of an open connection.

Private Sub RunButton_Click()
Dim rs As rdoResultset
Set rs = cn.OpenResultset("select * from titles where title like '%h'")
rdoGrid1.ShowData rs
End Sub

RDO: Display a Result Set in the MSFlexGrid

This routine is the ShowData method of a custom ActiveX control used to display data from a result set in an MSFlexGrid. The code sets the grid up based on the rdoColumns property titles and initializes the grid making it ready for the data. Note the use of the OrdinalPosition property to index the result set rdoColumns property. There are two sets of code to extract data from the rdoResultset—one that uses GetClipString, and another that uses the GetRows method to contrast typical approaches to this problem. Note that ADO 1.5 does not initially support the GetClipString method—its support in ADO 2.0 is under consideration.

Public Function ShowData(Resultset As rdoResultset) As Variant
Dim cl As rdoColumn
Static GridSetup As Boolean
Dim MaxL As Integer
Dim rsl As rdoResultset
Dim Rows As Variant
On Error GoTo ShowDataEH
Set rsl = Resultset
If GridSetup Then
FGrid1.Rows = 51
FGrid1.Cols = rsl.rdoColumns.Count
FGrid1.Row = 0
For Each cl In rsl.rdoColumns
FGrid1.Col = cl.OrdinalPosition - 1
FGrid1 = cl.Name
If rsl.rdoColumns(cl.OrdinalPosition - 1).ChunkRequired Then
MaxL = 1
MaxL = rsl.rdoColumns(cl.OrdinalPosition - 1).Size + 4
End If
If MaxL > 20 Then MaxL = 20
FGrid1.ColWidth(FGrid1.Col) = TextWidth(String(MaxL, "n"))
Next cl
GridSetup = True
End If

FGrid1.Rows = 1 'Clear Grid of data (except titles)
FGrid1.Rows = 51
FGrid1.Row = 1
FGrid1.Col = 0
FGrid1.RowSel = FGrid1.Rows - 1
FGrid1.ColSel = FGrid1.Cols - 1
FGrid1.Clip = rsl.GetClipString(50, , , "-")
' With FGrid1
' Rows = rsl.GetRows(FGrid1.Rows)
' For i = 1 To UBound(Rows, 2) ' Loop through resultset returned
' .Row = i
' For j = 0 To UBound(Rows, 1) - 1
' .Col = j
' .Text = "" & Rows(j, i)
' Next j
' Next i
' .Row = 1
' End With
FGrid1.RowSel = 1
FGrid1.ColSel = 0
Exit Function

Select Case Err
Case 40022:
Resume ExitShowData
Case 13
FGrid1.Text = "< >"
Resume Next
Case Else
MsgBox "Could not display data: " & Err & vbCrLf & Error$
Resume ' ExitShowData
End Select

End Function

Using ADO to Solve Basic Data Access Problems

The following discussion walks through code used to perform the same basic operations using ADO. Initially we create a set of stand-alone ADO objects referenced off of the ADODB object. These objects are fleshed out later in the code when we set specific properties to open connections and execute result sets.

ADO: Establishing a Connection

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cnB As New ADODB.Connection
Dim Qy As New ADODB.Command

The next line of code creates a connect string—just like RDO’s. In both cases we are using ODBC’s "DSN-less" connection strategy to save time and increase performance. It seems the documentation calls for a somewhat different Connect string that references an ADO "Provider" instead of a "Driver" and does not call for the "dsn=" syntax, but the ODBC connect string used in RDO seems to work fine.

Const ConnectString = "uid=billva;pwd=bv;driver={SQLServer}; _ server=sequel2;database=biblio;dsn=''"

These next declarations set up the variables used throughout the example. Note creation of a variant array to hold the result set.

Dim sql As String
Dim rc As Integer
Dim i As Integer
Dim Changes As Integer
Dim bms() As Variant

Next, we open the ADO connection to our database in the Form_Load event. Note that this code is very similar to RDO except that the constants are prefixed with "ad" instead of "rd". While this is the case in this example, many ADO constants are not the same as their RDO equivalents so you cannot just change the first two letters and expect them to work.

Note that we have to use the ADO Properties collection to deal with the desired prompt behavior—but ADO defaults to "no prompt" which makes more sense anyway, so you should not have to specify the prompting behavior. In RDO, we could simply set the behavior using the OpenConnection argument. In ADO we have to set the Properties ("Prompt") property—an example of this is shown in the code.

Keep in mind that the code in your RDO Connect event is handled differently—probably in an in-line ADO error handler, as ADO does not (yet) support events. This means that you will have to work with situations where the "informational" messages sent back by SQL Server will be discarded and that asynchronous open connection routines will have to be coded using synchronous techniques.

Private Sub Form_Load()
With cn
' Establish DSN-less connection
..ConnectionString = ConnectString
..ConnectionTimeout = 10
..CursorLocation = adUseNone
‘ .Properties("Prompt") = adPromptNever ‘ This is not required in ADO
End With
With cnB
..ConnectionString = ConnectString
..CursorLocation = adUseClientBatch
End With
End Sub

ADO: Run a Basic Query

Once the connection is open, we can proceed to running a query. This code is very similar to the code we just executed with RDO. However, in this case we use the new ADO Open method that takes the SQL query and the ADO Connection object as arguments—instead of using the rdoConnection object’s .OpenResultset object. Yes, you can also use the ADO Connection object’s Execute method—just as you could in RDO as long as it didn’t return a rowset.

No you won’t be able to run this query asynchronously in ADO—not until version 2.0. Yes, you can choose to process the query’s resultset asynchronously. That is, ADO will tell the cursor driver to complete population of the result set in the background by setting the "IDBAsynchStatus" property to True. No, ADO will not return control any sooner when this option is set as with RDO asynchronous operations, but the cursor will be populated (in time) without your code having to move to the end of the result set.

Private Sub RunButton_Click()
Dim rs As New ADODB.Recordset
rs.Open "select * from titles where title like '%h'", cn
ADOGrid1.ShowData rs
End Sub

ADO: Display a Result Set in the MSFlexGrid

The following code implements the same ShowData method of a custom ActiveX control adapted from the RDO control. In this case, since we can’t use GetClipString, we use GetRows instead. Since we then have to parse the resulting Variant array ourselves, the routine is noticeably slower. If you have problems getting the GetRows function to work, you will have to convert to a more conservative (read slower) approach that loops through the rows. However, this technique should be avoided as it is far less efficient than either using GetRows or binding directly to a control.

Note how the OrdinalPosition property can no longer be used as an index on the Fields collection to pull out the column titles (as we could in RDO). To deal with this substitute a new integer counter to address the column being worked on. We also had to use the DefinedSize and ActualSize properties to find the TEXT and IMAGE data type fields that would not fit in a column. These new properties make it easier to determine the details of specific field values. I also added additional code to deal with BLOB types when we worked through the data columns.

Public Function ShowData(Resultset As Recordset) As Variant
Dim cl As Field
Static GridSetup As Boolean
Dim MaxL As Integer
Dim Op As Integer
Dim rsl As Recordset
Dim rows As Variant
On Error GoTo ShowDataEH
Set rsl = Result
Just use ADO, the support for RDO will be terminated by Microsoft in the near future !
ADO is the best of (all) the other data layers.

greetinx, RoverM
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.