William Hindman
wdhindman at dejpolsystems.com
Wed May 16 18:03:50 CDT 2007
"it's irrelevant which is quicker against an MDB." Arthur ...yep, you were right Arthur ...you're not to be taken seriously :)))) William Hindman ----- Original Message ----- From: "Arthur Fuller" <fuller.artful at gmail.com> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Wednesday, May 16, 2007 11:24 AM Subject: Re: [AccessD] DAO vs ADO > 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 >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >