Gaining Remote Access to Microsoft Access with RDO

This tutorial shows how to use the RDO control, which is similar to the ADO control, to access a Microsoft Access Database on a network drive from a machine on another node of the network.

Introduction

In the early days of Microsoft Access,  DAO (Data Access Objects) was the only interface that exposed the Jet Engine, and VB developers could directly connect to Access through ODBC. This worked best with local deployments of applications. RDO, which is an acronym fro Remote Database Objects, is an object oriented data access interface to ODBC working similar to the familiar DAO. RDO is the access method of choice when connecting to larger databases such as SQL Server, Oracle and others because it supports properties and methods that are suitable for these servers. These sophisticated servers can run complicated queries and stored procedures. It can also, however, access JET and ISAM databases through existing ODBC drivers which have limited SQL capability.

ADO, another acronym for ActiveX Data Objects, succeeded the DAO/RDO duo and maps more or less to RDO methods. The object model of ADO is simplicity itself, but it has a large number of properties, methods and events. While ADO is hierarchical like DAO and RDO, it allows the creation of objects outside the hierarchy. It should be emphasized that Microsoft support for DAO/RDO  is only for backward compatibility.

Another direction in the evolution of data access as visualized by Microsoft is to move slowly away from MDAC, which bundles all the three letter acronym methodologies. The reason it is being bundled as it is with the Windows OS, is that different versions of MDAC can give rise to problems during deployment.  It is for this reason that the SQLClient is not a part of the MDAC series, but a stand alone data access method.

This tutorial is about using the RDO control which is similar to the ADO control to access a Microsoft Access Database on a network drive from a machine on another node of the network. The steps involved in creating a Visual Basic application which accesses a Nwind.mdb file on a machine Hodentek from an application running on another machine XPHTEK connected to the network will be described.

{mospagebreak title=Remote data location}

The remote Microsoft Access database, nwind.mdb is contained in a network share on the Hodentek machine as shown here. The shared folder Htek on this machine contains the database file.

 

Visual Basic Application location

On the machine XPHTEK, where the VB application is located, create a shared drive as shown.

{mospagebreak title=Create an ODBC source on XPHTEK}

Since the RDO control connects through the ODBC connection, create a System DSN which connects to the database on the remote machine. For this, bring up the ODBC Data Source Administrator wizard from the Start–>All Programs–>Control Panel–>Administrative Tools–> Data Sources (ODBC) shortcut, which will open up the window shown below.

Click on the Add… button, which brings up the list of ODBC drivers available on the machine.

Highlight the Microsoft Access (.mdb) driver and click Finish. The ODBC driver on this machine is [ODBCJT32.dll], Version:4.00.6304.00. This brings up the next window where you will specify the DSN. You must give a data source name (DSN) and an optional description of the data source.  The data source name for this example is NWonHodentek.

When you click OK to the above window you will be asked to locate the database, and you can find its location by bringing up the window Select database.

Since the database is on a network drive, you should bring it up by using the drop-down as shown. This will show the nwind.mdb file in the shared folder as shown. Another way would be to click on the button Network… which will open up the Map Network Drive window, where you can choose the network drive on which the remote database is situated, or browse to it.

Clicking OK to the screen will bring you back to the ODBC Microsoft Access Setup screen. If you click on the Advanced button you will see the information for this DSN as shown here.

After setting up a DSN for a remote database is complete, the ODBC Data Source Administrator window shows the System DSN, NWonHodentek as shown here.

{mospagebreak title=Visual Basic Application}

Adding a MSRDC Activex Control to tool box

Start the Visual Basic Program (VB 6.0) on the Hodentek machine. Create a project, RemoteAccess.vbp, and add a form to it. Follow Project–>Components from the main menu to open up the Components window with three tabs, Controls, Designers, Insertable Objects. In the Controls tabbed page scroll down to place a check mark for the Microsoft RemoteData Control 6.0 as shown, and a check mark for Selected Items Only, and click OK. This adds the ActiveX control MSRDC20.OCX to the tool box, a small icon with an antennae which shows ‘MSRDC’ when you hover over the icon.

Properties and Methods of MSRDC

The properties and methods of this object can be reviewed in the Object browser as shown here. The data source name of this control is the one that was configured earlier.

{mospagebreak title=Controls on the form}

From the tool box click the MSRDC control (added in the previous step) and draw it out on the form. It will look very much like the DAO control except that it has the caption MSRDC1 as shown. Add three text boxes and take out the text property so that they look like blank text boxes in the design view as shown.

Right click the MSRDC control to bring up the Property page for this control. Click on the drop-down arrow for Data Source and you should be able to locate the DSN created earlier as shown. The RDO does not have a SQL designer of its own, and you need to script the SQL statement for data access. SQL statements scripted here should be supported on the remote database. For this example a simple query which returns all columns is shown here.

Now you may access the property window of this control as shown here. All the data related items are categorized in this view. This window shows the SQL statement, the access permissions, and so on.

Data binding to Textbox Control

The way you bind the fields (columns) to the text boxes is very similar to the DAO method that you may be used to. Right click on the text box to bring up its properties window. Here you need to tweak two of the properties. When you click on an empty area in the box next to DataSource in the Data category you should be able to see the MSRDC1. This the control source for the text box. The field (column) can be selected by clicking on an empty area corresponding to the DataField in the Data Category. From the drop-down menu, which contains all the columns returned by the select statement, pick the one that you need to retrieve. Similarly the other two text boxes are configured. The text-boxes as configured return the first name, last name and telephone number of the employees table in the remote database.

When you save and run the program you should be able to navigate through the result set as shown in this screen shot of the form.

Summary

When using an ODBC DSN, it is really very easy to retrieve the result sets (same as Recordsets) from a remote Microsoft Access database. It is also possible to modify records which are not covered in this tutorial. It should be emphasized that DAO and RDO technologies are only present for backward compatibility. Moving on to ADO is recommended. There are plenty of articles and white papers on Microsoft and other sites where several of these migration issues are discussed.

2 thoughts on “Gaining Remote Access to Microsoft Access with RDO

  1. Is this the same RDO that preceded ADO and we all used back in 1997? It was great in its time but I don’t think MS supports it anymore, or VB6 for that matter…

  2. You have one up on me. OK. You are right, RDO will not be supported in the future. There’s lot of commotion that vb6 is on its way out. But lots of people are still using it. For some it was love at first sight.
    I see people asking about RDO quite often. Why even today people are still using DAO. One faq is how can I use DAO with Access 2003? You see, there is a disconnect. But in case you are thinking of using it, and you want to know how to, please read this article.
    Any questions, please let me know. I look forward to your comments.

    sincerely,

    J

[gp-comments width="770" linklove="off" ]