Generally, DAO is Access specific and is probably the fastest product to work strictly with Access databases. It is not used with SQL Server unless you go through an ODBC interface. DAO is basically the library for data manipulation on an Access MDB. So, to use DAO with SQL Server the layers are DAO, Jet, ODBC and finally SQL Server. ADO along with OLE DB comes under the umbrella of MDAC (Microsoft Data Access Components), which comes native on the Windows 2000 and above OS's. ADO is the counterpart to DAO in that it is the library for all the data objects. It uses OLE DB kind of like DAO uses Jet to get at the data in the database.
Both ODBC and OLE DB have another layer underneath that provide service to the specific database. There is an ODBC driver for Access, SQL Server, Oracle, etc... There are OLE DB providers for Access, SQL Server, Oracle, etc....
Unlike DAO (Access MDB) the ADO libraries are used with many front end products. You can use DAO with front ends that use an Access MDB. ADO is more versital in that the same ADO code can be used in multiple front end products that go to multiple database products.
The SQL Server native OLE DB provider for ADO will be faster than ODBC linked tables. With ODBC linked tables you end up using Jet along with ODBC, which is even more overhead than going through just the ODBC layer. Be aware, that the OLE DB provider named MSDASQL routes you through ODBC so it will not be as efficient as the native provider. Use the provider called SQLOLEDB.1 since it will interface directly with SQL Server.
