Charlotte Foust
cfoust at infostatsystems.com
Wed May 16 18:33:16 CDT 2007
What version of Access are you using, William? Microsoft in their wisdom built some of both DAO AND ADO into Access 2003, so you can do some things without referencing the libraries directly. Not true of earlier versions, and yes, I'd say DAO.net will be very, very far behind because it's jet specific, which means no managed code. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman Sent: Wednesday, May 16, 2007 4:16 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] DAO vs ADO ...other than some rarely used data typing and access to the jet user roster that MS stuck in Jet4 without updating DAO, I'd be interested in exactly what ADO can do that can't be done with DAO when an mdb is the be ...actually, I can even do the data typing and roster access from DAO without ever referencing the ADO libraries. ...much as it pains the ADO advocates, the simple fact is that MS tried to deprecate DAO in favor of ADO and failed ...ADO is now a terminus object model and DAO has been renamed and given new life as the focus of A2K7 ...anything new ADO wise will be in ADO.Net, not ADO. ...can DAO.Net (or its evil twin) be far behind? William Hindman ----- Original Message ----- From: "jwcolby" <jwcolby at colbyconsulting.com> To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> Sent: Wednesday, May 16, 2007 11:28 AM Subject: Re: [AccessD] DAO vs ADO > >but since it can do things that ADO cannot this is irrelevant. > > Should be > > but since it can do things that DAO cannot this is irrelevant. > > 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 jwcolby > Sent: Wednesday, May 16, 2007 11:20 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] DAO vs ADO > > Yes, except... > >>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). > > 1) When using an MDB, DAO is usually much faster. > 2) ...the reverse is true for ADO is a little vague. > > ADO is never "much faster", but since it can do things that ADO cannot > this > is irrelevant. As for a "better" object model, it is better stated that > it > has an object model optimized for abstracted data containers (if such a > thing is possible). It is tough to say it is optimized for anything since > it is designed to handle whatever is thrown at it. > > Other than that, it is a good summarization. Perhaps it could be placed > up > on our web site? > > 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 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