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

"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.
So, I am saying *if* one makes the choice to use ado, you do NOT have to
build a connection string to the back end JET share nor do you have to use a
jet odbc connection to the back end. 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).

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

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

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

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%'

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

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!

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

This simply makes (forces) your sql statements to be MORE database engine
neural. So, that is why ado is a BETTER choice then dao if you plan to be
working with different data engines.

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

I think that's pure bullshit. 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.

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.

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

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.

When we had vb6 developers coming into access development, they often used
ado because that's what they had been using. 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.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx


.

Relevant Pages

  • Re: WITH COMPRESSION
    ... People who used Jet before ... ADO were perfectly fine with DAO, and got the benefit of all the ... I would think the number of developers who knew real SQL 92 syntax ...
    (comp.databases.ms-access)
  • Re: Use DAO or ADO?
    ... SQL Server is more popular than Jet. ... real world of development, Dot Net", ADO has been superceded by ADO.NET ...
    (comp.databases.ms-access)
  • Re: query mdb on network into Word. Access.exe not on workstation
    ... adodb provider for Jet did not know what to do. ... not sure that ADO uses DAO. ... >>> have ADO 2.8 because it was installed with the Office installation. ...
    (microsoft.public.access.externaldata)
  • Re: query mdb on network into Word. Access.exe not on workstation
    ... adodb provider for Jet did not know what to do. ... not sure that ADO uses DAO. ... >>> have ADO 2.8 because it was installed with the Office installation. ...
    (microsoft.public.data.ado)
  • Re: query mdb on network into Word. Access.exe not on workstation
    ... adodb provider for Jet did not know what to do. ... not sure that ADO uses DAO. ... >>> have ADO 2.8 because it was installed with the Office installation. ...
    (microsoft.public.vb.database.ado)