ADO/DAO

[JB]

I've been away from Access for awhile and am now returning.

When I left, ADO had arrived on the scene, but most people were still
using DAO.

Could someone point me towards some industry statements and opinions as
to which you should be using for new Access development today, and to
some more enlghtened Usenet arguments about the same topic?

Thanks in advance.

[deko]

> Could someone point me towards some industry statements and opinions as

to which you should be using for new Access development today, and to
some more enlghtened Usenet arguments about the same topic?

AFAIK, ADO doesn't play well with some Automation functions. I use a lot of
Automation in my mdb and also had trouble with using bookmarks in a cloned
Recordset (may have been my error), so I went with DAO. But ADO is the
access method of choice otherwise.

[Larry Linson]

DAO is the natural language of the Jet database engine, and the language of
choice if you are using Jet (the default when you use Access as installed).

DAO and Jet are "in maintenance mode", that is only bugs that affect other
products are expected to be fixed (though Microsoft has, in fact, done some
corrections and enhancements beyond what their policy states since they have
been in maintenance mode.

There was a good deal of "hype" about ADO, which some "took to heart", when
it came out. But the "classic ADO" used and still used in Access is also a
_dead-end_. The successor is already released, ADO.NET, which shares only
part of the name, and is built on a different object model.

There has been recent information from knowledgeable Microsoft insiders
that, at least "in general", it is even better to use MDB-Jet-ODBC-MS SQL
Server (could be any ODBC-compliant server DB, though) than to use the
Access Project, ADP, ADODB, MS SQL Server (and, far as I know, this can ONLY
be used with Microsoft SQL Server, not any other server database).

Thus, I would have to differ, strongly, with deko's advice.

Larry Linson
Microsoft Access MVP
"JB" <gy**********@jmjservices.com> wrote in message
news:1O*******************@fe2.columbus.rr.com...

I've been away from Access for awhile and am now returning.

When I left, ADO had arrived on the scene, but most people were still
using DAO.

Could someone point me towards some industry statements and opinions as
to which you should be using for new Access development today, and to
some more enlghtened Usenet arguments about the same topic?

Thanks in advance.

[deko]

> There was a good deal of "hype" about ADO, which some "took to heart",
when

it came out. But the "classic ADO" used and still used in Access is also a
_dead-end_. The successor is already released, ADO.NET, which shares only
part of the name, and is built on a different object model.

Perhpas what I've been reading is hype. That's okay with me since I've
become pretty comfortable with DAO. I thought ADO was going to replace DAO?

[John Winterbottom]

"JB" <gy**********@jmjservices.com> wrote in message
news:1O*******************@fe2.columbus.rr.com...

I've been away from Access for awhile and am now returning.

When I left, ADO had arrived on the scene, but most people were still
using DAO.

Could someone point me towards some industry statements and opinions as
to which you should be using for new Access development today, and to
some more enlghtened Usenet arguments about the same topic?

As a general rule we use DAO with Jet and ADO with SQL Server, the only two
databases we work with). There are some exceptions. Some of the newer
data-aware ActiveX controls, like the Office Web controls ,use ADO. There is
also some ADO functionality that's not available with DAO AFAIK, like using
streams which are handy when working with XML. Also there are some SQL DDL
statements that can only be executed against Jet databases using an ADO
connection, (I think check and default constraints), - but of course you can
always manipulate field DAO objects directly. If you want to work with
linked tables you must use DAO. There may be more.
Best way is to learn both.

[Douglas J. Steele]

"deko" <de**@hotmail.com> wrote in message
news:ax***************@newssvr27.news.prodigy.com. ..

Could someone point me towards some industry statements and opinions as
to which you should be using for new Access development today, and to
some more enlghtened Usenet arguments about the same topic?
AFAIK, ADO doesn't play well with some Automation functions. I use a lot

of Automation in my mdb and also had trouble with using bookmarks in a cloned
Recordset (may have been my error), so I went with DAO. But ADO is the
access method of choice otherwise.

Not so. While it's true that Access 2000 and 2002 had a reference to ADO
chosen by default, Access 2003 is back to having a default reference to DAO
(although it also has an ADO reference by default, and the ADO reference is
higher in order)

ADO is dead: it's been replaced by ADO.Net, which is considerably different.

DAO, while no longer actively supported, is still the better method if
you're strictly dealing with Jet databases (i.e. MDB files). It was
developed specifically for the Jet Engine, so is more efficient.

Michka wrote a good article entitled "What does DAO have that ADO/ADOx/JRO
do not have (and might never have!)" almost 4 years ago, and, as far as I'm
aware, it's still true today. http://www.trigeminal.com/usenet/usenet025.asp

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

[David W. Fenton]

"deko" <de**@hotmail.com> wrote in
news:xi*******************@newssvr25.news.prodigy. com:

There was a good deal of "hype" about ADO, which some "took to
heart",

when

it came out. But the "classic ADO" used and still used in Access
is also a _dead-end_. The successor is already released, ADO.NET,
which shares only part of the name, and is built on a different
object model.

Perhpas what I've been reading is hype. That's okay with me since
I've become pretty comfortable with DAO. I thought ADO was going
to replace DAO?

It should have been obvious to anyone who stopped to think about it
that ADO could never replace DAO for Jet data stores.

Think about what ADO actually is: like ODBC, it is a database
abstraction layer that sits between client applications and the
database engines. Its advantages is in providing a common interface
to many different database engines. ADO is a great advance over ODBC
in that it can have much more smarts about the back end db engine,
and because it simply incorporates support for a larger superset of
db engine features.

Now, what is DAO?

It's the interface layer between applications and Jet.

Period.

It is abstracted from Jet at a certainly level, in that it's not Jet
itself, but a layer on top of it. But it is not abstracted at any
higher level as ODBC and ADO are, because it only needs to deal with
one db engine.

So, it should be blazingly obvious that DAO will always be superior
for getting to Jet data, since it's Jet's native data access
interface.

In short, MS's promotion of ADO in Access databases by not having a
DAO reference by default never had any technical merit to it. It was
always first and foremost a marketing move. A2K3 is better in that
they include both references, but they still get it backwards by
putting the ADO reference first (so it gets preference).

Lots of ADO is available through the Access.Application object, such
as the one thing that DAO lacks that ADO provides, the UserRoster.
And for things that aren't natively available, one can easily use
late binding for the few things ADO offers that DAO lacks.

Now, if you're not using a Jet back end or you're not using an MDB
but an ADP, then all bets are off.

But Microsoft definitely steered the unwary Access developer down
the garden path with the ADO promotion that had no technical
justification whatsoever.

But I repeat: that was quite obvious at the time to anyone who gave
the subject a few minutes of thought. The later abandonment of ADO
in favor of ADO.NET was just icing on the cake to prove that on the
subject of ADO and Access, Microsoft's marketing is more important
than technical merit or suitability to task.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Larry Linson]

"John Winterbottom" wrote

As a general rule we use DAO with
Jet and ADO with SQL Server, the
only two databases we work with).
There are some exceptions. Some
of the newer data-aware ActiveX
controls, like the Office Web controls,
use ADO. There is also some ADO
functionality that's not available with DAO
AFAIK, like using streams which are
handy when working with XML. Also
there are some SQL DDL statements that
can only be executed against Jet databases
using an ADO connection, (I think check and
default constraints), - but of course you can
always manipulate field DAO objects directly.
If you want to work with linked tables you
must use DAO. There may be more.
Classic ADO is appropriate, certainly, for classic .asp pages. Perhaps it is
just the ticket if you are working with XML from code _in Access_, as well,
but I haven't had occasion to need to do that.

Just about everyone I know who is serious about web-based applications is
now using .NET, for which classic ADO is not appropriate, because the better
(and quite different) ADO.NET has succeeded it there.

If "something else" supplants both DAO and ADO in future versions of Access,
I'd say ADO.NET is a likely candidate. However, I caution that Microsoft has
had many, many "flavor of the current release" access methods over time that
were promoted as "access method of choice for the foreseeable future". My
recollection is that classic ADO was hyped just as much in VB 6 as in Access
2000 and later, but of course classic VB died with VB 6. So, when the time
comes for a successor to DAO and ADO, it could be something else again,
something we haven't even dreamt of yet.

My limited experience working with ADP / ADO against SQL Server is that it
is workable, but often a "pain". Everyone involved with the ADP on which I
am currently doing some work has a separate MDB linked to the same MS SQL
Server tables for quick adhoc queries, etc., against the tables and data.
Best way is to learn both.
I respectfully disagree. My personal approach was, I'll bother learning
another dead-end access method only when I have a client whose work demands
it. I got one, they had swallowed someone's statement that "the only way to
get performance from MS SQL Server with Access is with an ADP", invested in
the conversion, and weren't interested in investing in a re-conversion (they
are expecting that the functionality of the application is going to be
"folded in" to the corporate CRM system within a couple of years -- I am
expecting they are going to need someone applying "band-aids" to the app for
more than a couple of years, based on my observation of typical corporate
CRM implementations).

That would still be my advice to others: don't bother to invest your time
and energy in learning an access method that is already doomed, because its
successor (ADO.NET) is already on the scene, UNLESS you get some paying work
that demands you use it. Then, make a judgement, if appropriate, whether the
pay for the work will be worth the time and effort learning ADO. (But, my
experience so far is that everything except the I/O works so similarly in
ADPs to the way it does in MDBs that the learning curve isn't as steep as
I'd expected. If you have to do much nitty-gritty I/O from code, you are
going to find some differences.)

Larry Linson
Microsoft Access MVP
Best way is to learn both.

[John Winterbottom]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:cX******************@nwrddc02.gnilink.net...

I respectfully disagree. My personal approach was, I'll bother learning
another dead-end access method only when I have a client whose work demands it. I got one, they had swallowed someone's statement that "the only way to get performance from MS SQL Server with Access is with an ADP", invested in the conversion, and weren't interested in investing in a re-conversion (they are expecting that the functionality of the application is going to be
"folded in" to the corporate CRM system within a couple of years -- I am
expecting they are going to need someone applying "band-aids" to the app for more than a couple of years, based on my observation of typical corporate
CRM implementations).

That would still be my advice to others: don't bother to invest your time
and energy in learning an access method that is already doomed, because its successor (ADO.NET) is already on the scene, UNLESS you get some paying work that demands you use it. Then, make a judgement, if appropriate, whether the pay for the work will be worth the time and effort learning ADO. (But, my
experience so far is that everything except the I/O works so similarly in
ADPs to the way it does in MDBs that the learning curve isn't as steep as
I'd expected. If you have to do much nitty-gritty I/O from code, you are
going to find some differences.)

We've been waiting for the "next Access access method" for some time now.
It's easy for you to say "don't learn ADO and don't learn DAO" to others if
you already know at least one of them yourself. But for someone who knows
neither you are effectively saying "don't develop any Access applications
until some unknown time in the future, when ADO.NET may or may not be
incorporated into Access. Perhaps they should just go fishing until then.
Actually, that may not be such a bad idea <g>

And we are not talking about difficult subjects to learn. 95% of my DAO or
ADO code is essentially the same thing; opening recordsets, or executing
queries or stored procedures. I doubt it would take longer than a few days
of solid study to learn both, at least to the point of being functionally
literate. .

[David W. Fenton]

"John Winterbottom" <as******@hotmail.com> wrote in
news:c7************@ID-185006.news.uni-berlin.de:

It's easy for you to say "don't learn ADO and don't learn DAO" to
others

But that's not what Larry said. What he said amounts to "Learn DAO
because you will always need it, and learn ADO if you are ever
required to."

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Larry Linson]

Thanks, David. You said, succinctly, what I _almost_ said in many more
words.

Larry Linson
Microsoft Access MVO

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"John Winterbottom" <as******@hotmail.com> wrote in
news:c7************@ID-185006.news.uni-berlin.de:

It's easy for you to say "don't learn ADO and don't learn DAO" to
others

But that's not what Larry said. What he said amounts to "Learn DAO
because you will always need it, and learn ADO if you are ever
required to."

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc