DAO or ADO in Windows 2000, which to choose?

[Simon Harvey]

Hi guys,

I noticed when I tried to create a Database object in VBA, that VB had no
idea what i was talking about. I figured it would be another reference issue
but i couldnt think why such a default library would be missing. So I
discovered that ADO, which doesnt have a database object, is referenced by
default and you have to explicitly reference DAO 3.6 to get access to the
required objects.

I have two questions. How come ADO is used now instead of DAO and should I
be using ADO instead?

Also, in as few words as possible (i'm not expecting a whole article on it
, how do you access database tables using ADO.

Does anyone have a decent resource that describes ADO in terms of VBA
explicitly?

Thanks everyone

Kindest Regards

Simon

 

[Lars-Eric Gisslén]

Simon,

I'm not going to tell which one to use, but I only use ADO in VBA. Ok, I
nerly only works with MS SQLServer so then there is no problem. As I'm a
system developer I prefer ADO because you can use it in almost any
programming language as it's an ActiveX Interface to the OleDB system. That
makes life much easier

To put it simple, ODBC is the past and OleDB is the future. Even if your DB
vendor does only provides ODBC drivers (instead of OleDB provider) you can
still access ODBC drivers as there comes an OleDB provider also for ODBC.

There is nothing specific for VBA and ADO as the ActiveX interface is just
the same independent of which dev tool you are using, it's more a matter of
learning ADO's object model and how to use it. If you for instance know how
to use ADO in C++ you will also know how to use ADO in VBA. The syntax will
differ a little bit as they are two different dev tools but the way you use
the objects is still the same.

There are plenty of information on the MSDN site about how to use ADO. What
you should pay much attention to is the Connection object. The Command and
Recordset objects are quite straight forward. If your data source support
cursors you should learn as much as possible about cursors as you may get
unexpected behaviour is you use the wrong kind of kursor.

Regards,
Lars-Eric

 

[Phobos]

Hi guys,

I noticed when I tried to create a Database object in VBA, that VB had no
idea what i was talking about. I figured it would be another reference issue
but i couldnt think why such a default library would be missing.

It's not missing, you just have to set a reference to it.

If VBA had native support for every concievable object library, the
resources required would be ridiculous, so you have to reference them.

So I
discovered that ADO, which doesnt have a database object, is referenced by
default and you have to explicitly reference DAO 3.6 to get access to the
required objects.

Correct, Microsoft have decided that ADO is the way to go, even though most
Access programmers have a different opinion.

I have two questions. How come ADO is used now instead of DAO and should I
be using ADO instead?

Depends, if you are using Access alone then DAO is quicker then ADO because
it manipulates JET dbs directly.

Also, in as few words as possible (i'm not expecting a whole article on it
, how do you access database tables using ADO.

With the objects contained within ADO.

Does anyone have a decent resource that describes ADO in terms of VBA
explicitly?

MSDN

P

 

[Phobos]

ADO does have database objects, but they're called Connections.

Then they are Connection objects.

ADO is the later technology, and much more powerful for some data
activities, particularly across the Net.

ADO is not "more powerful" than DAO, DAO is limited to manipulating JET
objects whereas ADO can access many different data-sources but is slower as
there is an extra layer (OLE DB).

DAO is simpler and a bit easier to use, and entirely adequate if you are
simply reading and writing rows from a database.

DAO is much faster than ADO for manipulating Access data objects and there
is no real difference between the functionality of the two technologies,
most Access programmers use DAO whenever they can.

If you know nothing about either and have a free choice, use ADO. You can't
use both.

Yes you can!

There is nothing to stop you declaring an ADO recordset and a DAO recordset
within the same procedure.

P

 

[martinique]

Rather childishly pedantic set of responses ...

Then they are Connection objects.

Yes, connections to databases.

ADO is not "more powerful" than DAO, DAO is limited to manipulating JET
objects whereas ADO can access many different data-sources but is slower as
there is an extra layer (OLE DB).

ADO has a lot of features that DAO doesn't have (disconnected recordsets,
server/client-side cursors, schema-manipulation, shaped recordsets, etc,)
that make it significantly more powerful; and for most uses apps developed
with it run significantly faster also, even with plain JET tables.

DAO is much faster than ADO for manipulating Access data objects and there
is no real difference between the functionality of the two technologies,
most Access programmers use DAO whenever they can.

Wrong all round. DAO can't manipulate Access data objects at all. You have
to use OLE automation for that. DAO can manipulate JET database objects
(such as are created by Access), but the speed difference is rarely
significant. 'No real difference between the functionalities' !? Suggest you
read the documentation before making a fool of yourself in public.

'Most' Access programmers? I doubt it, patently untrue, and irrelevant
anyway.

Yes you can!

There is nothing to stop you declaring an ADO recordset and a DAO recordset
within the same procedure.

Apart from common sense. You can't exchange information between the two
other than manually via code.


Phobos, I regret the passing of DAO too. But that's no reason to be idiotic,
offensive, and untruthful in a posting. Get a life.

 

[Doug Robbins - Word MVP]

I saw nothing offensive at all in Phobos response.

If you would post with a valid email address, I would make a few other
comments to you privately.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Phobos]

Rather childishly pedantic set of responses ...

What? Mine or yours?

Yes, connections to databases.

So, what you are saying is that ADO can only connect to databases?

ADO has a lot of features that DAO doesn't have (disconnected recordsets,
server/client-side cursors, schema-manipulation, shaped recordsets, etc,)
that make it significantly more powerful; and for most uses apps developed
with it run significantly faster also, even with plain JET tables.

ADO has these features because you can connect to more and different apps
than just Access databases.

Wrong all round. DAO can't manipulate Access data objects at all.

So DAO cannot manipulate data objects within Access???

I really do not know how to respond to that comment, it is so patently
absurd as to be nothing more than argument for the sake of argument.

DAO can manipulate JET database objects
(such as are created by Access)...

You just stated that it can't, I wish you'd make your mind up.

...but the speed difference is rarely
significant. 'No real difference between the functionalities' !? Suggest you
read the documentation before making a fool of yourself in public.

I'm not in public.

'Most' Access programmers? I doubt it, patently untrue, and irrelevant
anyway.

Read the Access newsgroups.

Apart from common sense.

So you can use both!

Why do you say "you can't use both" and then follow it up with a statement
that is supposed to reinforce your argument, but instead refutes it?

Phobos, I regret the passing of DAO too. But that's no reason to be idiotic,
offensive, and untruthful in a posting. Get a life.

Do you listen to your own advice?

P

 

[martinique]

What? Mine or yours?

No need to apologize. You've already nailed your ribbon to the mast.