DAO or ADO in Windows 2000, which to choose?

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

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

"Simon Harvey" < XXXX@XXXXX.COM > skrev i


issue

issue


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.




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




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




With the objects contained within ADO.




MSDN

P

Then they are Connection objects.


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

can't

Yes you can!

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

P

Rather childishly pedantic set of responses ...











Yes, connections to databases.


as


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.


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.

recordset

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.

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




idiotic,

What? Mine or yours?










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


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

are
there

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.


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

you

I'm not in public.


Read the Access newsgroups.


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?


idiotic,

Do you listen to your own advice?

P