ADO vs. DAO

[RoyVidar]

Ah, I wasn't very clear. The execute takes one line but the
openrecordset requires two lines.

Tony

<grin>

From the help file - Execute Method (ADO Connection):

"For a row-returning command string:

Set recordset = connection.Execute(CommandText, RecordsAffected,
Options)

Return value
Returns a Recordset object reference."

defaults to forwardonly, readonly, though.

dim rs as adodb.recordset
dim s as string
s = "select * from mytable"

set rs = currentproject.connection.execute(s, , adcmdtext)

debug.print rs.getstring

For other cursor/locktype, I think, one would need to either rely upon
implicit instantiation (shudder), or use more than one line.

dim rs as new adodb.recordset
dim s as string
s = "select * from mytable"

rs.open s, currentproject.connection, adopenkeyset, adlockoptimistic,
adcmdtext

debug.print rs.getstring

(air code)

 

[(PeteCresswell)]

Per Jack Hudson:

Not being critical here,
simply curious to know why ADO solutions are not given more frequently. Any
observations y'all like share?

I've dabbled in ADO - especially when going against SQL Server or Oracle and
retrieving multiple recordsets in one server hit.

But my experience is that writing DAO takes a *lot* less code and once I read
that DAO works better for JET back ends (which most of my projects are), I
decided to stick with DAO unless I could come up with a reason to use ADO (as in
SQL Server-based apps).

 

[MikeR]

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

In my experience, DAO is WAAAAAAY faster than ADO for Access dbs.
Mike

 

[(PeteCresswell)]

Per MikeR:

In my experience, DAO is WAAAAAAY faster than ADO for Access dbs.

If it is as it seems - an extra layer on top of DAO - it seems almost
inevitable.

(I'm guessing that the thingie that we supply to ADO that lets it read DAO
really just lets ADO translate it's requests into DAO-speak and relay them to
JET).

 

[David W. Fenton]

(I'm guessing that the thingie that we supply to ADO that lets it
read DAO really just lets ADO translate it's requests into
DAO-speak and relay them to JET).

I don't believe so. I think ADO communicates with Jet directly. But
Access communicates with ADO through an intermediate layer, the name
of which I've forgotten.

 

[Jamie Collins]

You're confusing the ACCDB version of DAO with the Jet 4 version of
DAO.

I am not. I think the mention of 'Jet 4.0' has confused you so allow
me to restate my point in the context of Access 2007 and ACCDB only:

The ACCDB version of the engine has CHECK constraints**: to create one
you must use ANSI-92 Query Mode SQL DDL syntax. You can do this using
the Access 2007 user interface, which uses ANSI-92 Query Mode by
default, or you can use the ACCDB version of the OLE DB provider, with
uses ANSI-92 Query Mode only.

The ACCDB version of DAO uses ANSI-89 Query Mode only (is incapable of
ANSI-92 Query Mode) and, unlike ADO, cannot use the OLE DB provider,
consequently you cannot create a CHECK constraint in the ACCDB engine
using DAO

**You need CHECK constraints to be able to create sequenced primary
keys (lowercase, meaning constraints that prevent duplicate data --
and I don't mean the front end forms LOL) e.g. a constraint in a
simple history table to prevent this:

INSERT INTO SalariesHistory (employee_ID, start_date, end_date,
salary_amount)
VALUES (1, #2001-01-01 00:00:00#, #2001-12-31 23:59:59#, 50000)
;
INSERT INTO SalariesHistory (employee_ID, start_date, end_date,
salary_amount)
VALUES (1, #2001-12-01 00:00:00#, #2002-12-31 23:59:59#, 75000)
;

otherwise for the period December 2001 employee_ID = 1 would seemingly
be in receipt of two salary amounts:

SELECT employee_ID, salary_amount
FROM SalariesHistory
WHERE employee_ID = 1
AND #2001-12-15 00:00:00# BETWEEN start_date AND end_date;

would return two rows for 50000 and 75000 respectively, being a
business rule violation in the context of most history tables. A table-
level CHECK constraint is required to prevent overlapping periods.

Jamie.

--

 

[Jamie Collins]

Assuming that documentation was produced for the release of Office
2000, that information is EIGHT YEARS OLD.

MSDN articles that are archived content (e.g. some I've seen relating
to Jet 3.51) are marked thus. That article remains the best reference
resource for Access SQL DDL. The Access 2007 Help contains far too
many inaccuracies as regards Access SQL syntax

Microsoft has changed its tune. That is obvious by the way they've
changed the default references in newly created databases.

Not as obvious as saying, "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." Show me an article that says "We've changed our tune
about ADO in favour of DAO" as explicitly as that!

ADO *never* made any sense for Jet data,
and anyone who understands what a database abstraction layer is
should see that (ADO is a database abstraction layer; DAO is an
interface to a particular database engine

Anyone who uses the SQL language understands that abstraction is a
good thing! The point of having an optimizer is that I use SQL to tell
it *what* I want and the optimizer uses its knowledge of the data
engine to determine *how* to achieve this. Both DAO and DAO must go
via the optimizer, that's abstraction.

DAO is faster than ADO but this is only noticeable in, what, less than
one percent of SQL operations across all Access users? If I told you
something could be achieved even faster than DAO by cracking the file
with a hex editor, would this be even better than an interface? Would
you use it in production code?

Microsoft made a decision (not on logic, but on marketing) to leave
certain new Jet 4 features out of DAO and put them in ADO, instead.
That is a flaw in MS's strategy, not in DAO.

MS's strategy may have been the cause but a deficient DAO is a very
real result.

Now that Jet is again a live development platform, I expect the
ACCDB version of DAO to gain lots of new features to keep up with
the development of the ACCDB Jet engine.

I expected it too but they didn't deliver, did they. Do you not see
that DAO (largely) skipped a step, let's call it the Jet 4.0? Why do
you think MSFT will ever catch up this step if they didn't do it for
Access2007? Also consider it is not just support for engine
functionality that DAO lacks, it's the enhanced properties/methods/
events/objects put into ADO still lacking in DAO (not to mention the
fundamental flaws in the DAO object model which one must code around
to prevent memory leaks etc).

And there are things in DAO that ADO can't do (hence the need for
JRO).

We've been here before, David. JRO is part of ADO. But yes, even when
you consider ADO as a whole there are some things that DAO can do that
ADO can't. For the overwhelming majority if Access/Jet engine
functionality both DAO and ADO do it equally well, so it's a lifestyle
choice which one you use.

[ADODB] lacks features that require
you to use *two* external libraries instead of one.

DAO lacks features that require one to use ADODB!

ADO is slower than DAO for Jet data.

Granted but, as before, for the majority of operations for the
majority of users it will make no difference. Denormalizing sometimes
gives better performance but to always denormalize would be the wrong
conclusion and some would say that the cost of denormalizing is never
justified (as I do with DAO and its flaky object model).

It's not native
to the db engine.

To employ a Fentonism, who gives a rat's arse? That's like telling a
father that there's nothing like a mother's love.

It makes *no* sense whatsoever to use ADO for Jet except for the
small handfull of features that Microsoft chose to put in ADO and
not in DAO 3.6.

So, put another way, there is some sense in using ADO, therefore
agreeing that "ADO offers no advantage" is a misstatement.

Jamie.

--

 

[Jamie Collins]

Microsoft has changed its tune. That is obvious by the way they've
changed the default references in newly created databases.

Agreed, both DAO and ADODB are referenced by default so MSFT considers
them to both be valid choices (alternatively, that using both is a
valid choice). And before you say it, DAO has a higher precedence
because there is more Access DAO code that fails to disambiguate
objects variables whereas the overwhelming majority of ADO code
learned from this mistake and fully-qualifies objects variables with
the appropriate library name. This is analogous as to why ADO is
superior to DAO in terms of object model and peoperties/methods/
events. ADO was built on DAO knowledge and experience but there is no
desire for MSFT to now reengineer DAO to capitalize on ADO successes.

Jamie.

--

 

[Jamie Collins]

On Mar 30, 7:47 pm, "David W. Fenton" <[email protected]>
wrote:

David,
Please help me resolve two apparently contradictory comments in your
post:

1) You say ADO *never* made any sense for Jet data because it is not
native to the db engine.

2) You say DAO for other db engines is a choice that needs to be made
based on how well things work with the drivers provided for it.

Q1. If DAO is not native to other db engines, how can using DAO for
them make any sense?

Q2. Can ADO make sense for Access/Jet if the drivers/providers
provided for it work well?

Jamie.

--

 

[Jamie Collins]

MS has changed its mind.

Please provide a citation for this.

FWIW I can provide citations for the opposite view, albeit articles I
consider to out of date e.g.

Access 2007 Developer Reference: Converting DAO Code to ADO
http://msdn2.microsoft.com/en-us/bb256407.aspx

Where is the equivalent article for porting ADO with the OLE DB
provider to DAOACE? Put another way, why is the sole Access 2007
article on conversion for _from_ DAO to ADO?

I am prepared to accept that MSFT has changed its mind but if it has I
think you should be aware that MSFT is not being very vocal about it,
hence the reason for the OP's question. FWIW I think the lack of
public announcements is because DAO has not been enhanced (e.g. the
aforementioned missing Jet 4.0 step), so both ADO and DAO are required
in the current Access 2007 era.

[Microsoft] is now recommending all DAO for Jet data

Please confirm that this does not mean that MSFT recommends not using
ADO for Access/Jet data (otherwise why did they create the
Microsoft.ACE.OLEDB.12.0 provider?)

Jamie.

--

 

[Jamie Collins]

I think ADO communicates with Jet directly.
Typo?

But
Access communicates with ADO through an intermediate layer, the name
of which I've forgotten.

OLE DB?

Jamie.

--

 

[Jamie Collins]

[ADO seems] an extra layer on top of DAO

(I'm guessing that the thingie that we supply to ADO that lets it read DAO
really just lets ADO translate it's requests into DAO-speak and relay them to
JET).

ADO uses DAO?! Your guess is completely wrong. Care to guess how much
remains of your credibility on the subject of ADO vs DAO? (try zero)

Jamie.

--

 

[Jamie Collins]

In my experience, DAO is WAAAAAAY faster than ADO for Access dbs.
Mike

In my experience, it rarely makes any different in real terms. That
said, I ceased using DAO seriously some years ago, as I suspect is the
case with you and ADO (assuming you ever used ADO seriously), so such
subjective sweeping statements aren't worth the paper they are written
on. Now, if you care to post some code to measure the difference...?

And since when was sheer performance the most important thing about
DMBS? What would you choose between the wrong answer in the blink of
an eye and the correct answer in due course? Denormalized designs
often give the best performance...

Jamie.

--

 

[Jack Hudson]

Thanks for all the comments. Still confused, but better informed.

Where does ADO.Net fit into this discussion, i.e., does ADO.Net have a place
in Access VBA?

Again, thanks for the info.

Best regards, Jack

 

[Douglas J. Steele]

Thanks for all the comments. Still confused, but better informed.

Where does ADO.Net fit into this discussion, i.e., does ADO.Net have a
place in Access VBA?

Not at the present time it doesn't.

 

[(PeteCresswell)]

Per Jamie Collins:

[ADO seems] an extra layer on top of DAO

(I'm guessing that the thingie that we supply to ADO that lets it read DAO
really just lets ADO translate it's requests into DAO-speak and relay them to
JET).

ADO uses DAO?! Your guess is completely wrong. Care to guess how much
remains of your credibility on the subject of ADO vs DAO? (try zero)

Who claimed credibility? If anybody got the impression that I did, I would
correct that misapprehension. After all, I *did* say "I'm guessing...."..