ADO vs DAO

[Michaël]

Hello,

I have an old Access DB. Was initially developed in Access 97 and thereafter
converted to Access 2003. The Backend DB will soon be included in
SQL server. Can I still use the DAO library or should I switch to ADO?

Tx

regards

Michaël

 

[Stefan Hoffmann]

hi Michael,

I have an old Access DB. Was initially developed in Access 97 and thereafter
converted to Access 2003. The Backend DB will soon be included in
SQL server. Can I still use the DAO library or should I switch to ADO?

As long as you don't have any specific request or need for ADO, stay
with DAO. It means lesser migration effort. Using linked tables makes
the use of any other BE than Access/Jet quite transparent.

ADO is nowadays a deprecated technology (Access 2007).


mfG
--> stefan <--

 

[David W. Fenton]

I have an old Access DB. Was initially developed in Access 97 and
thereafter converted to Access 2003. The Backend DB will soon be
included in SQL server. Can I still use the DAO library or should
I switch to ADO?

Assuming you're going to use ODBC linked tables, DAO is the right
choice, as ODBC uses Jet for handling the data.

If you're going unbound, then, well, I'd ask why you're using Access
at all.

 

[Clifford Bass]

Hi Stefan,

Where do you get that idea?

Clifford Bass

 

[David W. Fenton]

=?Utf-8?B?Q2xpZmZvcmQgQmFzcw==?=

Where do you get that idea?

I don't know where Stefan gets it from, but it's quite clear that
MS's latest technologies have abandoned ADO for ADO.NET (which
shares little other than the name and a few superficial object
types). If you're working in MS's .NET languages, you will see ADO
treated as a legacy technology (most often used with "Classic ASP"
as opposed to ASP.NET).

In Access itself, it's definitely been demoted in importance (for
good reason), in favor of DAO.

If you're using linked tables in Access, I see no reason to ever use
ADO, except for a few things that ADO does that DAO can't, or
doesn't do well.

 

[Clifford Bass]

Hi David,

Thanks for the reply. His statement did surprise me due to the
prevalence of ADO.NET. And due to the fact that in other places DAO has been
declared as deprecated. I do have a memory of reading something about the
COM version of ADO not going to be further developed to work with .NET--the
idea being you should switch to ADO.NET But if I remember correctly the
intent was to continue the ADO COM as a separate item for non-.NET stuff.
However, I don't quite remember where that was that I read it. Access 2007
still supports DAO. Since I don't always keep up-to-date on these constantly
changing technologies I was curious.

Thanks again,

Clifford Bass

 

[Clifford Bass]

Hi Michael,

After Stefan's and David's comments, I did some searching on
Microsoft's web site to get a clearer picture. You may wish to check out
<http://msdn.microsoft.com/en-us/library/ms810810.aspx>.

Clifford Bass

 

[Tony Toews [MVP]]

After Stefan's and David's comments, I did some searching on
Microsoft's web site to get a clearer picture. You may wish to check out
<http://msdn.microsoft.com/en-us/library/ms810810.aspx>.

That muddies things up actually. If you mean that DAO is deprecated.
Strictly speaking that may be true however ACE, maintained by the
Access team, is now the replacement in Access 2007.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Stefan Hoffmann]

hi Clifford,

Where do you get that idea?

Uh, oh, my brain?

Well, deprecated is a strong word, maybe assumptive, but from my point
of view Access 2007 has made a clear shift to DAO back again.


mfG
--> stefan <--

 

[Clifford Bass]

Hi Tony,

It is my understanding that ACE is not a replacement to DAO, but rather
a replacement to Jet. You use DAO / ADO / OLEDB / ODBC, which in turn use
Jet or ACE, to get to the Access databases. I'm afraid I have found another
article that muddies the waters further:
<http://msdn.microsoft.com/en-us/library/cc811599.aspx>.

Clifford Bass

 

[Douglas J. Steele]

Of course, the second article you cite explicitly mentions the "ACE version
of DAO" and talks about the ACE DAO provider. And when you add a reference
to the Microsoft Office 12.0 Access data engine (acedao.dll), you refer to
objects defined in the library as DAO.

I believe that Stefan's point is based on the fact that no more development
is being done on ADO (although I will acknowledge that new providers are
being produced for classic ADO). On the other hand, since all of the DAO
functionality was put into ACE, and then additional functionality was added,
most of us feel that it's legitimate to say that development is still being
done on DAO.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[David W. Fenton]

=?Utf-8?B?Q2xpZmZvcmQgQmFzcw==?=

Thanks for the reply. His statement did surprise me due to
the
prevalence of ADO.NET.

ADO.NET and ADO really don't have anything to do with each other.

And due to the fact that in other places DAO has been
declared as deprecated.

Until it was undeprecated. Microsoft got all hot over ADO around the
release of Access 2000 (1999), and then realized, it seems to me,
that ADO wouldn't work properly with the .NET technologies. But in
the meantime, they'd devoted a lot of effort into persuading Access
developers (for instance) to switch from DAO to ADO. There was never
any clear reason for this for any Access app using linked tables
(Jet back end or ODBC links to a server database), but MS pushed it,
anyway, going so far as to omit the DAO reference entirely in Access
2000 MDBs.

By A2K3, they'd figured out that it was a mistake and with A2K7
they've completely reversed course, what with the new
Access-specific version of Jet.

I do have a memory of reading something about the
COM version of ADO not going to be further developed to work with
.NET--the idea being you should switch to ADO.NET But if I
remember correctly the intent was to continue the ADO COM as a
separate item for non-.NET stuff.

Well, ADO.NET is not usable in Access, so ADO has a legacy use there
(in the handful of cases where ADO offers anything of value to the
Access developer). And ADO is all you can use with classic ASP.

However, I don't quite remember where that was that I read it.
Access 2007 still supports DAO. Since I don't always keep
up-to-date on these constantly changing technologies I was
curious.

Many of us mistrusted MS's motives with the release of Access 2000
and ignored ADO entirely. This has served us rather well as we
haven't wasted any time on a technology that was obsolete almost
immediately after it was incorporated into Access.

 

[David W. Fenton]

That muddies things up actually. If you mean that DAO is
deprecated. Strictly speaking that may be true however ACE,
maintained by the Access team, is now the replacement in Access
2007.

The article clearly predates Access 2007 and thus reflects an
obsolete point of view about what data access interfaces are
"obsolete."

 

[David W. Fenton]

=?Utf-8?B?Q2xpZmZvcmQgQmFzcw==?=

I'm afraid I have found another
article that muddies the waters further:
<http://msdn.microsoft.com/en-us/library/cc811599.aspx>.

That's a bloody stupid article, since it seems to take pains to try
to obfuscate the lineage of the ACE -- it is Jet 5 (or 4.5), nothing
more, nothing less.

The Access development team has always been extremely clear about
this, so I just don't understand why Microsoft would promote such
gibberish. They continue the terrible practice of referring to a
Jet/ACE data store as an Access database, which is very confusing in
an article about how to use the data store without ever using Access
itself.

I'd give that article a 0 on a scale of 1 to 10.

 

[David W. Fenton]

I believe that Stefan's point is based on the fact that no more
development is being done on ADO (although I will acknowledge that
new providers are being produced for classic ADO). On the other
hand, since all of the DAO functionality was put into ACE, and
then additional functionality was added, most of us feel that it's
legitimate to say that development is still being done on DAO.

It's also the case that development is continuing on Jet, even
though it's called ACE.

This is all so incredibly badly done. That article is just terrible,
terrible, terrible. It obfuscates more than it reveals (and it's
self-contradictory, in fact).

One thing I did learn, though, is that there's an ADO version that
can read/write Jet/ACE data stores under 64-bit Windows. I expect
that a 64-bit version of ACE itself (and acedao.dll) will come in
the next version of Access.

 

[Clifford Bass]

Hi David,

While it was originally written in January 2002, it has a revised date
of December 2008.

Here is some more muddying of the waters. Both from Access 2007's help:

1) You can use the [CodeProject or CurrentProject] Connection property
to return a reference to the current ActiveX Data Objects (ADO) Connection
object and its related properties.

2) In order to manipulate the structure of your database and its data
from Visual Basic, you must use Data Access Objects (DAO ). The
[Application.]CurrentDb method....

Clifford Bass

 

[Clifford Bass]

Hi Douglas,

I did notice the stuff about ACE DAO. Hence my comment about thing
being further muddied. It does say that it is 32-bit only. It will be
interesting to see if they produce a 64-bit version.

Clifford Bass

 

[Clifford Bass]

Hi David,

Since they all go through the ACE Engine (acecore.dll) I would presume
that a 64-bit version of that is also in the offing. It really seems like
they will have to provide either 64-bit versions of the ACE DAO in order to
deal with the huge number existing databases. Or provide some well-designed
conversion/upgrade process. It will be interesting to see what is in store.

Clifford Bass

 

[Clifford Bass]

Hi Stefan,

Aaah! The brain. Good souce of ideas! I must say the more I find out
about ADO and DAO and their "future", the more I feel like I am in quicksand.
For example, did you know that there is a .NET DAO provider? I did not
until a minute ago when I checked to see if I could add such a thing into a
VB .NET project. I'm not sure I believe my eyes.

Clifford Bass

 

[Sylvain Lafontaine]

Currently, there is no offering of an ACE Engine running under the 64 bit
mode and don't expect to see one soon (or more exactly, ever). The article
http://msdn.microsoft.com/en-us/library/cc811599.aspx don't say that there
is a 64 bit ACE provider available; it only say that when the ADO version
that comes with DAC 6.0 is running under the 32 bit mode, it can support the
new .accdb format. This ADO version can also run under the 64 bit mode but
when it is doing so, it can only access 64 bit OLEDB providers and at this
moment, there are no 64 bit OLEDB provider available for either JET or ACE
(and quite probably, there will never be one).