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 >