Charlotte Foust
cfoust at infostatsystems.com
Wed May 16 15:57:30 CDT 2007
That isn't opening a querydef as understood in DAO, and the ADOX library
is a separate criter from ADODB. You need a separate reference for ADOX
before you can deal with catalogs, and there are still many things that
are just not worth trying in ADO.
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 1:39 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] DAO vs ADO corrected summarization
Hi John:
Actually ADO can open a query def:
Dim cat as New ADOX.Catalogue
Dim cmd as ADODB.Command
...
Set cmd = cat.Procedures("MyQuery").Command
cmd.Parameters("Forms!MyForm!MyField1")=#6/23/2007#
...
There are a number of other ways to hook into various
controls/catalogues within the FE DB but hardly worth the effort as the
defaults work so well.
I have never attributed DAO to its internal functions related to the
Presentation section of the Access app.; especially when not using the
DAO database connections or the MDB database but in that case DAO is MS
Access as it is the FE base.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, May 16, 2007 10:54 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] DAO vs ADO corrected summarization
>There are no operations that DAO can do that ADO can not duplicate but
>the
reverse is not true.
There are no DATA operations that DAO can do...
ADO cannot open a query def, manipulate a control on a form in design
view, see a property of a form, open the VBE editor etc. There are LOTS
of operations that DAO can do that ADO cannot, all having to do with
manipulating objects inside of an MDB container. These are of course
not DATA operations (per se), they do not have to do with tables /
records / fields.
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 Lawrence
Sent: Wednesday, May 16, 2007 1:38 PM
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
--
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