[AccessD] DAO vs ADO

Arthur Fuller fuller.artful at gmail.com
Wed May 16 10:24:36 CDT 2007


Yep. And I also want to see the demonstrations that ADO is slower than DAO.
Unfortunately, these might be hard to provide, because it's irrelevant which
is quicker against an MDB.

On 5/16/07, Charlotte Foust <cfoust at infostatsystems.com> wrote:
>
> You beat me to it, Jim.  I was going to say the same thing.  ADO is
> about manipulating *data* not tables, fields, etc.
>
> Charlotte Foust
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Wednesday, May 16, 2007 8:09 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] DAO vs ADO
>
> Jim,
>
> <<DAO was designed specifically for Jet and ADO around SQL db BE's (3,
> 4).
> >>
>
> In regards to ADO, that's not correct.  ADO was designed as a
> universal data connector lib. It allows you to connect to databases,
> spreadsheets, text files, etc.
>
> Jim.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson
> Sent: Wednesday, May 16, 2007 10: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
>
> --
> 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
>



More information about the AccessD mailing list