ADO vs DAO

[Mike]

I have an Access XP database that contains a lot of code
that accesses data via DAO. Would there be any benefits
in converting the code to use ADO instead, i.e. speed,
stability?

Thanks.

 

[Bruce M. Thompson]

I have an Access XP database that contains a lot of code

that accesses data via DAO. Would there be any benefits
in converting the code to use ADO instead, i.e. speed,
stability?

If you are only accessing an MDB file using Jet (the default), there is *no*
compelling reason to change anything. DAO would still be the preferred method
for accessing your data (there's no difference in stability). I still program
exclusively in DAO.

 

[Alick [MSFT]]

Hi Mike,

As Bruce mentioned, DAO makes much more efficient use to the Microsoft Jet
database engine than ADO and many operations are faster under DAO.

The DAO object model is designed specifically for the Microsoft Jet
database engine. Jet itself incorporates ISAM and ODBC connectivity and
makes the back-end providers look as much like the native Jet engine as
possible, though this comes at the expense of performance. DAO also has an
ODBCDirect mode that allows it to host RDO objects and access ODBC
datasources in a very efficient manner.

The ADO object model was designed for OLE DB providers and is a much
simpler and more flexible object model than DAO. However, its architectural
design poses some problems when using the Microsoft Jet OLE DB provider,
and it is more limited than DAO in Jet functionality it supports.

The advantage of using ADO in lieu of DAO is when you request data through
an OLE DB provider that does not have a corresponding ODBC Driver.

Using ADO also allows you to access a greater variety of datastores than
DAO since ODBC accesses relational data only. OLE DB exposes non-relational
data to ADO, allowing you to access and manipulate more data sources using
ADO/OLE DB than possible with DAO/ODBC.

Please feel free to reply to the threads if you have any concerns or
questions.


Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| Content-Class: urn:content-classes:message
| From: "Mike" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.modulesdaovba.ado
|
| I have an Access XP database that contains a lot of code
| that accesses data via DAO. Would there be any benefits
| in converting the code to use ADO instead, i.e. speed,
| stability?
|
| Thanks.
|
|

 

[Bruce M. Thompson]

Thanks, Alick, for that *excellent* explanation of the advantages in each of
these technologies.

 

[david epsom dot com dot au]

There is no important "OLE DB provider that does not have a
corresponding ODBC Driver", and for the unimportant ones you can
use a generic OLE DB ODBC Driver. And in any case,

/The advantage of using DAO in lieu of ADO is when you request
data through an ODBC Driver that does not have a corresponding
OLE DB provider/ /ODBC exposes [relational and non relational]
data to DAO, allowing you to access and manipulate more data
sources using DAO/ODBC than possible with ADO/OLE DB/

The actual advantage of using ADO instead of DAO is

"The supplier wants you to"

An important subset of that reason is

"DAO is not being updated or extended"

And within that reason

"DAO already does not support the full set of SQL now provided by JET or
SQL Server"
"DAO already has poorer support for SQL Server than ADO has"

ODBC was an Open Standard: OLE is a proprietary MS standard used widely by
MS. It makes sense for MS to standardise on one connectivity standard, and
it makes sense for them to use the standard they designed, are comfortable
with, control, and have most experience with.

It also makes sense for MS to gradually drop DAO, which they have replaced
with ADO, and ADO.NET, rather than trying to support lots of data objects.

These are not reasons for a DEVELOPER to use ADO instead of DAO, so MS has a
story (which they may even believe) about 'non-relational data' and 'OLE DB
Providers'. However it would be foolish to believe that the story has any
relationship to objective reality.

For a developer, the choice is between an evolving and supported standard
(ADO) with better SQL Server support, but which is more complex and slower,
or a closed standard that is primarily maintained for backwards
compatibility (DAO), but is faster, simpler, and has a different set of
features.

As a developer myself, my recommendation is to go with the flow, and use the
technology that the supplier has the deeper commitment to whenever possible:
ADO.NET. And to avoid technologies that the supplier has no commitment to:
RDO. I use DAO on heritage projects: the weaknesses of ADO in dealing with
ordinary data sources are not sufficient to prevent me from using ADO in
ordinary development.

(david)

 

[Bruce M. Thompson]

It also makes sense for MS to gradually drop DAO, which they have replaced

with ADO, and ADO.NET ...

Not "replaced". DAO is still alive and supported and remains the best choice
when implementing JET.

I do not work in any "enterprise" environment where there are scores (or
hundreds or thousands) of users editing and adding data or the storage needs
enter the multi-gigabyte range - I service strictly small businesses and
non-profit organizations, so DAO and JET remain my technology of choice.

 

[Victor Cheung]

I vote DAO.. I find it faster..

Not "replaced". DAO is still alive and supported and remains the best choice
when implementing JET.

I do not work in any "enterprise" environment where there are scores (or
hundreds or thousands) of users editing and adding data or the storage needs
enter the multi-gigabyte range - I service strictly small businesses and
non-profit organizations, so DAO and JET remain my technology of choice.

 

[Greg Kraushaar]

I don't know about XP, but earlier versions saw a marked performance,
Stability etc DECREASE when running on ADO.

DAO is mature technology & written with the MDB structure in mind. ADO
was built to try to convince the community to move to SQL Server. It
failed.

(My opinion only)
Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)

 

[Greg Kraushaar]

I do not work in any "enterprise" environment where there are scores (or

hundreds or thousands) of users editing and adding data or the storage needs
enter the multi-gigabyte range -

I have done so, and have done it successfully with MDBs and DAO. I
admit I did have to jump through a few hoops for a project that size,
and still keep performance and reliability, but it was still faster
than the learning curve for ADO into DB2.
Several years later,the customer is still using it to support several
hundred users in half a dozen countries.

The management keep claiming that SAP is going to replace it real soon
now, but the SAP developers cannot keep up with the speed of business
changes. They have a lead time of six months minimum, and the business
changes every quarter. I don't maintain it any more. I prefer small
business too, but the current developers are still ahead of the game.

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)

 

[Bruce M. Thompson]

I do not work in any "enterprise" environment where there are scores (or

I have done so, and have done it successfully with MDBs and DAO. I
admit I did have to jump through a few hoops for a project that size,
and still keep performance and reliability, but it was still faster
than the learning curve for ADO into DB2.
Several years later,the customer is still using it to support several
hundred users in half a dozen countries.

Thanks for your input, Greg. It's success stories such as these that keep DAO
going.