[AccessD] DAO vs ADO

Let's do this -- I can't keep up with the conversation and hope to make
every change without missing something. 

If those who contributed are willing -- would you please look over the
document (follows), make necessary changes, and send them to me privately.
I'll compile them and resubmit the edited doc to the list for a final check.
Are you guys agreeable to that? 

Susan H. 

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

Susan H.