Re: [Access2003, VBA] Use DAO or ADO?

One of the reasons my clients preferred Access / Jet / ODBC was that they
did not have to search out developers who knew specific back-end server
language dialects. Those were harder to locate and more expensive to
hire/contract; competent Access developers were in greater supply and were
less expensive. Those clients _instructed_ the prime contractors to avoid
passthrough queries unless there was a compelling need; the common
compelling need we found was a Stored Procedure to calculate the next key
for each given table. Those keys were not subject to being "lost" like
Autonumber or its server equivalent and could have been shown to the users
(though we did not do so... for business and business logic reasons, we used
them similarly to how Autonumber is used).

OLEdb seems obviously to be a dying technology... classic ADO may be around
in the Office world, and a few other software packages, but in "Microsoft's
real world of development, Dot Net", ADO has been superceded by ADO.NET
which is not OLEdb-based, and uses a different Object Model.

I agree, David, that the claimed "advantages" of ADO are most often simply
workarounds to avoid problems created by selecting ADO in circumstances
where it made no sense in the first place, except that someone thought
(wrongly) that it was to be the "wave of the future" in Microsoft
development. Instead, it was just another of Microsoft's attempts at
three-letter-acronym data handling techniques that lasted no, or little,
longer than any of the others.

Larry Linson
Microsoft Office Access MVP

"David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx> wrote in message
news:Xns9B7EAC2D9756f99a49ed1d0c49c5bbb2@xxxxxxxxxxxxxxxx
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@xxxxxxx> wrote in
news:s3l4l.1975$H11.1866@xxxxxxxxxxxx:

"David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx> wrote in message

You can use CurrentProject.Connection when you have linked
tables to a jet backend to open a ado recordset.

Why would you want to do so?

Because one might have chosen to use ado in place dao in their
application.

Why would you have done that?

So, I am saying *if* one makes the choice to use ado,

...which I'm saying is a mistake in any app with linked tables...

you do NOT have to
build a connection string to the back end JET share

...which you'd never have to do if you used linked tables...

nor do you have to use a
jet odbc connection to the back end.

...which you'd never have to do with linked tables...

There is a built-in connection object
created for you (it likely pulls the connection string out of the
linked table..but it not using odbc).

...which you'd never need if you're using linked tables.

So, that means you can go in your code with ado:

Dim rs As New ADODB.Recordset

rs.Open ("select * from contacts"), CurrentProject.Connection
Do While rs.EOF = False
Debug.Print rs!FirstName
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Who cares? If you're using linked tables, you don't have to do it
that way at all. You can use DAO on the CurrentDB and be done with
it, never having to worry one bit about connection strings for your
back end.

This is the point of linked tables.

Not using them unnecessarily complicates an application without
offering any signficant benefit.

Perhaps you are not clear or even aware that when using linked
tables to a jet file share you do have a built in ADO connection
object.

If you're using ADO. But why would you use ADO with linked tables?

So, again my
point was **IF** you going to use a ado recordsets, you don't have
create or build your connection string to the jet back end.

But *why* would you use ADO if you're using linked tables?

So, in the above I not setting up a odbc connection to that jet
back end..am I???

I don't know and I really don't care. You're giving an academic
example of what ADO makes easier if you choose the wrong tools.
That's nice that ADO makes it really easy to get around the mistake
of choosing ADO as your interface, but, well, I'm questioning the
initial choice of ADO when you have linked tables -- DAO makes
*much* more sense.

And eliminates the problem you say ADO "solves."

While there is some advantages when using ADO with sql server,
oracle (at least ones that provide a oleDB connection) there
also a good case to choose ADO if you plan to upgrade to sql
server in the future.

Why? If you're using ODBC, you gain exactly nothing.

You gain sql neutral code. eg:

dao:
select * from tclCustomers where city like 'new*'
The above is jet/dao syntax, but if you use an ado reocrdset, you
can go:

ado:
select * from tclCustomers where city like 'new%'

Why does this matter? Every ODBC driver usable by Jet will translate
Jet's syntax into the relevant syntax for the back end in use. Why
would anyone *care* about having "neutral" SQL in your Access front
end?

Both the above are hitting a jet back end. However, the 2nd syntax
will ALSO work for mysql, oracle and sql server if I take that sql
and want to run it server side.

Via ODBC linked tables, the first will also work with MySQL, Oracle
and SQL Server.

And, if simply want to change the reocrset to pass-though, then
again I do NOT have to change the sql syntax. (in fact, if I do
setup a oleDB connection string, then I get 100% by-pass of jet,

I'm not sure this is correct, but can't prove that it's not.

But why anyone would want to throw away all the enormous capability
of Jet, I don't know. So far as I can tell, it's only ignorant
people with an irrational fear of an incredibly versatile database
engine that they don't understand who want to avoid Jet at all
costs.

and I not have to
change the sql one bit. If you code to dao, you WILL have to
change your syntax to work with sql,mysql etc. if you want to
avoid performance bottle necks.

And if you're using linked tables, none of this is important.

Note that it is not that oleDB is faster then odbc, it
is the fact that you are by-passing jet (that DOES make a big
difference in performance). So, while it most a myth that oleDB is
faster then odbc, a pass-though that by-passes jet (via oleDB, or
jet odbc) is MUCH faster then going though jet. Again, MUCH
faster!

I'd like to see some evidence for this claim. I don't believe it's
true. If it were, MS wouldn't be saying that MDB/ODBC is better than
ADP/OLEDB. Remember, they explain that the data shaping layer with
ADP/OLEDB causes significant bottleneck. And that layer is there
with ADO used from an MDB, too.

So, your claim of performance benefits seems to me to be at odds
with MS's own recommendations.

Hence, with ado, you wind up using ONE sql syntax, and a NOT
mix/mash in your reocrdset code when you hitting the server.

With linked tables you end up using one SQL syntax and it works
automatically with all back ends. You don't *need* to know what the
back end SQL dialect is.

This simply makes (forces) your sql statements to be MORE database
engine neural.

And this is good because...?

So, that is why ado is a BETTER choice then dao if you plan to be
working with different data engines.

I think this is all so much bull***. The point of ODBC and linked
tables is that they make the specifics of the back end database
engine irrelevant.

So, ADO is a better abstraction and it is more data engine
neutral then DAO.

I think that's pure bull***. If you're using linked tables, then
Jet is involved, and it's just bloody silly to use ADO to work
with Jet, even if the back end is something other than Jet. If
you're using ODBC, then you're not communicating directly with
the back end tables.

Well, if you change your connection string from JET to sql server,
you ARE now communication directly with the server side..and you
NOT have changed your sql syntax. This will be far less work to
use that sql if you choose ado as your recordset model.

If you're using linked tables, you don't have to make any changes to
the SQL syntax when switching from Jet to SQL Server linked tables.

I don't know what you're nattering on about. Every example you
provide seems to ignore reality, claiming for ADO exactly the same
benefits you get with linked tables and DAO.

ADO does tend to
force you to be somewhat more data engine neural when you write
your sql statements.

I think that is, once again, completely BS.

Well, I guess we see this differently. ADO is MORE vendor/data
engine natural then dao is. That is not conjecture, that is just
the way it is.

Give me an example of where you have to write SQL for linked tables
differently for different database engines?

ADO is NOT the data engine where as dao is pretty much the jet
engine object model.

And Jet can communicate via ODBC and various ISAMs with a whole host
of database engines. And it's remarkably smart in doing so, taking
away much of the complexity involved, and presenting all data
sources as similar.

ADO represents an abstraction on top of whatever you are using, be
it jet,oracle, MySql, sql server etc.

I don't see really any reason to use ADO for access applications,
but there are cases when server side stuff is involved.

And if you're using linked tables, there's no reason to use ADO.

Which is what I've been saying repeatedly.

When we had vb6 developers coming into access development, they
often used ado because that's what they had been using.

Yes, because they are ignorant of the benefits of linked tables in
Access, which they've never encountered before.

However, now that ado is on it
way out, there is LESS reasons to choose ado (even in ms-access).
However, using ado in access still can make sense in some cases.

There is *no* reason to use ADO from Access if you're using linked
tables, except for the handful of things that can be done through
ADo that can't be done through DAO. For Jet data, there's about 4 of
these, for other db engines, I'm sure there are more. For non-Jet
dbs, passthrough queries obviate any need to resort to ADO, seems to
me.

You have not provided a single compelling argument for using ADO in
an Access application, except if you're not using linked tables.
Since linked tables are the key feature of Access that makes it easy
to use, ADO only makes sense if you're ignoring the best practices
for the application development tools you're using.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


.