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.