DAO vs ADO

[Rich Locus]

Hello:
In the Access 2003 days, I tried using ADO on local tables in my Access
database with disastrous results. As I stepped through macros, some actions
would not even finish before other ones started, causing my queries to be
missing data because the previous step was still executing. I never had that
problem with DAO. What is the recommendation now... is ADO better at making
sure a process is finished before the next begins, or should I stick with DAO
for only internal tables that don't link outside to other databases? Forums
in the past said to stay away from ADO unless you were needing to go outside
your local tables.
Thanks

 

[Banana]

Hello:
In the Access 2003 days, I tried using ADO on local tables in my Access
database with disastrous results. As I stepped through macros, some actions
would not even finish before other ones started, causing my queries to be
missing data because the previous step was still executing. I never had that
problem with DAO. What is the recommendation now... is ADO better at making
sure a process is finished before the next begins, or should I stick with DAO
for only internal tables that don't link outside to other databases? Forums
in the past said to stay away from ADO unless you were needing to go outside
your local tables.
Thanks

FWIW - I tend to do 95% of my data access using DAO, and that included
linked tables from server-RDBMS backends. The other 5% time when I do
use ADO is because I need to use a specific functionality such as
wanting to using a stored procedure as a recordsource for a bound form
without losing updatability.

DAO is under active development while ADO is basically dead (they've
moved onto ADO.NET which only has three letters in common and no more)
and probably will continue to exist for backward compatibility.

 

[Arvin Meyer [MVP]]

The recommendation now is to use DAO. ADO is still supported, but no
additional changes will be made to ADO.

 

[John W. Vinson]

should I stick with DAO
for only internal tables that don't link outside to other databases?

Microsoft now recommends that you stick with DAO... period.

 

[David W. Fenton]

In the Access 2003 days, I tried using ADO on local tables in my
Access database with disastrous results. As I stepped through
macros, some actions would not even finish before other ones
started, causing my queries to be missing data because the
previous step was still executing. I never had that problem with
DAO. What is the recommendation now... is ADO better at making
sure a process is finished before the next begins, or should I
stick with DAO for only internal tables that don't link outside to
other databases? Forums in the past said to stay away from ADO
unless you were needing to go outside your local tables.

Are you sure the problem wasn't that you were using macros?

I really don't think that ADO and DAO called in VBA are going to be
treated differently in terms of whether they execute asynchronously
or not.

Clearly, DAO is the better data access method for Jet/ACE data,
though.

 

[Armen Stein]

The other 5% time when I do
use ADO is because I need to use a specific functionality such as
wanting to using a stored procedure as a recordsource for a bound form
without losing updatability.

I agree. We use ADO only when calling a SQL Server sproc using an ADO
command object. Otherwise it's DAO all the way.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com