For years now MS has suggested using ADO as DAO is obsolete. However, I
still see MVP's giving code solutions using DAO. Not being critical here,
simply curious to know why ADO solutions are not given more frequently. Any
observations y'all like share?

Regards, Jack Hudson

Jack Hudson wrote:
> For years now MS has suggested using ADO as DAO is obsolete. However, I still
> see MVP's giving code solutions using DAO. Not
> being critical here, simply curious to know why ADO solutions are not
> given more frequently. Any observations y'all like share?
>
> Regards, Jack Hudson

DAO was never obsolete just not the shiny new thing on the shelf. The
overwhelming consensus that I see in these groups is that ADO offers no
advantage when working against Jet data and has some disadvantages. Since most
posters in these groups are working against Jet data it makes sense to continue
posting DAO solutions.

I for one almost never use Jet tables but still use DAO because it works for
everything I need and have never felt any reason to learn something new.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

"Jack Hudson" <(E-Mail Removed)> wrote:

>For years now MS has suggested using ADO as DAO is obsolete. However, I
>still see MVP's giving code solutions using DAO. Not being critical here,
>simply curious to know why ADO solutions are not given more frequently. Any
>observations y'all like share?

We're old farts?

What Rick said is generally true. I'm more comfortable in DAO as
that's what I've been using for a long time. And ADO requires two
lines of code not one.

The only thing about ADO that is useful to me is that it will allow
you to reset the autonumber seed with DAO won't. And that is pretty
minor and seldom used.

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

"Jack Hudson" <(E-Mail Removed)> wrote in message
news:jJXOh.10269$(E-Mail Removed)...
> For years now MS has suggested using ADO as DAO is obsolete. However, I
> still see MVP's giving code solutions using DAO. Not being critical here,
> simply curious to know why ADO solutions are not given more frequently.
> Any observations y'all like share?
>
> Regards, Jack Hudson
>

ADO was a newer object model, and is not really tied to JET.

In some ways, the ADO object model is a bit cleaner then DAO. (the two areas
that I notice the most is are when you do a update in ADO, the record
pointer does not move...in DAO it does, and that tends to be a bit of pain
(actually quite a bit of pain). Also, in ADO if you move to the next record,
you don't have to issue a update, were as in DAO you do. And, if memory
servides me correct, the "edit" method is optional in ADO. These about the
only two, or three real things as to why I think ADO is a bit cleaner then
DAO.

However, it is ADO that really be put out to pasture, and we are now being
told to use ado.net.

However, for ms-access, we continue to receive new versions of JET..and
access 2007 has new data types. So, in effect, DAO continues to get
enhancements and new features (if you consider DAO as part of the jet
engine). ADO it not receiving any such enhancements.


In access 2007, the JET odbc direct ability was removed. So, this means that
JET is still being changed, and this is one example of a feature that has
been put to pasture. This issue does favour using ADO in this case (as then
you can connect to sql server, and by-pass jet. However, jet + odbc via dao
still functions as usual, and pass-though queries will still by-pass jet,
and thus you get much the same result.

In addtion, workgroup secirty for access 2007 has been removed. So, in
effect, this is two areas which reduce the advantage of using DAO over that
of ADO.

However, in both cases, dao, or ado hitting sql server perform about the
same.

so, in a funny way, about 7 years ago, we were told to use ADO, but we all
just continued on using DAO. As time passed, lo, and behold...today, DAO is
still strong, and in the ms-access community DAO is stronger then is ADO,
and DAO still enjoys good support from Microsoft.

So, it likely more developers are dropping ADO and moving to ADO.NET then we
who continue to use DAO.

If your developing the application in mind with eventual moving to sql
server, then there is some advantages to using ADO in your application. And,
we have a good number of vb6 developers who now jumped into ms-access, and
they are also more familiar with ADO. So, what is really nice is that both
dao, and ado enjoy widespread support in ms-access. For the most part those
being told to throw out, and stop using DAO were actually a bit
miss-informed.

It is not really much a problem or issue, DAO tends to be the choice when
building ms-access application (because that is the way we always done
it..and it tends to be a bit less code in a good number of cases -- sans my
two examples).

You certainly can use ADO also. ADO is another layer on top, and does
abstract out the database more then DAO. So, if you eventually plan to
change the database engine, ADO is still a better choice.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

On Mar 30, 12:19 am, "Rick Brandt" <rickbran...@hotmail.com> wrote:
> > For years now MS has suggested usingADOas DAO is obsolete. However, I still
> > see MVP's giving code solutions using DAO. Not
> > being critical here, simply curious to know whyADOsolutions are not
> > given more frequently.
>
> DAO was never obsolete

That certainly true but MSFT did give the impression that DAO was no
longer MSFT's data access component of first choice e.g.

Intermediate Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Using ADO vs. DAO
"In previous versions of Access, Data Access Objects (DAO) was the
primary data access method. That has now changed. Although DAO is
still supported, the new way to access data is with ADO."

> The
> overwhelming consensus that I see in these groups is that ADO offers no
> advantage when working against Jet data and has some disadvantages. Since most
> posters in these groups are working against Jet data it makes sense to continue
> posting DAO solutions.

There is an overwhelming preference for DAO, which is surely the
simple explanation as to why we see more DAO solutions posted i.e. it
is in proportion to greater code/knowledge base. Also note that we see
many MVP wannabes in these groups and MVPs prefer DAO, imitation being
the sincerest form of flattery; put another way: you may prefer ADO
yourself but exclusively posting ADO is not the path to glory here.
Take, for example, the oft requested task of seeding an incrementing
autonumber: there is no shame is posting the DAO solution (create
column with autonumber property, insert (seed - 1) rows, delete all
rows) and the straightforward and elegant ADO solution (create a
column with the autonumber property seeded appropriately) is rarely
seen.

However, "overwhelming consensus that... ADO offers no advantage" must
be a MMM (misleading statement, misstatement or misunderstanding)
because there are some functionality accessible to ADO and not DAO;
willingness to accept Hobson's Choice is a huge advantage <g>. It may
be a popular misconception that ADO offers no advantage but 'popular'
does not equate with 'truth'!

> I for one almost never use Jet tables but still use DAO because it works for
> everything I need and have never felt any reason to learn something new.

I don't think many people would recommend DAO for non-Jet tables. From
personal experience DAO+Oracle was disastrous and mapping data types
between DAO and SQL Server couldn't be made to fit

Jamie.

--

On Mar 30, 12:32 am, "Tony Toews [MVP]" <tto...@telusplanet.net>
wrote:
> ADO requires two
> lines of code not one.
>
> The only thing about ADO that is useful to me is that it will allow
> you to reset the autonumber seed with DAO won't.

CurrentProject.Connection.Execute "ALTER TABLE YourTable ALTER
autonumber_col INTEGER IDENTITY(1, 1);"

That's _one_ line of code by my reckoning.

Jamie.

--

On Mar 30, 3:23 am, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
Albert,
Yours is the best reflection on ADO vs DAO I've seen in the Access
2007 era and sincerely thank you for it.

A couple of points:

> it is ADO that really be put out to pasture, and we are now being
> told to use ado.net.
>
> it likely more developers are dropping ADO and moving to ADO.NET then we
> who continue to use DAO.

You seem to be using 'we' to mean 'developers across all MS platforms'
e.g. Visual Studio and Office. I think you comments about ado.net are
misleading in the context of ms-access because AFAIK there is no
practical way of using ADO.NET in an ms-access project whereas ADO
classic remains a viable option.

> access 2007 has new data types. So, in effect, DAO continues to get
> enhancements and new features (if you consider DAO as part of the jet
> engine) .ADO it not receiving any such enhancements.

Out of interest, are you recommending the use of the Access2007 multi-
value data types in a standard ms-access project (i.e. other than
sharepoint integration etc)? I've read your opinions on pick, which of
course has the required predicates, but I wondered where you stand on
the Access/Jet implementation of multi-value types. I don't see anyone
in these groups evangelizing or even advocating them. It is relevant
to this thread because you are using them to add to the case for DAO.

Another thing to note is that DAO has still not got enhancements for
some of the Jet 4.0 features (e.g. creating CHECK constraints); ADO
(via the OLE DB providers) has received such enhancements.

Jamie.

--

"Jamie Collins" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 30, 12:19 am, "Rick Brandt" <rickbran...@hotmail.com> wrote:
>> > For years now MS has suggested usingADOas DAO is obsolete. However, I
>> > still
>> > see MVP's giving code solutions using DAO. Not
>> > being critical here, simply curious to know whyADOsolutions are not
>> > given more frequently.
>>
>> The
>> overwhelming consensus that I see in these groups is that ADO offers no
>> advantage when working against Jet data and has some disadvantages.
>> Since most
>> posters in these groups are working against Jet data it makes sense to
>> continue
>> posting DAO solutions.
>
> However, "overwhelming consensus that... ADO offers no advantage" must
> be a MMM (misleading statement, misstatement or misunderstanding)
> because there are some functionality accessible to ADO and not DAO;
> willingness to accept Hobson's Choice is a huge advantage <g>. It may
> be a popular misconception that ADO offers no advantage but 'popular'
> does not equate with 'truth'!

While it's true that there's functionality available only in ADO, there's
also functionality available only in DAO.

See MichKa's article "What does DAO have that ADO/ADOx/JRO do not have (and
might never have!)" at http://www.trigeminal.com/usenet/usenet025.asp

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

On Mar 30, 11:54 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> While it's true that there's functionality available only in ADO, there's
> also functionality available only in DAO.

Absolutely correct and thanks for pointing out my omission...

> See MichKa's article "What does DAO have that ADO/ADOx/JRO do not have (and
> might never have!)" athttp://www.trigeminal.com/usenet/usenet025.asp

....but from a quick glance, not only is that article obviously biased,
it contains a number of MMMs (misleading statement, misstatement or
misunderstanding). How many problems must I highlight for you to
desist in linking to this article <g>?

"Creating users and groups in a way that allows you to recreate them
in case an MDW file is lost...fails in ADO which does not allow you to
specify PIDs" is a clear misstatement: unlike DAO, ADO may be used to
execute Jet SQL DCL. See:

Advanced Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx

[Quote]

To use the PID with a CREATE USER statement, list the PID value after
the password.

Copy Code
CREATE USER Tim pwd H3sJaZ9k2m

[Unquote]

"Securing Access project objects such as forms, reports, or macros...
fails in ADOx because it does not properly map the expected constants
for permissions to execute, read changes, and write changes to these
object types" could be a misstatement. There is certainly the *syntax*
to do this: from the above article:

[Quote]

The keywords used with the GRANT and REVOKE statements are as
follows:
....
The CONTAINER keyword is used to specify any of the container objects,
such as tables, relationships, forms, or reports.

[Unquote]

but whether it works via ADO I have not tested.

"ADO...has no AllPermissions property and requires you to separately
enumerate the user and all of their groups" is misleading because,
again, the ADO solution is to execute Jet SQL DCL. From the above
article again:

[Quote]

Using PUBLIC will set permissions for the default Users group account
so that everyone will get the assigned privileges
....
ALL PRIVILEGES: Allows a user all permissions, including
administrative, on a specified table, object, container, or database.

[Unquote]

This applies to setting permissions; I don't know what "Retrieving
implicit permissions" means in context.

"Capability to set and change Jet options without making registry
changes (works in DAO through DBEngine.GetOption and
DBEngine.SetOption, fails in ADO, which has no such analogue)" A clear
misunderstanding. There are direct equivalents using settings in the
OLE DB provider. See:

ADO Provider Properties and Settings
Microsoft Jet 4.0 Provider Properties
http://msdn2.microsoft.com/en-us/library/aa140022(office.10).aspx

Table 5. Provider-specific ADO Connection Object Session properties

>From a quick glance the DAO.SetOptionEnum equivalents are found here;
the first three certainly are covered by Jet OLEDB:Shared Async Delay,
Jet OLEDB:Flush Transaction Timeout and Jet OLEDB:Implicit Commit Sync
respectively.

I'm hope four is enough because I'm out of time!

I think some are simply not a fair comparison:

"Allowing a separate Jet session to run using a special object in the
object model" Erm, why is that relevant to database access components
e.g. why isn't opening a second connection acceptable in context?

"Allowing the creation/change/deletion of any and all properties
through the JPM" Most of the properties seem to relate to the Access
user interface rather than the Jet layer i.e. these properties were
omitted _by design_.

In the interest of fairness, I think the point about locking (the
article labours the point with three items to make essentially the
same point) is actually worse than implied e.g. pessimistic locking of
objects fails through ADO.

Jamie.

--

In news:(E-Mail Removed),
Jamie Collins <(E-Mail Removed)> wrote:
> On Mar 30, 11:54 am, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>> While it's true that there's functionality available only in ADO,
>> there's also functionality available only in DAO.
>
> Absolutely correct and thanks for pointing out my omission...
>
>> See MichKa's article "What does DAO have that ADO/ADOx/JRO do not
>> have (and might never have!)"
>> athttp://www.trigeminal.com/usenet/usenet025.asp
>
> ...but from a quick glance, not only is that article obviously biased,
> it contains a number of MMMs (misleading statement, misstatement or
> misunderstanding). How many problems must I highlight for you to
> desist in linking to this article <g>?
[...]

Very interesting post, Jamie. However, most of your rebuttals involve
executing Jet SQL statements via ADO, contrasted with manipulating
programming objects via DAO. I'm not convinced this is a fair
comparison. Any process that lets you pass SQL statements to the
database engine will give you access to those features of the database
engine that are supported in that engine's dialect of SQL. That's
different, though, from an object library that provides classes,
methods, and properties to manipulate those features. Both DAO and ADO
can execute SQL statements to accomplish many of the tasks the article
refers to -- at least, reading your post I am now aware that they can --
but DAO provides Jet-specific programming objects to accomplish those
same ends. To a SQL-oriented person, that may be of no importance, but
to a code-oriented person it can be easier to create objects and
manipulate properties than to look up uncommon SQL syntax.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)