databasedev.co.uk - database solutions and downloads for microsoft access

ActiveX Data Objects vs. Data Access Objects

DAO stands for "Data Access Objects" and ADO stands for "ActiveX Data Objects". But which method is the best to use?

This is a question that has been asked for years by developers and it is still being debated today. There are many compatibilities between the two methods, but the most significant difference between them is the ability to work with data outside of Access and the JET engine environment.

Unless you're an experienced developer working with split database systems, the importance of using one method over the other is quite marginal. Local databases and smaller projects should use DAO, while larger ones should use ADO. The reason for this is because developers generally want to keep things as simple as possible. ADO is very efficient with outside (remote) connections, while DAO is good for manipulating local objects.

The opinions on the differences between these two methods are virtually endless, so let's take a look at a small example that uses an ADO connection to loop through an "orders" table and display one field:

Function ShowRS()

On Error GoTo Err_Handle

Dim cnStr As String
Dim cn As ADODB.Connection
Dim cnRs As New ADODB.Recordset
Set cn = CurrentProject.Connection

cnRs.Open "SELECT * FROM orders", cn

   With cnRs

      .MoveLast
      .MoveFirst
      
         Do Until .EOF
            Debug.Print !orderid
               .MoveNext
         Loop
   
   End With

Err_Handle:
   cnRs.Close
   cn.Close
   Set cnRs = Nothing
   Set cn = Nothing
            
End Function

This can also be done using DAO. The following code outputs the exact same information as previous function:

Function ShowRS()

On Error GoTo Err_Handle

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM orders", dbOpenDynaset)

   With rs

      .MoveLast
      .MoveFirst
      
         Do Until .EOF
            Debug.Print !orderid
               .MoveNext
         Loop
   
   End With

Err_Handle:
   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing

      If Err.Number <> 0 Then
         MsgBox (Err.Description)
      End If

End Function

As you can see, there is not much difference in the amount of code that has to be written using the two methods, but the syntax is quite different.

ADO is a relatively new technology when compared to DAO. Because of this, ADO has many more resources that can be utilized by a developer. It works more with "schemas", which have a wider reach to data than recordsets do. Consider an Excel spreadsheet. One of the more popular objects in Excel is the "range". In fact, this is closely related to a schema. If you open an Excel workbook through an ADO connection, named ranges actually become part of the schema collection.

Say we want to open a workbook from Access and print out the field names on Sheet1. Here is the datasheet we are going to analyze:

To get the field names, we might write a procedure like the following:

Function GetExcelFields()

On Error GoTo Err_Handle

Dim i As Integer
Dim strSource As String
Dim strSheet As String

Dim cn As ADODB.Connection
Dim cnRs As ADODB.Recordset
Set cn = New ADODB.Connection
Set cnRs = New ADODB.Recordset

strSource = "c:\orders.xls"
strSheet = "Sheet1"

   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strSource & ";" & _
                          "Extended Properties=Excel 8.0;"
      .Open
   End With

      cnRs.Open "SELECT * FROM [" & strSheet & "$]", cn
      
         With cnRs
         
            For i = 0 To .Fields.Count - 1
               Debug.Print .Fields(i).Name
            Next i
                  .MoveNext
         
         End With

Err_Handle:
   cnRs.Close
   cn.Close
   Set cnRs = Nothing
   Set cn = Nothing

      If Err.Number <> 0 Then
         MsgBox (Err.Description)
      End If
            
End Function

The result of the code can be seen in the immediate window:

There is plenty to learn from using ADO, and most of it comes from split database environments where the backend is located on a server. It is a well known fact that Microsoft has stopped supporting its outdated technologies, even those that are only a few years old. However, both DAO and ADO will be around for quite some time yet, because the majority of "small-scale" Access users have no need for the newer technology. Plus, maintaining an older technology that is already built-in to a core product is very inexpensive.

So if you're just starting out, DAO is the thing for you. Otherwise, you might want to consider what ADO has to offer.