Big Data

Build efficient data access with RDO

RDO is designed to access remote ODBC relational data sources. It's smaller and faster than DAO and offers more flexibility in handling various types of result sets. Our look at data access solutions continues with an intro to RDO and a little code demo.

Remote Data Objects (RDO) is an object-oriented data access interface to ODBC combined with the easy-to-use style of Data Access Objects (DAO). RDO exposes almost all of ODBC's low-level power and flexibility, and its objects and collections provide a framework for creating and manipulating remote ODBC databases. RDO is smaller and faster than DAO, and because they have similar object models, it’s relatively simple to convert applications that use DAO to applications that use RDO.

RDO objects
To understand RDO better, let's examine the interface’s objects, which are listed in Table A.

Table A
Names and descriptions of RDO objects (Source: Microsoft)

Each object belongs to an associated collection, except the top-level rdoEngine object. RDO automatically creates an instance of rdoEngine and the default rdoEnvironments(0).

To open a DSN-less connection using RDO, you must specify the connection string with parameters. This string specifies the driver and allows connecting without DSN. Of course, you can also use a DSN connection if you prefer.

The rdoConnection represents a physical connection to a database. You have to create a valid connection object before you can get or manipulate data. You should always close the rdoConnection object when it's no longer necessary. You can do that using the object’s Close method. When you close the connection, the related rdoQuery and rdoResultset objects are also closed. The CursorDriver property of the connection object determines the type of cursor that will be used for the result set. The CursorDriver property can be set to:
  • ·        RdUseIfNeeded—The ODBC driver chooses the appropriate cursor type
  • ·        RdUseOdbc—An ODBC cursor will be used
  • ·        RdUseServer—A server-side cursor will be used
  • ·        RdUseClientBatch—An optimistic batch cursor will be used

The rdoQuery is a compiled SQL statement that manages SQL queries requiring the use of input, output, or input/output parameters. If your stored procedure returns output parameters or a return value, or if you want to use rdoParameter objects to handle the parameters, you have to use an rdoQuery object to manage your stored procedure.

There are four types of rdoResultset objects:
  • ·        RdOpenForwardOnly—Default type of resultset; rows of the resultset can only be searched in forward direction
  • ·        RdOpenKeyset—Can have updatable rows; movement within the resultset is unrestricted; can contain columns from one or more tables in a database
  • ·        RdOpenDynamic—Can have updatable rows; can contain columns from one or more tables in a database
  • ·        RdOpenStatic—Membership, order, and values in a result set are fixed when the cursor is opened.

RDO object model
Because the RDO object model (Figure A) is similar to the DAO object model, modifying your code to take advantage of RDO for remote databases is not particularly complicated. In fact, if you use classes in your VB code to handle all of your database needs, modifying the code to use RDO will be a snap. If, however, your data access code is not well organized, and you would need to go through every form to make changes, a conversion to RDO may not be worth it. But you might want to use RDO for your next project.

Figure A
The RDO object model (Source: Microsoft)

The RDO 1.0 rdoPreparedStatement object and rdoPreparedStatements collection are supported by RDO 2.0 but only for backward compatibility. You should convert your code to use the rdoQuery object and rdoQueries collection instead.

Advantages and disadvantages of using RDO
RDO has proven to be a fast and robust solution for accessing SQL, Oracle, Sybase, and other large relational databases. However, access to relational databases is strictly through existing ODBC drivers. In addition, RDO doesn't support Jet or ISAM databases very well. But unlike DAO, RDO allows you to execute queries against stored procedures and handle multiple result sets.

Let's code
Let’s create a simple VB project that uses RDO to access the data stored in a remote database. Follow these steps:
  1. 1.      Fire up VB and start a new project.
  2. 2.      Go to Project References and check Microsoft Remote Data Object 2.0, as shown in Figure B.

Figure B
References showing Microsoft Remote Data Object selected

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

Figure C
Input form

  1. 4.      Add the code shown in Listing A to the Form Load event.
  2. 5.      Modify the connection string in RDOConn.Connect = "uid=sa;pwd=;driver={SQL SERVER}; server=sqlserver;database=pubs;" to reflect the driver, server, and database names.
  3. 6.      Modify the query to execute in sSQL = "SELECT au_lname FROM AUTHORS" to reflect the query you want to run
  4. 7.      Press [Ctrl][F5] to run the project. You should see a screen like the one in Figure D.

Figure D
You should see results similar to these when you run your project.

What comes next?
In this sample, we used RDO to get data from a remote database and display the list of last names from the table AUTHORS. Next time, we’ll look at ADO, its object model, and some code samples. We’ll also discuss ADO’s advantages and disadvantages and take a look at ADO.NET.

Editor's Picks

Free Newsletters, In your Inbox