Charlotte Foust
cfoust at infostatsystems.com
Wed May 16 12:59:19 CDT 2007
I disagree, Jim. ADO can't handle most of the UI manipulation that DAO can. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Wednesday, May 16, 2007 10:38 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] DAO vs ADO corrected summarization I would say DAO is but a subset of the functionality of ADO, highly optimized the MDB. There are no operations that DAO can do that ADO can not duplicate but the reverse is not true. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, May 16, 2007 9:11 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] DAO vs ADO corrected summarization Jim, I think that saying ADO is more flexible than DAO is like saying pliers are more flexible than a monkey wrench. They are different tools for different purposes. DAO is certainly more flexible if you only need to access an MDB database. It is all you will ever need, and can manipulate ACCESS objects that ADO simply does not understand. OTOH ADO is more flexible if you might need to switch between different types of data stores. DAO cannot easily do that. OTOH, since LINKS can be used for some very specific types of objects, and you can programmatically manipulate Links with DAO, you can create links to certain types of other objects which allows DAO to "manipulate" data in those specific types of objects. You see what I am getting to here. They are just different animals. Access / JET can link to a very specific set of data stores. ADO can "talk to" any type of data store that you can find a driver for. ADO is much more flexible and easier to use if you want to switch quickly and easily from data store to data store. To do that with DAO, you have to muck around with the links, and even then your choices are limited as to where the data can be. 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: Wednesday, May 16, 2007 11:53 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] DAO vs ADO corrected summarization 1. The difference between DAO and ADO. DAO is optimized for the Jet Engine (1) and is a specific library for MDBs (2). The library has objects for databases, tables, queries, forms and reports (documents) modules etc. The library knows all about the structure of the MDB and allows the programmer to program to the pieces of an MDB container (1, 2, 4). ADO on the other hand 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 (ibid). ADO is a more generic data handling model, and it handles that role much better than DAO in many cases (1, 4). DAO will run parameter queries using references on forms but ADO will fail (e.g. Forms!frmSomething!cmbSomething) (4). When identical functions are included in both object models Access will use the library first referenced unless specified otherwise (3). The two models have objects of the same name but different methods and properties (1). ADO is more flexible than DAO (4). DAO was designed specifically for Jet and ADO was designed as a universal data connection library (3, 4, 5). ADO manipulates "data" not database objects (1). DAO is an object model optimized for data containers and is designed to handle all objects (2). 1a. Examining VBA code to determine which library is used There is much confusion because both libraries have some of the same objects. If an object is not dimensioned (Dim) VB attempts to use the reference physically closest to the top of the reference list (2, 3). DAO and ADO recordsets are FUNDAMENTALLY DIFFERENT and will not function interchangeably. If a reference is any part of a mdb structure, then it is DAO since ADO does not understand the MDB structure (2). You can't pass objects back and forth between ADO and DAO (1). 1b. Mixing DAO and ADO They can be mixed but dimension statements must specifically declare objects as DAO or ADODB (ADOX) (1, 2). The dimension statement binds the variable to the correct object in the correct library and both can be used "at the same time" (2). 2. Recommendation for MDBs ADO is abstracted, allowing one 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. DAO can only be used for an MDB or a LINKED table (2). 3. Connecting to data store When working with an ADP, which is an Access FE directly to SQL Server without linked tables, ADO is necessary (1, 3). DAO can't handle an ADO recordset and vice versa (1). If the table is linked, the link handles the connection to the data store and either ADO or DAO can be used. The link uses ODBC internally, but does not use ADO itself. ADO can (in code) directly reference tables out in a NON-MDB data store using ODBC whereas DAO cannot (2). Recommendations: 1. In VBA dimension (Dim) objects with either DAO or ADODB for similar objects (e.g. DAO.Recordset or ADODB.Recordset). 2. Use DAO when: a. Connecting to a data store via a LINK (SQL Server, MDB, Excel, etc.) b. within an Access MDB container c. References are preceded by "Microsoft DAO Library" (1) 3. Use ADO when: a. ODBC is used to an external data store (SQL Server, MDB, Excel, etc.) b. working with an ADP c. references are preceded by "ActiveX Objects" (1) 1. Charlotte Foust 2. John Colby 3. William Hindman 4. Drew Wutka 5. Jim Dettman Jim jhewson at karta.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins Sent: Wednesday, May 16, 2007 10:33 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] DAO vs ADO Other than that, it is a good summarization. Perhaps it could be placed up on our web site? ======I've already sent a message to John on that! ;) We need to make sure it's technically accurate and then a quick edit! Susan H. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com