The best rule of thumb to go by is: use DAO when working with local
Access/Jet objects, and ADO when working with SQL Server data.
I agree with the above. ADO has much better sql server support
(stored procedures support for example is better).
In the
case of DSN-less links, you are working with local Jet QueryDef
objects. The links contain only connection info and not the actual
tables, which makes DAO a good choice. If you were to create a
recordset in code, you'd want to use ADO since it would be going
against SQL Server to retrieve the data. Using DAO in this case would
add additional overhead by loading Jet, making it very inefficient.
Loading JET, or loading the ado object model these days don't
make much difference!
Further, believe it or not, when you use a DSN less link, and use what is
called ODBC direct, then actually only load the dao object model,
and jet does not touch your code!
I am not kidding here, and I repeat:
JET DOES NOT get loaded, nor does jet even touch your sql
that you pass to the server!!!
Here is a code example for ODBC direct: (this is DAO!!)
Dim strCon As String
Dim rstRecords As DAO.Recordset
Dim wrk1 As DAO.Workspace
Dim MyCon As DAO.Connection
Set wrk1 = DBEngine.CreateWorkspace("TestWorkSpace", "", "", dbUseODBC)
strCon = "ODBC;driver={SQL Server};DSN=;" _
& "SERVER=192.168.1.101;" _
& "DATABASE=RidesSql;" _
& "UID=SA;PWD=;OPTION=3;"
Set MyCon = wrk1.OpenConnection("mycon", dbDriverNoPrompt, False, strCon)
' now, you have a regular connection, and can build a recordset as
' normal...
Set rstRecords = MyCon.OpenRecordset("select * from tblJunk")
Note I used "dbUseODBC", and thus we are by passing JET directly. Use of
this
keyword means that JET is not to be used, nor even loaded!
It would also mean that recordsets that are JOINS are NOT updateable like
they would be in JET
(or with ADO.......golly..does ADO support updateable joins? (or do you have
to used shaped
recordsets (another feature of ado!!)??? (anyone??)...
While the above is DAO, it does not load, or use JET (you have to use the
dbUseODBC
to prevent JET from loading)
I could also argue that ms-access runs all day long and runs
quite fast despite having to load JET all the time. Once it
is loaded, then that time is not really much of a issue.
It is kind like saying a macros in ms-access run slow, and VB runs much
faster.
Fact is, when you use a macro to open a form, or VB code to open a form,
while the VB code runs 100's of times FASTER then macros, the problem is not
the speed of the VB vs the Macro code, but the rather LARGE time it takes to
load a access form (this large form load time is the SAME if you use VB, or
macros to load).
This same concept applies to using DAO, or ADO to sql server. As long as the
queries can be processed on the server side, then really, you will not see
any performance difference in a JET linked table to sql server, or a dao, or
even a ADO pass-through query query. (ado certanly encourages and helps make
sure you sql runs server side..but it is not the only way to keep things
server side).
In words
select * from tblCustomer where InvoiceId = 12345
In all 3 examples (odbc dircet, dao, dao linked tables, ado)...the sql is
sent to sql server, procccsed, and from the table of 1 millon reocrds, ONE
record is sent back. There is NO practical peformance diffence in this case
is all 3 of the above approaches..
So, I certainly do agree that ADO is better for sql server, but not because
it performs better, but because the ADO object model is the next gen data
object that came after DAO and JET. (there are some things that show ado can
scale better with sql server...but really...it not a big issue).
Another good reason to use the ADO object model is it allows you to switch
your data engine with greater ease then JET. In fact, when we use ms-access
with ado, we are going from ado to jet. Since all code looks just at the ado
object, then you rely "less" on a particular data engine object model (it
gives another layer of abstraction here). The idea here is thus to break the
connection between code and the data engine even further. I mean, if we all
had used ado all the time, then migration to sql server would be REAL easy!
the ado.net data object is once again even better at this concept of
abstracting out the data engine farther from the code.
Once again, I most certainly agree with your recommends that ado is better
for sql server, but from a performance point of view, it is a hard sell!