jwcolby
jwcolby at colbyconsulting.com
Tue May 15 10:26:35 CDT 2007
1) What is the difference? DAO is a library specific to MDBs. This library has objects for databases, tables, queries, forms and reports (documents) modules etc. IOW this library "knows about" the structure of the MDB and allows the programmer to program to the pieces of an MDB container. Many programmers think of DAO as a recordset / field library, but that is only a tiny piece of the DAO library. ADO is an abstracted library that is used to manipulate tables, fields and their properties. It does not understand the structure of the object which contains the tables / fields 1a) how do I know when looking at code it's either one? This is a good question and one that is the source of much confusion, EVEN FOR VBA, which is why you should in your code dimension an object as DAO.Recordset (for example) or ADODB.Recordset. Both libraries have a recordset object and if you do not prefix the dimensioned object with the correct library, then VB attempts to use the reference physically closest to the top of the reference list. BTW DAO and ADO recordsets are FUNDAMENTALLY DIFFERENT and will not function interchangeably Also, in general, if a reference is to any part of the mdb structure, then it is DAO since ADO does not understand the MDB structure. 1b) Can they be mixed? Yes they can, or at least you can use BOTH libraries, even in consecutive lines of code. Again, the DIMENSION statement determines which library the object uses to process the activity. 2) Which is recommended for MDBs. THIS is a loaded question. ADO is abstracted. Allowing you to switch from MDB to SQL Server to EXCEL at the drop of a connection string. It is also slower, often times RADICALLY slower. DAO knows the details of the data store intimately and can thus optimize its operations whereas ADO cannot. OTOH, DAO can only be used for an MDB or a LINKED table. 3) I read somewhere, that ADO is required for ODBC to SQL Server with an Access FE - is that correct? No. If the table is linked, the link handles the connection to the data store and either ADO or DAO can be used to dimension the recordset object. I BELIEVE that the link uses ODBC internally, but does not use ADO itself. OTOH, ADO can (in code) directly reference tables out in a NON-MDB data store using ODBC whereas DAO cannot. So the answer depends on whether the table that you want to manipulate is linked or not. HTH. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson Sent: Tuesday, May 15, 2007 10:18 AM To: Access Developers discussion and problem solving Subject: [AccessD] DAO vs ADO I'm confused when looking at these two libraries. My questions are many, so I listed only a few below. 1. What is the difference? Yeah I know, this is a loaded question, but how do I know when looking at code it's either one? Can they be mixed? 2. Which is recommended for MDBs - or is there no difference in performance? 3. I read somewhere, that ADO is required for ODBC to SQL Server with an Access FE - is that correct? That's enough for now, Thanks! Jim Jim H. Hewson Applications Support Manager Karta Technologies, Inc. 5555 Northwest Parkway San Antonio, Texas 78249 210-582-3233 jhewson at karta.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com