Re: DAO vs ADO

From: Albert D. Kallal (PleaseNOOOsPAMMkallal_at_msn.com)
Date: 08/08/04

  • Next message: Lyle Fairfield: "Re: DAO vs ADO" 
    Date: Sun, 8 Aug 2004 01:39:17 -0600

    "Mary Chipman" <mchip@online.microsoft.com> wrote in message
    news:vaq9h0dvf65vpb9boi75c00bpo1cllk3ee@4ax.com...

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

    -- 
Albert D. Kallal   (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn
  • Next message: Lyle Fairfield: "Re: DAO vs ADO"