[Access2003, VBA] Use DAO or ADO?

External Since: May 26, 2005 Posts: 31

Larry

SQL Server is more popular than Jet.

Stop living in a cave, old man.

-Aaron

On Dec 26, 1:52 pm, "Larry Linson" wrote:
> One of the reasons my clients preferredAccess/ 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; competentAccessdevelopers 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" inMicrosoft
> development. Instead, it was just another ofMicrosoft'sattempts at
> three-letter-acronym data handling techniques that lasted no, or little,
> longer than any of the others.
>
> Larry Linson
> MicrosoftOfficeAccessMVP
>
> "David W. Fenton" wrote in messagenews:Xns9B7EAC2D9756f99a49ed1d0c49c5bbb2@74.209.136.88...
>
>
>
> > "Albert D. Kallal" wrote in
> >
>
> >> "David W. Fenton" 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 yourAccessfront
> > 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
> >> avoidperformancebottle 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 inperformance). 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 ofperformancebenefits 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 bullshit. 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 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.
>
> > 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 foraccessapplications,
> >> 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 intoaccessdevelopment, 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
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -

External Since: May 26, 2005 Posts: 31

thanks Lyle-- I applaud your passion
thanks Lyle-- I applaud your passion
thanks Lyle-- I applaud your passion

On Dec 27, 6:12 am, lyle fairfield wrote:
> On Dec 26, 10:22 pm, "David W. Fenton"
> wrote:
>
> > Classic ADO is a dead technology that noAccessdeveloper who is not
> > developing ADPs should spend even one minute with.
>
> If ADO is a dead technology and MS has returned to ODBC links as its
> preferred way of interfacing with Server-hosted data, then why, inAccess2007, does this code
>
> Dim myWorkSpace As DAO.Workspace
> Set myWorkSpace = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
>
> elicit this (error) response:
>
> MicrosoftVisual Basic
>
> Run Time Error ‘3847’
>
> "ODBCDirect is no longer supported.
> Rewrite the code to use ADO instead of DAO."
>
> ?