[AccessD] DAO vs ADO

>>When reviewing the References list, how does one know which reference
uses ADO vs DAO? 
You set a reference to the ActiveX Data Objects library to use ADO, just
as you set a reference to the Microsoft DAO library to use DAO.

Charlotte Foust  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson
Sent: Wednesday, May 16, 2007 7:58 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] DAO vs ADO

Thanks to everyone who replied.
I'm sometimes a little bone-headed and it takes me sometime to digest
what has been said.  I combined all the comments in the hope I would
understand the concepts better.  Below is what I gleaned out of the
correspondence.  I do have one more question... When reviewing the
References list, how does one know which reference uses ADO vs DAO? 

Thanks,

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 around SQL db BE's
(3, 4).  When using an mdb/Jet BE's DAO is usually much faster and has a
better object model ...the reverse is true for ADO (3).  

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
3.  Use ADO when:
	a. ODBC is used to an external data store (SQL Server, MDB,
Excel, etc.)
	b. working with an ADP


1.  Charlotte Foust
2.  John Colby
3.  William Hindman
4.  Drew Wutka

Jim
jhewson at karta.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com