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.

 

[NEAL MCCAWLEY]

I don't understand. If you don't need to use ADO, then....why do you? Can
you answer my original question?

Thanks.

 

[Douglas J. Steele]

Given that your "original question" doesn't appear anywhere in this thread,
I have no idea what it was.

I use ADO sometimes because it's easier. I have both cups and glasses in my
kitchen cabinet, There's never a time when I "must" use a glass: I could
always use a cup. However, there are times when I prefer to use a glass.

 

[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 *****

 

[Joe Fallon]

Steve,
Very interesting post.
I think your last statement is an excellent prediction.
I am also betting that MS is working hard on developing some sort of
Access.Net version.
(I do not have any inside info on this topic....)

 

[Sirocco]

Thanks Mary. You answered a previous question of mine and your wisdom
never fails me.
(BTW, I posted the original question as "Sirocco", the alias I prefer to
"Neal" but my account was reset after installing an update. I'm Sirocco
again.).

Anyway, it seems a few books I was reading last summer predicted a move to
ADP. But it seems I still may be getting some misdirection. Here's the
problem. I'm not worried about an "efficient" system. I'm worried about
what's "necessary". The conversation often leads to ADP but it often seems
to be in reference to "efficiency" when my concern is "what's necessary?".
Do you see my dilemma? Here's my original question again, slightly
reworded, and I think it represents a concern that is pervasive:

My current system includes an Access 2003 mdb front and am mdb back end end
(on a server - about 20 users). I'd like to put the tables on a SQL server
but keep the mdb front end (multi-user of course). The current mdb front
end contains a utility that re-links the mdb tables and it, like everything
else on the mdb front end, is written in DAO - I inherited it and did not
write it myself. CONVERSION OF THIS UTILITY IS MY CONCERN. I know I need
to re-write it at least a little but can I do it in DAO? Now, I found an
*ARTICLE* that contains DAO code to create a DSN-less link to SQL Server,
and for some reason I saw this article as manna from heaven, that it's my
solution (to the utility conversion problem). Or is this DSN-less stuff
even relevant to my problem? But of course it's a source of confusion
because I thought ADO must be used when the
tables were on a SQL Server! I 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 this re-linking utility
when I
transfer my tables to a SQL back end, in particular to define my data
source (right?), but can I in fact use DAO, as this *ARTICLE* suggests?
Note that the article does contracdict your "rule of thumb", which is to use
ADO with SQL Server, and this contradiction does of course cause confusion,
and demonstrates the "what's necessary" / "what's efficient" fork in the
road. I need a nuts n bolts strategy, and these 2 inch thick books I've
been reading have a way of not helping. So, regarding the *concern* I
described a few lines back, what is, if you please, the least time-consuming
way to approach the re-writing of my re-linking utility (assuming this
utility is even needed with Access 2003 mdb front and SQL back), based on
the architecture I stated above.

Thank you Mary and the others so much, I look forward to your responses.
Sirocco

 

[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?

 

[Albert D. Kallal]

My current system includes an Access 2003 mdb front and am mdb back end end
(on a server - about 20 users). I'd like to put the tables on a SQL server
but keep the mdb front end (multi-user of course). The current mdb front
end contains a utility that re-links the mdb tables and it, like everything
else on the mdb front end, is written in DAO - I inherited it and did not
write it myself. CONVERSION OF THIS UTILITY IS MY CONCERN. I know I need
to re-write it at least a little but can I do it in DAO?

Yes, you most certanly can keep *most* of your code in dao. I would not
worry too much

Now, I found an
*ARTICLE* that contains DAO code to create a DSN-less link to SQL Server,
and for some reason I saw this article as manna from heaven, that it's my
solution (to the utility conversion problem). Or is this DSN-less stuff
even relevant to my problem?

Actualy, you are correct, the dsn less stuff is actualy confising the issue
here.

But of course it's a source of confusion
because I thought ADO must be used when the
tables were on a SQL Server! I have been somewhat anxious about
converting
my existing DAO to ADO, and have even started using ADO in some of my new
coding projects.

That is just fine, but once again, you can contiue to use the dao code that
you have.
(yes, ado is better)

So, as a genearl rule:

new projects, and using sql server, then use ado.

old existing project with lots of code, then contiue to use dao

Granted I'll have to re-code this re-linking utility

Yes, and in fact, what you do is cntinue to use your linked tables, but now,
you simply link the tables to sql server via odbc (and, yes, you will and
can use dao code).

You new re-linking code to sql sserver can be found here:

http://members.rogers.com/douglas.j.steele/DSNLessLinks.html

So, here is what happens:

you upside your data to sql server, and then change all the linked
tables to not look at the back end, but now they look at sql server.

Here is some thoughts and comments:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server applications):

http://www.attcanada.net/~kallal.msn/Search/index.html

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

** Don't use quires that require more then one linked table

When you use
ODBC, one table could be on the corporate server, and the other ODBC might
be a FoxPro table link 3 computers from the left of you. As a result..JET
has a real difficult time joining these tables together..and JET can not
assume that the two tables are on the same box..and thus have the "box" join
the tables. Thus,while jet does it best..these types of joins can often be
real slow. The simple solution in these cases is to change the query to
view..and link to that. This is the least amount of work, and means the
joins occur on the server side. This also applies to combo boxes. Most
combos boxes has sql embedded in them. That sql has to be processed, and
then thrown to a linked odbc table. This is a bit sluggish. (a form can have
maybe one, or two combos..but after that ..it will start to load slow). So,
remove the sql from the combo box, build a view..and link the combo box
direct to that view (JUST USE the view name...the sort, and any sql need to
be in the view). The result is quite good combo box load performance. (and
again, not very much work. There are other approaches that can even speed
this up more..but we have to balanced the benefits VS. the amount of work
and coding. I don't think once should re-code all combo boxes to a call back
with a pass-through reocrdset..but that can be a solution also).

So, again, you can freely link all your tables to sql server via odbc, and
ONLY work on things that don't perform well

Of course, if you are using a ADP access project, the above points
about the joins with more then one table does NOT apply..since all
queries execute on the sql server side.

** Of course, if you do have sql with more then one table..then pass-though
is the best if using odbc.

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice,a and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.

** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100's of entries!). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducing the bandwidth
(amount of data) goes hand in hand. So, better applications
treat the users well..and run faster!

Hence, you simply link all your tables to sql server, and THEN
fix the parts that don't work well.

A few more tips:

make sure that you add a timestamp field to the sql server tables, and this
stamp is exposed to ms-access
(ms-access with linked odbc tables (your dao) uses the timestamp to help
figure what the heck needs to
be updated.

So, for a existing applications, you can keep you dao, and get good
performance. In fact, you only have to change
code and things that don't work.

For example, in dao to a ms-access table, you can get the current autonumber
id as follows:
:

dim rstRec as DAO.RecordSet
dim lngNewID as long
set rstRec = currentdb.OpenRecordSet("yourtable")

rstRec.AddNew
rstRec!City = "Edmonton"
lngNewID = rstRec!ID
rstRec.Update
rstRec.Close
set RstRec = Nothing

At this point, lngNewID is set to the last id created.

When using sql server, you have to force the update, so, all my code (which
works both for JET, and sql server) is now:

rstRec.AddNew
rstRec!City = "Edmonton"
rstRec.Update
rstRec.BookMark = rstRec.LastUpdated
lngNewID = rstRec!ID

So, *most* of your dao code will work, but as above, some things do need to
be changed.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
Post a follow-up to this message

 

[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

 

[Sirocco]

Albert, I can't thank you enough. You have gone above and beyond the call
of duty. Once I combine all these messages I will have a very valuable
reference. Thank you Albert, Mary and everybody else who contributed to
this thread - so fascinating.

Sirocco

 

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