ADO vs DAO

[Stefan Hoffmann]

hi Clifford,

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?

No, not really. But I'm not that surprised as there are so much
solutions in the real world that must be supported.
We will see what the future brings


mfG
--> stefan <--

 

[Alex Dybenko]

Hi Clifford,
not sure that we ever get 64bit version of DAO, as they had to move the
whole COM to 64bit first, which looks like a deprecated technology somehow

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

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

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.
[snip]

 

[Clifford Bass]

Hi Sylvain,

My presumption, and that is what it is, is that there will be a 64-bit
version of Office and Access at some point. Which I presume would require a
64-bit version of ACE or some successor. Could be wrong....

Clifford Bass

 

[Clifford Bass]

Hi Alex,

So if, or more likely when, there is a 64-bit version of Access, anyone
who makes the shift from 2007 and earlier may well have to make some major
changes in their code, regardless of whether it is ADO or DAO?

Clifford Bass

 

[Sylvain Lafontaine]

Don't get me wrong: everyone here would be happy to see a 64 bit version of
Office, Access and ACE as well. However, the fact that ACE is now under the
control of the Access team mean one thing: MS is no longer really interested
to see ACE (and JET) to be used outside of Access and Office.

The thing about ADO was not only to be the successor of DAO but also to help
democratize JET outside of the Access & Office world. When used outside of
Access, without the query engine, for example in a web site or as with an
independant application, DAO is not a very efficient interface because it
need to instantiate the DBEngine object - which is another layer above JET -
also because it has not been designed to work in a multi-threads,
high-concurrency environment such as a web server or many modern
applications.

When MS is reverting back to suggest to use DAO instead of ADO for an Access
application, they are not saying that DAO is superior to ADAO, they are
saying that they are not interested anymore to see JET & ACE to be used
outside of Access & Office. Even the mobile edition of Windows use a scaled
down version of SQL-Server instead of JET/ACE. Knowing that, it's probably
safe to say that there will be a long time before we can see a 64 bit
version of Access & Office and when there will be one, probably that there
will be as much difference between the current 32 bit versions of DAO and
VBA and their 64 bit versions that they are between ADO and ADO.NET or VB6
and VB.NET.

 

[David W. Fenton]

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

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

But the content has not been changed to include references to Access
2007, so that date doesn't really mean anything. For that matter,
you can find articles on Access 2 with revision dates that in the
last couple of years. The content hasn't changed in a decade, but
the articles have been moved around, thus changing the revision
dates.

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.

If you're using ADO, that's fine. If you're not, then it isn't of
any use.

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....

I don't see how this muddies the waters, though I would agree if
you're suggesting that #2 is not actually true (since you can use
ADO to execute DDL queries, though it doesn't make much sense to do
that, as DAO can do it, too, and DAO has access to more properties
that Jet's DDL).

 

[David W. Fenton]

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

My presumption, and that is what it is, is that there will be
a 64-bit
version of Office and Access at some point. Which I presume would
require a 64-bit version of ACE or some successor. Could be
wrong....

I simply can't see how MS would invest so much in forking Jet to
give Access it's on private Jet version and then not plan to move
forward with a 64-bit version of the ACE in some future version. If
there is ever a native 64-bit Access, there *must* be a 64-bit ACE.

 

[Clifford Bass]

Hi David,

I don't think, that in this case, the reason the article has a 2008
revision date is because it was moved. It discusses SQL Server 2005,
Office/Access 2007 and 64-bit Windows. And it is the article currently
pointed to off of the main MDAC page.

No, I do not consider #2 to be false. My thinking was that Access 2007
itself is clearly using both DAO and ADO. Which would indicate, to me at
least, that neither technology is considered the only way to go and neither
is considered obsolete.

I guess the summary of it all is that we just have to wait and see what
comes down the Access / Windows road.

Clifford Bass

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

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

But the content has not been changed to include references to Access
2007, so that date doesn't really mean anything. For that matter,
you can find articles on Access 2 with revision dates that in the
last couple of years. The content hasn't changed in a decade, but
the articles have been moved around, thus changing the revision
dates.

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.

If you're using ADO, that's fine. If you're not, then it isn't of
any use.

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....

I don't see how this muddies the waters, though I would agree if
you're suggesting that #2 is not actually true (since you can use
ADO to execute DDL queries, though it doesn't make much sense to do
that, as DAO can do it, too, and DAO has access to more properties
that Jet's DDL).

 

[david]

The interesting question is still, what will happen with the file
system? OLE file system has come and gone. New database
file system never arrived. New transactional file system has
arrived.

Jet uses the Windows Database System, based on DOS 3.x,
but without disk write-back, and with a private security system
because DOS/Windows 3 didn't have a security system.

ACE takes a start at using the new Windows Security System,
but it needs table-level and owner-permission security, which have
never been supported by a MS OS.

If the file system gets record-level security, as would have happened
in the proposed database file system, ACE gets table-level and
owner-permission security. If not, ACE stays with flat-file security.

With only flat-file security, ACE is fundamentally less useful,
is still getting squeezed by improved SQL Server functionality,
and has only a very narrow band of usefulness between Excel
and SQL Server. It's had a reprieve by the death of OLE/COM,
and by the death of FOXPRO, but without improvements to
the OS database primitives that it depends on, it still seems like
a dead end to me: eventually it must be replaced by something
that works better, and if MS doesn't do so, it will be IE6 and
Firefox all over again.

(david)

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

The thing about ADO was not only to be the successor of DAO but
also to help democratize JET outside of the Access & Office world.

This is simply false. ADO was a replacement for ODBC as a generic
data interface layer to *all* data stores (not just Jet, and not
just relational database, but including all kinds of data, even
unstructured data). ADO was a good idea -- ODBC is really long in
the tooth and does not support a whole lot of things found in modern
database engines. But ADO came at the wrong time -- it was
implemented as a COM interface just before the point at which MS was
moving everything to .NET, where COM is an unsafe platform.

The promotion of ADO for Jet had *nothing* to do with Jet or Access.
It was entirely based on MS's agenda to promote a new generic data
interface layer so it could retire DAO as one of the first steps in
phasing out Jet in favor of SQL Server.

Aside form the train wreck with .NET, this never would have worked,
because SQL Server is not a good fit for replacing Jet in a whole
class of Access applications (probably the vast majority of them, in
fact). And ADO turned out not to be a great interface for Jet (and
it even had major issues with SQL Server, which is one of the
reasons MS now promotes MDB/ODBC over ADP/ADO for SQL Server
development).

When used outside of
Access, without the query engine, for example in a web site or as
with an independant application, DAO is not a very efficient
interface because it need to instantiate the DBEngine object -
which is another layer above JET

I don't think that's right at all. DBEngine is not an object outside
of Jet, it *is* Jet. That is, it's the representation of the whole
of the Jet database engine. No matter what method you use for
accessing the Jet database engine, you are going to have some object
that corresponds to DBEngine in your interface (whether you use DAO
or use the Jet DLLs directly).

also because it has not been designed to work in a multi-threads,
high-concurrency environment such as a web server or many modern
applications.

This is one definite drawback of Jet. But I don't very many people
at all who are pro-Jet and pro-DAO have ever suggested that Jet was
a good candidate for a website datastore (except for read-only or
very low user population applications). And I, for one, would never
suggest DAO for the interface -- classic ADO is the obvious best
candidate for classic ASP, and ODBC for most other scripting
languages.

And I recall that Michael Kaplan said that Jet when accessed via ADO
*is* thread-safe, so that avoids one of the problems.

In other words, for websites (if you've determined that your website
fits into the narrow niche of apps for which a Jet data store is
appropriate), ADO is the obvious winner as data interface to Jet.

I don't know of anyone who has ever argued for using DAO in that
environment.

When MS is reverting back to suggest to use DAO instead of ADO for
an Access application, they are not saying that DAO is superior to
ADAO, they are saying that they are not interested anymore to see
JET & ACE to be used outside of Access & Office.

I don't think that's true at all. I think what's obvious is that
classic ADO got abandoned (for complicated reasons that had very
little to do with ADO's actual merit), and ADO.NET couldn't be
ported to Access, so DAO was the obvious choice for future
development as a data interface layer for Jet.

Even the mobile edition of Windows use a scaled
down version of SQL-Server instead of JET/ACE.

Er, that version of "SQL Server" very little in common with
full-fledged SQL Server, according to what I've been told.

Knowing that, it's probably
safe to say that there will be a long time before we can see a 64
bit version of Access & Office and when there will be one,
probably that there will be as much difference between the current
32 bit versions of DAO and VBA and their 64 bit versions that they
are between ADO and ADO.NET or VB6 and VB.NET.

I think your argument is based on a whole lot of incorrect
assessments of the facts and history and is not compelling at all.

MS has provided instructions to developers for how to utilize and
distribute the ACE in the absence of Access 2007. That says to me
that MS is completely interested in people using the ACE outside of
Access.

 

[David W. Fenton]

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

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

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....

I don't see how this muddies the waters, though I would agree if
you're suggesting that #2 is not actually true (since you can use
ADO to execute DDL queries, though it doesn't make much sense to
do that, as DAO can do it, too, and DAO has access to more
properties that Jet's DDL).

No, I do not consider #2 to be false. My thinking was that
Access 2007
itself is clearly using both DAO and ADO.

Eh? Access 2007 does not use ADO unless you add it in. Well, maybe
the Switchboard wizard still uses ADO, but that's not something you
have to use.

Which would indicate, to me at
least, that neither technology is considered the only way to go
and neither is considered obsolete.

Classic ADO is clearly obsolete. Access is the only MS product in
current development that utilizes classic ADO, in fact. And the only
reason for that is that ADO is a good interface for data stores
*other* than Jet. And if you're using linked tables to non-Jet back
ends, DAO is still the best interface, since a linked table by
definition will be provided via Jet.

 

[David W. Fenton]

The interesting question is still, what will happen with the file
system? OLE file system has come and gone. New database
file system never arrived. New transactional file system has
arrived.

I think MS got badly burned with their ambitious plans for WinFS and
will likely leave that alone for a long time to come. I don't see
any inadaquacies in NTFS, myself, and think that the UNIX approach
of keeping individual components as simple as possible is the best.
I don't think a file system should know anything at all about the
internals of the file it is storing, and the kind of thinking that
believes that is helpful is a throwback to the old days of mainframe
non-relational databases.

Or so it seems to me.

Jet uses the Windows Database System, based on DOS 3.x,
but without disk write-back, and with a private security system
because DOS/Windows 3 didn't have a security system.

I don't know what this means. Can you provide some pointers as
explanation? My understanding is that NTFS has zilch to do with the
DOS file system, except that it's compatible for applications that
expect the older file system.

ACE takes a start at using the new Windows Security System,
but it needs table-level and owner-permission security, which have
never been supported by a MS OS.

Again, I don't know what you mean here.

If the file system gets record-level security, as would have
happened in the proposed database file system, ACE gets
table-level and owner-permission security. If not, ACE stays with
flat-file security.

How can any database format that stores more than one table in a
file be internally understandable as records from the file system?
It seems to me that the only way the file system could manage
internal record access could be if each table is in a separate file.
Otherwise, the file system has to know a huge amount about the
internal structure of the data store, and I just don't see what
benefit there is to that.

With only flat-file security,

I don't know what you mean by that.

ACE is fundamentally less useful,
is still getting squeezed by improved SQL Server functionality,
and has only a very narrow band of usefulness between Excel
and SQL Server.

I think this is just not true at all. SQL Server is not even close
to being as easy to install, use and maintain as Access/ACE.

It's had a reprieve by the death of OLE/COM,

How, exactly has the "death of OLE/COM" helped the ACE, which is
COM-based?

and by the death of FOXPRO, but without improvements to
the OS database primitives that it depends on, it still seems like
a dead end to me: eventually it must be replaced by something
that works better, and if MS doesn't do so, it will be IE6 and
Firefox all over again.

I think what you've written is a bunch incoherent lunacy. It makes
no sense at any level whatsoever.

 

[Clifford Bass]

Hi David,

I could be wrong on Access 2007 using ADO. It may just be providing
for the use of ADO through the CurrentProject.Connection and
CodeProject.Connection objects--not actually using them. However, you can
use those without adding any reference to any ADO library.

Clifford Bass

 

[Sylvain Lafontaine]

Well, if you really want to split the hairs, your statement is also false.
ADO is not a replacement for ODBC because the ODBC are providers and ADO can
use these providers as well with the help of the MSDASQL specialized
provider. If you want to put the fine prints like any good lawyers, with
will running in round for years to come.

If you take a look at it, the set ADO + OLEDB Providers is not really
different from the set DAO + ODBC providers and is only one small step
higher in term of level of abstraction for accessing databases and these
other things like Excel that can show an interface similar to a database.
Some people here are talking about DAO and ADO like if the difference
between them was like the difference between the day and the night.

The big hurdle with ADO is that it's not really object oriented (OO); it's
more like a couch of paint of OO around a core of functional (or structural)
programming; which is a big hurdler in today world of programming were
everything seem to be geared towar becoming OO programming. However, the
biggest hurdle with ADO - and of COM/DCOM as well - is its lack of intrinsic
security. This is in contract to .NET which has been build up from the
ground using OO programming and with integrated security. (Many people here
will argue that DAO is object oriented. Personally, I thinkg that it will
be more exact to say that DAO has one thin layer of OO paint around a
functional core and that ADO have two.)

This absence of intrinsic security might also explains why there is not 64
bit ODBC driver for JET and probably that there will never be one. MS
simply doesn't seem interested to invest money for porting to the 64 bit
world something which is instrinsicaly insecure and will remain so whatever
you try to do with it.

 

[david]

I think the revised date just means they have checked it for
broken links -- there is some clearly (2+2=5) wrong stuff
there that has similar revision history.

(david)

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

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

 

[david]

explanation? My understanding is that NTFS has zilch to do with

DOS file system, except that it's compatible for applications that

http://support.microsoft.com/kb/101191

"share" is the database api used by Jet.

(david)

 

[Clifford Bass]

Hi David,

But there is reference to Access 2007. So there have been some changes
to the text in the past two years. I guess my point is that one should not
just dismiss the contents out-of-hand. However, one can take them with a
grain (cube) of salt if one wishes.

Clifford Bass

 

[David W. Fenton]

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

I could be wrong on Access 2007 using ADO. It may just be
providing
for the use of ADO through the CurrentProject.Connection and
CodeProject.Connection objects--not actually using them. However,
you can use those without adding any reference to any ADO library.

You can use CurrentDB without a reference to DAO, because it's a
member of the top-level Application object.

CurrentProject and CodeProject are also members of the top-level
Application object, and even though they return ADO connections,
they don't require an ADO reference.

I don't quite know why you'd use them when not already using ADO,
but they are definitely there.

 

[Jamie Collins]

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.

It's kinda comforting to see the old guard still here and still claiming
that ADO is 'deprecated', 'obsolete', 'abandoned', 'dead' and other such
nonsense

Tell me, does ACEDAO have support for row level locking, CHECK constraints
and fixed-width text data types? No? Then we still need ADO to do these
things in VBA via the OLE DB providers.

ADO.NET is for languages that target the .NET framework and mentioning it
here is a bit distracting. ADO.NET supports Jet via OLEDB. So whenever anyone
mentions ADO.NET in the context of ACE/Jet, just think OLE DB.

Take a look at this article:

Developing Access 2007 Solutions with Native C or C++
http://msdn.microsoft.com/en-us/library/cc811599.aspx

It even has a section entitled, "Deprecated Data Access Methods". Do you see
ADO in the list? No, it's not in the Deprecated section. In fact, it gets a
section all of its own and one significant usage mentioned here is that ADO
can be used with ACE on 64 bit Windows.

P.S. don't you think it's time we all moved on and start embracing ACE as
being the future and stop calling it 'Jet'? Jet is now deprecated, has been
for years, and ACE is the last hope for Access and DAO. Make the most of it,
people!

Jamie.

--

 

[Sylvain Lafontaine]

It's kinda comforting to see the old guard still here and still claiming
that ADO is 'deprecated', 'obsolete', 'abandoned', 'dead' and other such
nonsense

Tell me, does ACEDAO have support for row level locking, CHECK constraints
and fixed-width text data types? No? Then we still need ADO to do these
things in VBA via the OLE DB providers.

ADO.NET is for languages that target the .NET framework and mentioning it
here is a bit distracting. ADO.NET supports Jet via OLEDB. So whenever
anyone
mentions ADO.NET in the context of ACE/Jet, just think OLE DB.

Take a look at this article:

Developing Access 2007 Solutions with Native C or C++
http://msdn.microsoft.com/en-us/library/cc811599.aspx

It even has a section entitled, "Deprecated Data Access Methods". Do you
see
ADO in the list? No, it's not in the Deprecated section. In fact, it gets
a
section all of its own and one significant usage mentioned here is that
ADO
can be used with ACE on 64 bit Windows.

I'm sorry to say that but ADO cannot be used with ACE on 64 bit Windows
(under the native 64 bit API instead of the emulated 32 bit mode called
WOW64). What the section that you are referencing is saying two things: 1-
that ADO can be used with ACE and also 2- that ADO can be used under the 64
bit mode; however, it don't say that ADO can be used with ACE *and* the 64
bit mode at the same time.

This is the same confusing language that we have seen about the 64 bit
version of the MSDASQL provider.

P.S. don't you think it's time we all moved on and start embracing ACE as
being the future and stop calling it 'Jet'? Jet is now deprecated, has
been
for years, and ACE is the last hope for Access and DAO. Make the most of
it,
people!

Jamie.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)