ADO VS. DAO

[Karen Hill]

Which one should I use for Access Databases 2000 and up? Which one is
newer, better and recommended?

[Rich P]

This is a relative question. If you are using an Access data project
(.adp) for interfacing with sql server you will have to use ADO because
sql server does not support DAO. If you are using an mdb then you have
a choice between ADO and DAO. My feeling is that DAO is better suited
for mdb's since the mdb is Jet based for which DAO was designed, where
ADO is more suited for a sql server type of engine.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[John Welch]

I agree with Rich. When I was starting out, I used ADO for mdbs because
Microsoft said that ADO was "newer and better and recommended", but then I
decided (with help from this group and reading) that DAO is the better
choice if you're sure your project is going to be an mdb and stay an mdb (or
mde).

"Karen Hill" <ka**********@yahoo.com> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.com...

Which one should I use for Access Databases 2000 and up? Which one is
newer, better and recommended?

[(PeteCresswell)]

Per Karen Hill:

Which one should I use for Access Databases 2000 and up? Which one is
newer, better and recommended?

If your back end is .mdb, DAO gives more functionality and takes less coding.

If your back end is C/S, ADO gives you more functionality - like pipelining
multiple result sets down a single connection/hit.

For my money, you should use DAO for going against .MDBs and ADO for C/S.

DAO links will work for C/S - and maybe they're the right thing to do under some
circumstances.... but I'd still favor ADO and stored procedures.
--
PeteCresswell

[David W. Fenton]

"Karen Hill" <ka**********@yahoo.com> wrote in
news:11**********************@t31g2000cwb.googlegr oups.com:

Which one should I use for Access Databases 2000 and up? Which
one is newer, better and recommended?

ADO was created later, which makes it newer, but Microsoft abandoned
development of ADO in favor of ADO.NET, which shares little with ADO
other than the name.

Newness never means a damned thing. It was obvious to me when A2K
was released that there was no point in using ADO against Jet data,
even though that was what MS was pushing very hard.

And now MS implicitly admits it was an error on their part, since
now DAO is recommended by them for Jet data.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

"Karen Hill" <ka**********@yahoo.com> wrote in
news:11**********************@t31g2000cwb.googlegr oups.com:

Which one should I use for Access Databases 2000 and up? Which one is
newer, better and recommended?

I use ADO for everything.

ADO has not been abandoned by MS.
ADO is much simpler to code than DAO.
ADO gives you many times the functionality than DAO gives you for both MDBs
and ADOs.

BTW, I have used DAOs extensively and expertly in the past.

--
Lyle Fairfield

[Rich P]

Quick note: I have several Enterprise Access projects (.adp) that use a
combination of VBA and ADO for interfacing with Sql Server. You cannot
use DAO code in an ADP. You can only read data from sql server tables
in an Access ADP with ADO - com ADO. com ADO can also be used with
MDBs, but DAO is easier for reading data from Access mdb tables.
Although, I use com ADO in mdbs for reading/writing data to Excel files.
Plus, from Excel 2000 and forward, there is support for com ADO which is
real nice for using properties/ methods within Excel for reading data
from an Access mdb tables like

Dim cmd As New ADODB.Command, RS As New ADODB.RecordSet
cmd.ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=;Data Source=" &
strPath
cmd.CommandType = adCmdText
cmd.CommandText = "Select * From tbl1"
Set RS = cmd.Execute
Range("A1").CopyFromRecordset RS
'--also works great with Sql Server tables - just different connection
string

For .Net project (VB, C#) you use ADO.Net. The big difference between
ADO.Net and com ADO is that ADO.Net is object oriented and you can do
stuff like this (for sql Server):
Imports System.Data.SqlClient (VB.Net)
Use System.Data.SqlClient (C#)
...
Dim cmd = New SqlCommand("Select * From tbl1, conn)

or for Access it would be
Imports System.Data.OleDb (VB)
Use System.Data.OleDb (C#)
...
Dim cmd = New OleDBCommand("Select * From tbl1, conn)

And a quick plug for VS.Net - VB still uses a lot of classic VB syntax,
but a lot of statements are interchangeable with C#. The big difference
between C# and VB.net is that C# uses a lot of Brackets{} and
semicolons. Otherwise, they have the same amount of functionality, both
use delegates, interfaces, and the rest of OOP stuff. C# is just a
little bit lower level - a little more granular than VB.Net (you can
fine tune a C# app a little more than VB.Net). Just remember, that the
thing with VB/VB.Net is RAPID APPLICATION DEVELOPMENT. There is nothing
faster than any form of VB for rapdid application development -
asp/aspx, exe, dll projects, interfacing with Sql
Server/Oracle/Mainframes...

There is no more ADODB in ADO.Net

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

Quick note: I have several Enterprise Access projects (.adp) that use a
combination of VBA and ADO for interfacing with Sql Server. You cannot
use DAO code in an ADP. You can only read data from sql server tables
in an Access ADP with ADO - com ADO. com ADO can also be used with
MDBs, but DAO is easier for reading data from Access mdb tables.
Although, I use com ADO in mdbs for reading/writing data to Excel files.
Plus, from Excel 2000 and forward, there is support for com ADO which is
real nice for using properties/ methods within Excel for reading data
from an Access mdb tables like

Dim cmd As New ADODB.Command, RS As New ADODB.RecordSet
cmd.ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=;Data Source=" &
strPath
cmd.CommandType = adCmdText
cmd.CommandText = "Select * From tbl1"
Set RS = cmd.Execute
Range("A1").CopyFromRecordset RS
'--also works great with Sql Server tables - just different connection
string

For .Net project (VB, C#) you use ADO.Net. The big difference between
ADO.Net and com ADO is that ADO.Net is object oriented and you can do
stuff like this (for sql Server):
Imports System.Data.SqlClient (VB.Net)
Use System.Data.SqlClient (C#)
...
Dim cmd = New SqlCommand("Select * From tbl1, conn)

or for Access it would be
Imports System.Data.OleDb (VB)
Use System.Data.OleDb (C#)
...
Dim cmd = New OleDBCommand("Select * From tbl1, conn)

And a quick plug for VS.Net - VB still uses a lot of classic VB syntax,
but a lot of statements are interchangeable with C#. The big difference
between C# and VB.net is that C# uses a lot of Brackets{} and
semicolons. Otherwise, they have the same amount of functionality, both
use delegates, interfaces, and the rest of OOP stuff. C# is just a
little bit lower level - a little more granular than VB.Net (you can
fine tune a C# app a little more than VB.Net). Just remember, that the
thing with VB/VB.Net is RAPID APPLICATION DEVELOPMENT. There is nothing
faster than any form of VB for rapdid application development -
asp/aspx, exe, dll projects, interfacing with Sql
Server/Oracle/Mainframes...

There is no more ADODB in ADO.Net

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[RoyVidar]

Rich P wrote in message <yx***************@news.uswest.net> :

You cannot
use DAO code in an ADP. You can only read data from sql server
tables
in an Access ADP with ADO - com ADO.

I agree that ADO is perhaps more suited for ADPs, but aren't you a bit
too categoric here? I mean, the CurrentDB method isn't available, but I
think you can do something like the below with the Nortwhind sample
database within an ADP, just reference the DAO library

dim db as dao.database
dim rs as dao.recordset
dim l as long

set db = dbengine.opendatabase("", dbDriverNoPrompt, false, _
"ODBC;DSN=Northwind;UID=;PWD=;Database=NortwhindCS ")
set rs = db.openrecordset("select * from employees", dbopendynaset, _
dbseechanges)
' loop the recordset ...

--
Roy-Vidar

[Larry Linson]

"Karen Hill" <ka**********@yahoo.com> wrote

Which one should I use for Access Databases
2000 and up? Which one is newer, better and
recommended?

You can use DAO with both Jet and with any ODBC-compliant server database.
In fact, after a lot of cluttering their Knowledge Base, newsgroups, etc.
with recommendations for ADP and ADO, the Access developers at Microsoft are
recommending Access MDB, Jet, ODBC, and server DB over ADP, ADODB, and
server.

If you want "newer", that would be ADO.NET (which is not supported by any
released version of Access). If you want "better", that would depend on who
you ask (but see above), if you want "recommended", that would again depend
on who you ask and when you asked them.

The "classic ADO" that is available in Access is a dead end, superceded by
ADO.NET which is not even built on the same object model.

[Lyle Fairfield]

Larry Linson wrote:

The "classic ADO" that is available in Access is a dead end, superseded by
ADO.NET which is not even built on the same object model.

On
http://msdn.microsoft.com/data/mdac/...dacroadmap.asp

MS says:
"ADO: ActiveX Data Objects (ADO) provides a high-level programming
model that will continue to be enhanced. Although a little less
performant than coding to OLE DB or ODBC directly, ADO is
straightforward to learn and use, and can be used from script languages
such as Microsoft Visual Basic® Scripting Edition (VBScript) or
Microsoft JScript®."

CONTINUE TO BE ENHANCED

[rkc]

Larry Linson wrote:

You can use DAO with both Jet and with any ODBC-compliant server database.
In fact, after a lot of cluttering their Knowledge Base, newsgroups, etc.
with recommendations for ADP and ADO, the Access developers at Microsoft are
recommending Access MDB, Jet, ODBC, and server DB over ADP, ADODB, and
server.

They need to get the word out to somebody besides you because
all the experts that get paid to rearrange the help files into
$50 books don't know it yet. The last one I was browsing at
Barnes and Nobles (something with Access 2003 VBA in the title)
never mentioned DAO or ODBC. In fact the "Real World Application"
presented in the manditory "Real World Application" chapter used
ADO exclusively via recordset connection properties with no linked
tables or bound forms anywhere in sight.

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:o6***************@twister.nyroc.rr.com:

Larry Linson wrote:

You can use DAO with both Jet and with any ODBC-compliant server
database. In fact, after a lot of cluttering their Knowledge
Base, newsgroups, etc. with recommendations for ADP and ADO, the
Access developers at Microsoft are recommending Access MDB, Jet,
ODBC, and server DB over ADP, ADODB, and server.

They need to get the word out to somebody besides you because
all the experts that get paid to rearrange the help files into
$50 books don't know it yet. The last one I was browsing at
Barnes and Nobles (something with Access 2003 VBA in the title)
never mentioned DAO or ODBC. In fact the "Real World Application"
presented in the manditory "Real World Application" chapter used
ADO exclusively via recordset connection properties with no linked
tables or bound forms anywhere in sight.

Then that book was written by a complete idiot who really had
absolutely no understanding of the issues involved and had no
business writing a book on Access.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[rkc]

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:o6***************@twister.nyroc.rr.com:

Larry Linson wrote:

You can use DAO with both Jet and with any ODBC-compliant server
database. In fact, after a lot of cluttering their Knowledge
Base, newsgroups, etc. with recommendations for ADP and ADO, the
Access developers at Microsoft are recommending Access MDB, Jet,
ODBC, and server DB over ADP, ADODB, and server.

They need to get the word out to somebody besides you because
all the experts that get paid to rearrange the help files into
$50 books don't know it yet. The last one I was browsing at
Barnes and Nobles (something with Access 2003 VBA in the title)
never mentioned DAO or ODBC. In fact the "Real World Application"
presented in the manditory "Real World Application" chapter used
ADO exclusively via recordset connection properties with no linked
tables or bound forms anywhere in sight.

Then that book was written by a complete idiot who really had
absolutely no understanding of the issues involved and had no
business writing a book on Access.

I don't know about complete idiot, but they certainly side stepped
nearly every benefit of using Access in the first place.

[Rich P]

Well, I guess if you work it, then there is a way to use
DAO code in an ADP. But why go through the hassel when you have the
CurrentProject.Connection object which works with com ADO very nicely?
The idea that I am trying to point out is that the later versions of
Access have been developed to do 2 things now 1) be a desktop RDBMS
(mdb), 2) be a front end interface for an Enterprise RDBMS like sql
server (adp). DAO is for the mdb. ADO is for the ADP. And ADO.Net is
for VS.Net. These technologies all have their place. Yes, com ADO and
DAO are interchangeable in the Access environment where ADO.Net is
exclusive to VS.Net. And the beauty of ADO.Net is that now you have
Non-Persisting tables in memory that don't require I/O disk reads. Now
you have real performance (with ADO.Net)

I guess my answer to the original post, based on the information
provided, would be to experiment, and read a lot of books to be familiar
with the technologies.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Larry Linson]

Yes, didn't I mention "cluttering the knowledge base and their website with
recommedations of ADP and ADO"? I don't doubt that, having done so, they
will be forced to maintain ADO for some period of time, but I don't believe
the promise of enhancements is really likely to happen.

Check the Office 12 blogs for comments on ADPs and DAPs. Their URLs have
been posted here.

Larry Linson
Microsoft Access MVP

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
Larry Linson wrote:

The "classic ADO" that is available in Access is a dead end, superseded by
ADO.NET which is not even built on the same object model.

On
http://msdn.microsoft.com/data/mdac/...dacroadmap.asp

MS says:
"ADO: ActiveX Data Objects (ADO) provides a high-level programming
model that will continue to be enhanced. Although a little less
performant than coding to OLE DB or ODBC directly, ADO is
straightforward to learn and use, and can be used from script languages
such as Microsoft Visual Basic® Scripting Edition (VBScript) or
Microsoft JScript®."

CONTINUE TO BE ENHANCED

[David W. Fenton]

Rich P <rp*****@aol.com> wrote in
news:Kw*****************@news.uswest.net:

The idea that I am trying to point out is that the later versions
of Access have been developed to do 2 things now 1) be a desktop
RDBMS (mdb), 2) be a front end interface for an Enterprise RDBMS
like sql server (adp). DAO is for the mdb. ADO is for the ADP.
And ADO.Net is for VS.Net. These technologies all have their
place. Yes, com ADO and DAO are interchangeable in the Access
environment

This is simply *not* true. DAO is superior to ADO when using Jet
data. It's not a matter of whether it's an ADP or an MDB, but of
where your data is stored.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Rich P]

<<
This is simply *not* true. DAO is superior to ADO when using Jet data.
It's not a matter of whether it's an ADP or an MDB, but of where your
data is stored.

yes. I am in complete agreement with you that for Jet, DAO is
definitely the way to go. If you notice that in all of my posts where I
reference currentDB in example code, I am also always using DAO code.
When interfacing with sql server you have various options. From the mdb
you can use DAO and ODBC or com ADO, and the coding efforts are about
the same. In the ADP you also can use DAO with ODBC or ADO, but ADO is
definitely easier to code against sql server tables. Anyway, that is my
experience. My suggestion to the original poster is to experiment and
read up on the various technologies available. Bottom line, there are
always several ways to carry out the same processes. Which way is the
best way? There really isn't any best way. It would be a matter of
preference, or whatever is being done in your shop.

For processing mdb tables from an Access mdb, it is easiest for me to
whip up DAO code. For Sql Server tables from an Access ADP it is
easiest for me to whip up some code with an ADO command object. I am
happy that Microsoft added the Sql Server interface (adp) to Access.
There is nothing easier for processing sql server tables outside of
Query Analyzer than an Access ADP - quite a bit easier than a VS.Net
project if you don't need multi-threading or delegates, etc. Plus, the
ADP has some real nice features like the ServerFilter property, and the
UniqueTable property which acts similar to the "Instead Of" trigger in
sql server where you can manipulate a table that is joined to another
table (which would otherwise be nonUpdatable in an MDB). There are just
so many options that after you understand the technology it is a matter
of preference.

Rich

*** Sent via Developersdex http://www.developersdex.com ***