KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q142929: INFO: Comparing DAO and RDO in Visual Basic 4.0

Article: Q142929
Product(s): Microsoft Visual Basic for Windows
Version(s): 4.0
Operating System(s): 
Keyword(s): kbDAOsearch kbODBC kbRDO kbVBp kbVBp400 kbGrpDSVBDB kbDSupport
Last Modified: 05-FEB-2002

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Enterprise Edition for Windows, version 4.0 
-------------------------------------------------------------------------------

SUMMARY
=======

This article discusses differences in the DAO (Data Access Object) model versus
the RDO (Remote Data Object) model in Microsoft Visual Basic 4.0, Enterprise
Edition for Windows. Remote Data Objects only come with the Enterprise Edition,
so you will need this edition to take advantage of the RDO model.

MORE INFORMATION
================

According to the online help, the Data Access Object model is defined as
follows:

You can use Data Access Objects to manipulate databases in either the native Jet
database engine .MDB format or in other installable ISAM database formats,
including Fox, dBASE, Excel, Btrieve, Paradox, and delimited Text.

In addition, you can use the Microsoft Jet database engine to access Microsoft
SQL Server and any other database that can be accessed with an ODBC driver using
the same DAO code.

According to the online help, the Remote Data Object model is defined as
follows:

With RDO and the RemoteData control, your applications can access ODBC data
sources without using a local query processor. This can mean significantly
higher performance and more flexibility when accessing remote database engines.
Although you can access any ODBC data source with RDO and the RemoteData
control, these features are designed to take advantage of database servers, like
Microsoft SQL Server and Oracle, that use sophisticated query engines.

By using RDO, you can create simple cursor-less result sets, or more complex
cursors. You can also run queries that return any number of result sets, or
execute stored procedures that return result sets with or without output
parameters and return values. You can limit the number of rows returned and
monitor all of the messages and errors generated by the remote data source
without compromising the executing query. RDO also permits either synchronous or
asynchronous operation so your application doesn't need to be blocked while
lengthy queries are executed.

DIFFERENCES:
------------

The following are differences between DAO and RDO:

- The DAO model is used for ISAM, Access and ODBC databases. The RDO model is
  designed for ODBC databases only, and it has been optimized for Microsoft SQL
  Server 6.0 and Oracle.

- The RDO model can have better performance, with the processing being done by
  the server and not the local machine. Some processing is done locally with
  the DAO model, so performance may not be as good.

- The DAO model uses the Jet Engine. The RDO model does not use Jet Engine, it
  uses the ODBC backend engine.

- The RDO model has the capability to perform synchronous or asynchronous
  queries. The DAO model has limitations in performing these type of queries.

- The RDO model can perform complex cursors, which are limited in the DAO
  model.


Sample Program:

The following sample program will perform identical ODBC database operations in
DAO, then RDO. Compare the DBEngine, Workspace, Database, and Recordset objects
in DAO to the rdoEngine, rdoEnvironment, rdoConnection, and rdoResultset objects
in RDO.

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add two command buttons to Form1.

3. Paste the following code into the General Declarations section of form1.

     Option Explicit
     Private Sub Command1_Click()
       'The following code is used with DAO to open an ODBC database,
       'process a query, and return a set of records.
       'Notice that this code makes a "DSN-less" connection
       Dim ws As Workspace
       Dim db As Database
       Dim rs As Recordset
       Dim sql As String
       sql = "Select * From titles"
       Set ws = DBEngine.Workspaces(0)
       Dim cnStr As String
       cnStr = "driver={SQL Server};server=mysvr;" & _
         "database=pubs;uid=myuid;pwd=mypwd"
       Set db = ws.OpenDatabase(Name:="PUBS", Exclusive:=False, _
         ReadOnly:=False, Connect:=cnStr)
       Set rs = db.OpenRecordset(sql, dbOpenDynaset)
       rs.MoveLast
       MsgBox "DAO: " & Str(rs.RecordCount) & " rows returned."
       rs.Close
       db.Close
       ws.Close
     End Sub

     Private Sub Command2_Click()
       'The following code is used with RDO to open an ODBC database,
       'process an asynchronous query, and return a set of records.
       'Notice that this code makes a "DSN-less" connection
       Dim en As rdoEnvironment
       Dim cn As rdoConnection
       Dim rs As rdoResultset
       Dim sql As String
       sql = "Select * From titles"
       Set en = rdoEngine.rdoEnvironments(0)
       With en
         .CursorDriver = rdUseOdbc
       End With
       Dim cnStr As String
       cnStr = "driver={SQL Server};server=mysvr;" & _
         "database=pubs;uid=myuid;pwd=mypwd"
       Set cn = en.OpenConnection(DSName:="", Prompt:=rdDriverNoPrompt, _
         Connect:=cnStr)
       Set rs = cn.OpenResultset(Name:=sql, Type:=rdOpenKeyset, _
         Option:=rdAsyncEnable)
       While rs.StillExecuting
         DoEvents
       Wend
       rs.MoveLast
       MsgBox "RDO: " & Str(rs.RowCount) & " rows returned."
       rs.Close
       cn.Close
       en.Close
     End Sub

4. Note that you will need to change your DRIVER, SERVER, DATABASE, UID, and PWD
  in the OpenConnection and OpenDatabase methods. You will also need to modify
  the SQL statement contained in the Command1_Click event to match your own SQL
  data source.

5. Start the program or press the F5 key.

6. You can then click on the Command1 button to execute the DAO model code or
  the Command2 button to execute the RDO model code. Notice that the RDO model
  performs the query asynchronously.

Additional query words:

======================================================================
Keywords          : kbDAOsearch kbODBC kbRDO kbVBp kbVBp400 kbGrpDSVBDB kbDSupport 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB400Search kbVB400
Version           : :4.0
Issue type        : kbinfo

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.