DAO vs ADO

[Sirocco]

I found an article that contains DAO code to create a DSN-less link to SQL
Server. This is exciting to me because I thought ADO must be used when the
tables were on a SQL Server, and have been somewhat anxious about converting
my existing DAO to ADO, and have even started using ADO in some of my new
coding projects. Granted I'll have to re-code my existing VB when I
transfer my tables to a SQL back end, in particular to define my data
source, but can I in fact use DAO, as this article suggests?

Also, generally, when using a SQL back end, when MUST I use ADO and when can
I just use my existing DAO?

Many thanks in advance.

 

[Douglas J. Steele]

I'm not sure you ever "have to" use ADO instead of DAO, but I've found I use
ADO when running stored procedures that I need to pass parameters to and/or
get a return value from.

 

[Mary Chipman]

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. 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.
FWIW, in a linked table app it is far more efficient to work with
pass-through queries to execute stored procedures and the like, which
you can also manipulate easily by using DAO to set the connection and
..SQL properties.

--Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Steve Jorgensen]

I believe it is not a universal truth that DAO/JET is less efficient than ADO
for querying server data. In fact, if you're not knowledgeable about all the
details of ADO operation and careful in implementation, ADO can incur a lot of
extra overhead retrieving metadata from the server for each query. Since PCs
are so fast these days, an extra layer of abstraction on the client, more or
less, is unlikely to be the bottleneck in a transaction that communicates
across a network, and then with a data store on a disk drive.

I'm not actually saying DAO is preferable to ADO for accessing server data,
but I feel DAO is not as unsuitable as is often assumed. I used it
successfully with Access 97 C/S solutions for years before there was an ADO,
and that was on what would now be considered antique hardware. Also, I still
find that the tried and true Access MDB/DAO combination is much more stable
and predictable than ADP/ADO (or MDB/ADO which is an odd hybrid), so when
Access is the front-end, I've learned to stick mostly to DAO (except when I
want parameterized server SP calls or disconnected recordsets - and not with
forms) regardless of the back-end. This may change later if Microsoft
improves the ADP/ADO technology, but I'm thinking it's more likely that a
better Access.NET solution will arise first, and ADPs will never really take
off.

 

[Albert D. Kallal]

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!

 

[Lyle Fairfield]

I agree with the above. ADO has much better sql server support
(stored procedures support for example is better).


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")

<snips>

Do tables, views, stored procedures and udfs appear in the database window
when such a connection is made from Access?

If, so can we double click on these objects to open their "contents" in
datasheet view, and can we bind a form to them?

If so, is this paragraph from the
Microsoft® Jet Database Engine Programmer’s Guide!
(kind of an old book)
still applicable?

***** begin quote *****
Microsoft Access, which relies on Microsoft Jet for its database
functionality, has calls to Microsoft Jet written into its internal code,
and doesn’t call the DAO DLL except when Visual Basic code in a Microsoft
Access database calls DAO. In other words, when you display a table through
the Microsoft Access user interface, Microsoft Access calls Microsoft Jet
directly through hard-coded routines. When you open a table by using Visual
Basic code, Microsoft Access hands the request to the DAO component.
***** end quote *****

 

[Mary Chipman]

The whole idea behind using ADO with SQL Server is to avoid *both* Jet
and DAO, which loads the Jet engine. The last thing you need is extra
overhead when going against SQL Server.

--Mary

 

[Mary Chipman]

This may change later if Microsoft
improves the ADP/ADO technology, but I'm thinking it's more likely that a
better Access.NET solution will arise first, and ADPs will never really take
off.

I agree with you. IMO ADPs never worked the way they should have to
begin with, and they're looking more and more like a dead-end
technology when you consider all the complexity Yukon is bringing to
the table (CLR integration, UDTs, etc) to say nothing of SQL Express,
which will have its own tools. To get ADPs right with Yukon would be a
tremendous effort, and it's hard to see how that would be a worthwhile
investment.

--Mary

 

[Lyle Fairfield]

The whole idea behind using ADO with SQL Server is to avoid *both* Jet
and DAO, which loads the Jet engine.

The WHOLE idea?

 

[Mary Chipman]

Originally there were no alternatives to DAO, which was the first data
access API. The evolutionary chronology goes like this: first there
was DAO, which is inefficient due to increased overhead of loading
Jet. then came RDO (for VB) and ODBCDirect for Access, which did not
load Jet. Then came ADO, which was intended to be "universal data
access" providing a common interface for multiple providers. So yes,
in the context of this thread, the whole idea was to get Jet out of
the way when you don't need it by using a provider that did not load
Jet. There were of course other motivations for building ADO

--Mary

 

[Larry Linson]

Originally there were no alternatives to
DAO, which was the first data access
API. The evolutionary chronology goes
like this: first there was DAO, which is
inefficient due to increased overhead of
loading Jet. then came RDO (for VB)
and ODBCDirect for Access, which did
not load Jet. Then came ADO, which
was intended to be "universal data access"
providing a common interface for multiple
providers. So yes, in the context of this
thread, the whole idea was to get Jet out of
the way when you don't need it by using a
provider that did not load Jet. There were
of course other motivations for building ADO

Somewhere along that way, you left out the low-level API for ODBC that was
sometimes used in classic VB.

Except in the world of Office, classic ADO was just another of those
one-release-wonders ("database method of choice for the foreseeable
future"), long since replaced by ADO.NET (which, it appears, is going to
last at least two versions of Visual Studio <G>).

But, given the history, sounds as if the other motivation might have been:

"Hey, what do we do with this department full of people whose only
experience has been writing 'database access methods of choice for the
foreseeable future'?"

"Can't they come up with another one for the next release?"

Larry <grinnin', duckin', and runnin'>

 

[Craig Alexander Morrison]

Mary, I remember you from the CompuServe days. You moved from a card
carrying member of the lurking public on a thread titled "Use Primary Key?"
in 1994 do you remember that? I remember your description of that thread as
"intellectual mud wrestling".

I also admired the following quote, but forgot to mention it at the time:

"oh, i see... since my name is "mary" i must be a female, thus i only do
rolodexes and recipes and thus could have no conceivable interest in such an
elevated discussion amongst real, testosterone-laden developers who have
big, important clients to design for. well, that derogatory remark sort of
goes with the territory in this thread, so i'm not even going to bother
defending myself."

I did like that a lot, thankfully it wasn't to me. Good to see you doing so
well. I do like the use of the Capital Letters.

....enough of the flashback.

Is ADO a dead duck? Is DAO and/or ADO a useful stepping stone to the .NExT
thing?

 

[Craig Alexander Morrison]

Caution: Do not take the following as fact; much is based on impressions and
not inside track information.

My, perhaps mistaken, understanding is that ADO has only another year or so
left (in a current product).

SQL Server (.NET) 2005 and Access (.NET) 2005/2006 are coming soon and the
rebadging of the Office Developer as Visual Studio Tools for Office with its
major pre-requisite being Visual Studio .NET does suggest the shift is
coming.

ADPs and DAPs are dead, as far as I can see it. Never tried the DAPs, but
did get some ADPs to work quite well, but always felt I had at least one
hand tied behind my back.

As I say I may be wrong, however that is the impression I get.

Our nickname for Microsoft Office System 2003 which mainly added enhanced
XML support, was Microsoft Office XP (SP3) until the actual Microsoft Office
XP (SP3) was released. I am not sure about the general opinion here but I
saw 2003 as a holding operation pending the next versions of SQL Server,
Windows and Office due next year.

I am hoping that 2003 proves to be the solid pre .NET tool that 97 has been
for many years.

Note, and this is a FACT: Microsoft Office System 2003 SP1 is now available
hard on the heels of the real SP3 for Microsoft Office XP.

 

[Craig Alexander Morrison]

What I am really asking is, is ADO.NET 2.0 going more like DAO than ADO or
is that the equivalent of asking if Bicycles are more like Ferraris than
Fords?

This is not my area, you may gather.

 

[Mary Chipman]

I find it amazing that you've saved all that old stuff. Well, times
sure have changed since then. For one thing, I don't do flame wars any
more and I learned where the shift key is located on the keyboard

ADO will be around for a while, but it's definitely not the way of the
future. If you're looking forward, think "managed code". It basically
boils down to, if Microsoft has a large installed base of customers
out there using a particular technology, then it will continue to be
supported, if not forever, then for a while longer. I don't see VB6
(or even Jet) dying quite as quickly as some have predicted. I
wouldn't necessarily recommend new development in deprecated
technologies, but I wouldn't recommend re-writing something that works
from the ground up either. FWIW, Access isn't a deprecated technology
per se, although certain features may be. They're already planning
v.next in Redmond. Whether that version will support developer
features compatible with or competitive with .NET is anyone's guess at
this point.

--Mary

 

[Craig Alexander Morrison]

Mary

I have a select collection (only 100 Threads, about 1500 messages) of
discussions mainly about Relation Database Design from late 1993 to early
1995 which is fairly constant in this changing world. They were collected in
an Access database that started life in 1993 (Version 1.1) and was available
for general download in 1994 to 1995 (Version 2.0).

That was a time when relational databases first came to the desktop with the
introduction of Access, not perfect but streets ahead of the competition.

All of my professional production systems from Access 2.0 have converted up
to each subsequent "good" version with very few problems. I would expect
this to continue even if the .NExT thing is a fairly dramatic shift.

I have been taking some time out from live developments to "play" with the
SQL Server 2005 BETA and was reviewing which old databases I would "play"
with. I now have a Access 2003 application that front ends a SQL Server 2005
database of my original Access 1.1 CompuServe Threads. At the same time I
have been revisiting the newsgroups and saw both your name and a
conversation not a million miles from the "Use Primary Key?" thread, titled
"Rule when Primary Key is needed?".

I notice that quite a few of the old names from the CompuServe days had or
have gone on to write books about and around Access, have you all got
nothing better to do. (vbg)

All the best.

--
Slainte

Craig Alexander Morrison

I find it amazing that you've saved all that old stuff. Well, times
sure have changed since then. For one thing, I don't do flame wars any
more and I learned where the shift key is located on the keyboard

ADO will be around for a while, but it's definitely not the way of the
future. If you're looking forward, think "managed code". It basically
boils down to, if Microsoft has a large installed base of customers
out there using a particular technology, then it will continue to be
supported, if not forever, then for a while longer. I don't see VB6
(or even Jet) dying quite as quickly as some have predicted. I
wouldn't necessarily recommend new development in deprecated
technologies, but I wouldn't recommend re-writing something that works
from the ground up either. FWIW, Access isn't a deprecated technology
per se, although certain features may be. They're already planning
v.next in Redmond. Whether that version will support developer
features compatible with or competitive with .NET is anyone's guess at
this point.

--Mary

snipped...

 

[Mary Chipman]

Most of the folks from the old Compuserve Access MVP crowd has moved
on to either .NET or SQL Server or both, except Viescas, who I believe
is still active in the Access ng's. My last book was several years ago
now, but the basic concepts of how to build an efficient Access-SQL
app haven't changed since then, so it's still useful info for many
people. I probably won't write another one any time soon. It's a lot
of work to do a good one with original research that isn't just a
rehash of the help files. Even though our book is still selling, the
hourly rate for the time we expended compares unfavorably with the
hourly rate for flipping burgers

If you're interested, these days you'll get the livliest discussions
on relational design in the microsoft.public.sqlserver.programming
newsgroup, where Joe Celko livens up many a thread.

Your Access FE should work fine with SQLS 2005 as long as you're
running in 2000 compatibility mode. It's unclear at this point if
Access will ever have full support for new features like CLR
assemblies and UDTs. There's only so much you can tack on to what is
essentially an ancient architecture. How much has really changed in
the last 10 years?

--Mary

 

[Lynn Trapp]

Even though our book is still selling, the

hourly rate for the time we expended compares unfavorably with the
hourly rate for flipping burgers

That's kind of like the hourly rates for teachers. Still, I can assure you
that you are much more appreciated by your audience than any burger flipper
ever was by their customers.

 

[Steve Jorgensen]

Most of the folks from the old Compuserve Access MVP crowd has moved
on to either .NET or SQL Server or both, except Viescas, who I believe
is still active in the Access ng's. My last book was several years ago
now, but the basic concepts of how to build an efficient Access-SQL
app haven't changed since then, so it's still useful info for many
people. I probably won't write another one any time soon. It's a lot
of work to do a good one with original research that isn't just a
rehash of the help files. Even though our book is still selling, the
hourly rate for the time we expended compares unfavorably with the
hourly rate for flipping burgers

I suppose you have to factor in how the name recognition factors in to your
billing rate though, eh? I see your book on a lot of book shelves, including
my own.

If you're interested, these days you'll get the livliest discussions
on relational design in the microsoft.public.sqlserver.programming
newsgroup, where Joe Celko livens up many a thread.

As do the opinions about Joe Celko. In learning about Agile database design,
though, I'm starting to sympathize more with some of what Joe says. Just
because a system will "probably" end up benefiting from surrogate keys at some
point is no reason to throw them in before they actually "are" needed
(provided that cascade update is available). It complicates the front-end
system without any proven benefit.

 

[Mary Chipman]

That's kind of like the hourly rates for teachers. Still, I can assure you
that you are much more appreciated by your audience than any burger flipper
ever was by their customers.