DAO or ADO?

I have been using DAO for quite a number of years now, in both Visual Basic
and in MS Access. In each new project I start now, I quietly ask myself if I
should concider the time it would take to learn ADO. The little I've tried
it out (without cracking a book) I found no advantages to ADO, but I have a
nagging doubt that it was created without good reason, and am wondering if
anyone has strong opinions as to which is better, and why?

Thank you,

Ross

"raylitalo" wrote in message
...
I have been using DAO for quite a number of years now, in both Visual Basic
and in MS Access. In each new project I start now, I quietly ask myself
if I
should concider the time it would take to learn ADO. The little I've
tried
it out (without cracking a book) I found no advantages to ADO, but I have
a
nagging doubt that it was created without good reason, and am wondering if
anyone has strong opinions as to which is better, and why?

Thank you,

Ross

There may be some advantages to ADO when programming against any database
other than Jet/Access. In those cases, ADO could allow a fairly simple
conversion from one database engine to another (i.e., SQL Server to Oracle).
But that is not the case with Jet/Access because it is likely you'll have to
re-write code or insert branch logic since SQL in Jet/Access is not strictly
ANSI standard.

nagging doubt that it was created without good reason, and

MS had the choice of extending DAO, or creating a new object.
They chose to create a new object: the design of the new
object is more comfortable for C++ programmers, and less
comfortable for Basic programmers.

A DAO database has tabledefs, which have fields. This provides
a method of data access which does not involve SQL. MS wanted
to provide this kind of access to general data. DAO has a
connection syntax which allows it to connect to Jet IISAM
data suppliers (like the 'text IISAM' or the 'Excel IISAM')
or to any ODBC data supplier. MS wanted an object which
could be connected to an OLEDB data supplier. OLE is a kind
of standard Microsoft connection technology. MS could have
re-written and extended DAO to support the new kind of connection,
but instead they chose to create a new kind of object.

Of course it by re-writing and extending DAO, they changed
it and broke old code, there would have been many unhappy
people.

A DAO dbengine object has a 'createworkspace' method:
a workspace has an 'opendatabase' method: a database has
an 'openrecordset' method. That means that there is both
a forward and a backward interface from dbengine to recordset,
and from recordset to dbengine.

This is a convenient paradigm for users: you can start with
a dbengine and there is a method which leads you to a recordset:
you can start with a recordset and there are properties which
lead you back to the dbengine.

But it is a design paradigm which is disliked by the people who
have to design and support the object. In object design, you want
to create objects with 'high cohesion and low coupling': you want
each object to know as little about any other kind of object as
is possible, so that changes or bugs in objects don't affect other
objects.

In ADO, a connection object doesn't have an 'openrecordset'
method. Instead, you might create a recordset object, and
create a connection object, and then connect up the recordset
object and the connection object. This is the kind of behaviour
I would have in any object which I wrote myself, and it is
the kind of behaviour people who write objects expect to see
and feel comfortable with.


So there you have it: ADO, a data object technology that
supports OLEDB, using a popular object design paradigm.
More recent than DAO, with newer features (XML), and
some lack of overlap, where feature implementation is
not exactly the same.

Does that make ADO or DAO better? ADO, absolutely, if you
want newer features, support for OLEDB, and a particular
object design paradigm....

(david)








"raylitalo" wrote in message
...
I have been using DAO for quite a number of years now, in both Visual
Basic
and in MS Access. In each new project I start now, I quietly ask myself
if I
should concider the time it would take to learn ADO. The little I've
tried
it out (without cracking a book) I found no advantages to ADO, but I have
a
nagging doubt that it was created without good reason, and am wondering if
anyone has strong opinions as to which is better, and why?

Thank you,

Ross

Okay, you have 2 replies from people who both know what they are talking
about: Paul says DAO is best for working in Access/JET, and David says ADO
is more flexible.

The main question is, what do you need to do? If you are creating databases
in Access, using tables stored in Access (JET), then use DAO. It *is*
Access. When you create a query graphically, Access itself is using DAO to
manage this and run it. When you create relationships, Access uses DAO to
handle this.

Microsoft pushed ADO for one reason: they wanted us all to shift our data
storage from Access/JET to SQL Server. That makes sense for them: they are a
marketing machine who can get more money that way. It makes *no* sense for
individuals, small developers, not-for-profit groups, and micro businesses.
I think most people are seeing through that now, and the DAO library is back
by default in Access 2003.

It is true that some of the new JET 4 features are not exposed in DAO, so
you may need to learn ADO at some stage also. But it is also true that many
features are not exposed in ADO. Even with the ADOX extensions you cannot
add a yes/no column to a table so that it displays a check box like you get
through the interface by default. That means that it is now much harder to
learn the whole scenario, as you have multiple libraries to learn and
remember if you want to do everything.

But if you are creating databases in Access, using Access tables, there is
absolutely no reason to follow the marketing hype and switch to ADO.
Leverage the knowledge you already have. Use the libaray Access itself uses,
the only library you can use for many of the structural aspects - DAO.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"raylitalo" wrote in message
...
I have been using DAO for quite a number of years now, in both Visual Basic
and in MS Access. In each new project I start now, I quietly ask myself
if I
should concider the time it would take to learn ADO. The little I've
tried
it out (without cracking a book) I found no advantages to ADO, but I have
a
nagging doubt that it was created without good reason, and am wondering if
anyone has strong opinions as to which is better, and why?

Thank you,

Ross

"Allen Browne" wrote:

Okay, you have 2 replies from people who both know what they are talking
about: Paul says DAO is best for working in Access/JET, and David says ADO
is more flexible.

The main question is, what do you need to do? If you are creating databases
in Access, using tables stored in Access (JET), then use DAO. It *is*
Access. When you create a query graphically, Access itself is using DAO to
manage this and run it. When you create relationships, Access uses DAO to
handle this.

Microsoft pushed ADO for one reason: they wanted us all to shift our data
storage from Access/JET to SQL Server. That makes sense for them: they are a
marketing machine who can get more money that way. It makes *no* sense for
individuals, small developers, not-for-profit groups, and micro businesses.
I think most people are seeing through that now, and the DAO library is back
by default in Access 2003.

It is true that some of the new JET 4 features are not exposed in DAO, so
you may need to learn ADO at some stage also. But it is also true that many
features are not exposed in ADO. Even with the ADOX extensions you cannot
add a yes/no column to a table so that it displays a check box like you get
through the interface by default. That means that it is now much harder to
learn the whole scenario, as you have multiple libraries to learn and
remember if you want to do everything.

But if you are creating databases in Access, using Access tables, there is
absolutely no reason to follow the marketing hype and switch to ADO.
Leverage the knowledge you already have. Use the libaray Access itself uses,
the only library you can use for many of the structural aspects - DAO.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


Wow, this group is really great. Thanks to all of you for your fantastic
replies!

Ross

Hi David and Raylito,

These are the differences I see when using ADO with
Access versus DAO with Access.

Benefits for ADO:
ADO provides a way to use Events.

Against ADO:
It IS a little slower. I'll get to this at the end.

An Example:
Private WithEvents adbADODB As ADODB.Recordset

Where you can NOT declare DAO withevents.
Private adbDAO As DAO.Recordset

What events are missing? And what are events? And how
are they used?

If the record is being navigated, you will get a MoveComplete
event. It's like the mouse clicks on listboxes or comboboxes,
the Click event fires when you click inside a combobox.

This makes the ADO more useful, because you don't
have to create hacks and create your own events. They are
already there and can be raised by the base class so that
outer classes and/or forms can see them and then update
something accordingly. The example that Microsoft inside
the VB data wizard uses the MoveComplete event to
update the text within a label and give a visual notification
of the record number currently being looked at.

By default, VB6 automatically uses ADO controls inside
of their Form wizards. If you create a new database form
using the wizard, you will be asked if you want to use a
Class, an ADO Data Control or ADO code. If you
choose VB to create to the class, you will see how the
events are used and you will see the full selection of
events.

I'm not too familiar with OLEDB. As far as know, OLE
means ActiveX and ActiveX means multiple threads, or
a .dll or an .exe that exposes certain classes and/or events.

One last thing about the explanation that David provided
with Connection / Recordset, I must comment upon.

It IS naturally convenient to open a database before you
open the recordset. The ONLY thing I think of an ADODB
connection as is as a connection to the database. I think
David is flawed in describing it the way he did. But
perhaps I'm stuck in my old DAO ways. g

In ADODB, you ALWAYS set up some sort of connection
to the file being connected to. In DAO, it was the DBEngine
Workspace that provided the means to supply user
identification, where in ADODB it is provided through the
Connection. Unless you're providing secure access to the
database, you will NEVER have a need to use the DAO
Workspace (if I'm wrong, someone let me know).

Either way, I think of it in this manner. The Connection in
ADODB is the Database in DAO. And each is required.
I would never use an ADODB.Recordset without using
an ADODB.Connection. The connection provides the
path the database. The database (data-engine) holds
the tables (recordsets) inside of it. They are both needed.

When I create an ADODB.Connection, I make sure I
destroy that object as well. The same should be done
with DAO as well.

What does this mean as far as cost goes? The cost of
ADODB is that tends to be slower, but you won't notice
it if you don't have a big enough recordset, or don't do
enough processing. So is that a cost? And when it comes
to other types of files, DAO is only made to access .mdb
but there are ways around that. .mdb's can be made to
connect via ODBC and whole tables be brought across
inside of Access, and whole new tables created. In this
manner, there is a good chance that DAO could be faster.

Will you notice the difference? If you're not working with
millions of records, you'll likely not see any difference. I
myself, don't like messing with ADO. I like DAO and I
love Microsoft Access. But the two are so similar, that
I really don't see a difference. A connection is always
needed to use the recordset (the same as the database
is always needed to use the recordset).

Private adoPrimaryRS As ADODB.Recordset

Private Sub Form_Load()
Dim db As ADODB.Connection, sConn As String, sRS As String
Set db = New ADODB.Connection
sConn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mail\test.mdb;"
sRS = "select age,id,namefirst,namelast from tPeople Order by namelast"
db.CursorLocation = adUseClient
db.Open sConn

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open sRS, db, adOpenStatic, adLockOptimistic
End Sub

Now, this is the default class ADO that VB6 creates with their
wizard. I wouldn't code it that way myself, that's just Microsoft
programmers that coded it like that.

In DAO, it would be as follows:

Private db As DAO.Database
Private rs As DAO.Recordset

Private Sub Form_Load()
Dim sDB As String
Set db = Database.Open("C:\mail\test.mdb")
Set rs = db.OpenRecordset("tPeople")
End Sub

Private Sub Form_Unload()
If IsObject(db) Then
db.Close
Set db = Nothing
End If
If IsObject(rs) Then
rs.Close
Set rs = Nothing
End If
End Sub

And it's not too difficult to raise an event inside of a
class module...

RaiseEvent MoveComplete

:-)

--
Jim Carlock
Post replies to newsgroup.

"david epsom dot com dot au" wrote:
nagging doubt that it was created without good reason, and

MS had the choice of extending DAO, or creating a new object.
They chose to create a new object: the design of the new
object is more comfortable for C++ programmers, and less
comfortable for Basic programmers.

A DAO database has tabledefs, which have fields. This provides
a method of data access which does not involve SQL. MS wanted
to provide this kind of access to general data. DAO has a
connection syntax which allows it to connect to Jet IISAM
data suppliers (like the 'text IISAM' or the 'Excel IISAM')
or to any ODBC data supplier. MS wanted an object which
could be connected to an OLEDB data supplier. OLE is a kind
of standard Microsoft connection technology. MS could have
re-written and extended DAO to support the new kind of connection,
but instead they chose to create a new kind of object.

Of course it by re-writing and extending DAO, they changed
it and broke old code, there would have been many unhappy
people.

A DAO dbengine object has a 'createworkspace' method:
a workspace has an 'opendatabase' method: a database has
an 'openrecordset' method. That means that there is both
a forward and a backward interface from dbengine to recordset,
and from recordset to dbengine.

This is a convenient paradigm for users: you can start with
a dbengine and there is a method which leads you to a recordset:
you can start with a recordset and there are properties which
lead you back to the dbengine.

But it is a design paradigm which is disliked by the people who
have to design and support the object. In object design, you want
to create objects with 'high cohesion and low coupling': you want
each object to know as little about any other kind of object as
is possible, so that changes or bugs in objects don't affect other
objects.

In ADO, a connection object doesn't have an 'openrecordset'
method. Instead, you might create a recordset object, and
create a connection object, and then connect up the recordset
object and the connection object. This is the kind of behaviour
I would have in any object which I wrote myself, and it is
the kind of behaviour people who write objects expect to see
and feel comfortable with.

So there you have it: ADO, a data object technology that
supports OLEDB, using a popular object design paradigm.
More recent than DAO, with newer features (XML), and
some lack of overlap, where feature implementation is
not exactly the same.

Does that make ADO or DAO better? ADO, absolutely, if you
want newer features, support for OLEDB, and a particular
object design paradigm....

(david)

"raylitalo" raylitalo wrote:
I have been using DAO for quite a number of years now, in both
Visual Basic and in MS Access. In each new project I start now,
I quietly ask myself if I should concider the time it would take to
learn ADO. The little I've tried it out (without cracking a book)
I found no advantages to ADO, but I have a nagging doubt that
it was created without good reason, and am wondering if
anyone has strong opinions as to which is better, and why?

Thank you,

Ross

I don't usually enter into these discussions any more, because ADO vs. DAO
was something of a 'religious war' issue some years back, and it got to be
pretty boring listening to some people frothing at the mouth over it (not
that anyone is doing that in this thread, I hasten to add! :-)

In the interests of accuracy, though, I want to point out that an ADO
recordset does not *always* need an ADO connection. For example, no
connection is required if the source of the Recordset is an XML file. And in
addition to security, another use of the DAO Workspace object is to
implement transactions.

Personally, I don't mind using either ADO or DAO for most data manipulation,
but I much prefer DAO over ADOX for data definition.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"Jim Carlock" wrote in message
...
Hi David and Raylito,

These are the differences I see when using ADO with
Access versus DAO with Access.

Benefits for ADO:
ADO provides a way to use Events.

Against ADO:
It IS a little slower. I'll get to this at the end.

An Example:
Private WithEvents adbADODB As ADODB.Recordset

Where you can NOT declare DAO withevents.
Private adbDAO As DAO.Recordset

What events are missing? And what are events? And how
are they used?

If the record is being navigated, you will get a MoveComplete
event. It's like the mouse clicks on listboxes or comboboxes,
the Click event fires when you click inside a combobox.

This makes the ADO more useful, because you don't
have to create hacks and create your own events. They are
already there and can be raised by the base class so that
outer classes and/or forms can see them and then update
something accordingly. The example that Microsoft inside
the VB data wizard uses the MoveComplete event to
update the text within a label and give a visual notification
of the record number currently being looked at.

By default, VB6 automatically uses ADO controls inside
of their Form wizards. If you create a new database form
using the wizard, you will be asked if you want to use a
Class, an ADO Data Control or ADO code. If you
choose VB to create to the class, you will see how the
events are used and you will see the full selection of
events.

I'm not too familiar with OLEDB. As far as know, OLE
means ActiveX and ActiveX means multiple threads, or
a .dll or an .exe that exposes certain classes and/or events.

One last thing about the explanation that David provided
with Connection / Recordset, I must comment upon.

It IS naturally convenient to open a database before you
open the recordset. The ONLY thing I think of an ADODB
connection as is as a connection to the database. I think
David is flawed in describing it the way he did. But
perhaps I'm stuck in my old DAO ways. g

In ADODB, you ALWAYS set up some sort of connection
to the file being connected to. In DAO, it was the DBEngine
Workspace that provided the means to supply user
identification, where in ADODB it is provided through the
Connection. Unless you're providing secure access to the
database, you will NEVER have a need to use the DAO
Workspace (if I'm wrong, someone let me know).

Either way, I think of it in this manner. The Connection in
ADODB is the Database in DAO. And each is required.
I would never use an ADODB.Recordset without using
an ADODB.Connection. The connection provides the
path the database. The database (data-engine) holds
the tables (recordsets) inside of it. They are both needed.

When I create an ADODB.Connection, I make sure I
destroy that object as well. The same should be done
with DAO as well.

What does this mean as far as cost goes? The cost of
ADODB is that tends to be slower, but you won't notice
it if you don't have a big enough recordset, or don't do
enough processing. So is that a cost? And when it comes
to other types of files, DAO is only made to access .mdb
but there are ways around that. .mdb's can be made to
connect via ODBC and whole tables be brought across
inside of Access, and whole new tables created. In this
manner, there is a good chance that DAO could be faster.

Will you notice the difference? If you're not working with
millions of records, you'll likely not see any difference. I
myself, don't like messing with ADO. I like DAO and I
love Microsoft Access. But the two are so similar, that
I really don't see a difference. A connection is always
needed to use the recordset (the same as the database
is always needed to use the recordset).

Private adoPrimaryRS As ADODB.Recordset

Private Sub Form_Load()
Dim db As ADODB.Connection, sConn As String, sRS As String
Set db = New ADODB.Connection
sConn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mail\test.mdb;"
sRS = "select age,id,namefirst,namelast from tPeople Order by namelast"
db.CursorLocation = adUseClient
db.Open sConn

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open sRS, db, adOpenStatic, adLockOptimistic
End Sub

Now, this is the default class ADO that VB6 creates with their
wizard. I wouldn't code it that way myself, that's just Microsoft
programmers that coded it like that.

In DAO, it would be as follows:

Private db As DAO.Database
Private rs As DAO.Recordset

Private Sub Form_Load()
Dim sDB As String
Set db = Database.Open("C:\mail\test.mdb")
Set rs = db.OpenRecordset("tPeople")
End Sub

Private Sub Form_Unload()
If IsObject(db) Then
db.Close
Set db = Nothing
End If
If IsObject(rs) Then
rs.Close
Set rs = Nothing
End If
End Sub

And it's not too difficult to raise an event inside of a
class module...

RaiseEvent MoveComplete

:-)

--
Jim Carlock
Post replies to newsgroup.

"david epsom dot com dot au" wrote:
nagging doubt that it was created without good reason, and

MS had the choice of extending DAO, or creating a new object.
They chose to create a new object: the design of the new
object is more comfortable for C++ programmers, and less
comfortable for Basic programmers.

A DAO database has tabledefs, which have fields. This provides
a method of data access which does not involve SQL. MS wanted
to provide this kind of access to general data. DAO has a
connection syntax which allows it to connect to Jet IISAM
data suppliers (like the 'text IISAM' or the 'Excel IISAM')
or to any ODBC data supplier. MS wanted an object which
could be connected to an OLEDB data supplier. OLE is a kind
of standard Microsoft connection technology. MS could have
re-written and extended DAO to support the new kind of connection,
but instead they chose to create a new kind of object.

Of course it by re-writing and extending DAO, they changed
it and broke old code, there would have been many unhappy
people.

A DAO dbengine object has a 'createworkspace' method:
a workspace has an 'opendatabase' method: a database has
an 'openrecordset' method. That means that there is both
a forward and a backward interface from dbengine to recordset,
and from recordset to dbengine.

This is a convenient paradigm for users: you can start with
a dbengine and there is a method which leads you to a recordset:
you can start with a recordset and there are properties which
lead you back to the dbengine.

But it is a design paradigm which is disliked by the people who
have to design and support the object. In object design, you want
to create objects with 'high cohesion and low coupling': you want
each object to know as little about any other kind of object as
is possible, so that changes or bugs in objects don't affect other
objects.

In ADO, a connection object doesn't have an 'openrecordset'
method. Instead, you might create a recordset object, and
create a connection object, and then connect up the recordset
object and the connection object. This is the kind of behaviour
I would have in any object which I wrote myself, and it is
the kind of behaviour people who write objects expect to see
and feel comfortable with.

So there you have it: ADO, a data object technology that
supports OLEDB, using a popular object design paradigm.
More recent than DAO, with newer features (XML), and
some lack of overlap, where feature implementation is
not exactly the same.

Does that make ADO or DAO better? ADO, absolutely, if you
want newer features, support for OLEDB, and a particular
object design paradigm....

(david)

"raylitalo" raylitalo wrote:
I have been using DAO for quite a number of years now, in both
Visual Basic and in MS Access. In each new project I start now,
I quietly ask myself if I should concider the time it would take to
learn ADO. The little I've tried it out (without cracking a book)
I found no advantages to ADO, but I have a nagging doubt that
it was created without good reason, and am wondering if
anyone has strong opinions as to which is better, and why?

Thank you,

Ross

Hi Brendan,

Thanks for clarifying that. I've not used XML much. It could
serve some future purpose for me.

Could you do me a favor and provide an example of using
XML. I'm thinking along the lines that the filename is thrown
in there in some manner and as such, represents the
connection.

After typing up that last night, I saw a couple mistakes with
what I typed in:

Private adoPrimaryRS As ADODB.Recordset

should read as:

Private WithEvents adoPrimaryRS As ADODB.Recordset

There was something less noticeable as well, but I'm not
seeing it at the moment.

Thanks.

--
Jim Carlock
Post replies to newsgroup.

"Brendan Reynolds" brenreyn at indigo dot ie wrote:
I don't usually enter into these discussions any more, because ADO vs. DAO
was something of a 'religious war' issue some years back, and it got to be
pretty boring listening to some people frothing at the mouth over it (not
that anyone is doing that in this thread, I hasten to add! :-)

In the interests of accuracy, though, I want to point out that an ADO
recordset does not *always* need an ADO connection. For example, no
connection is required if the source of the Recordset is an XML file. And in
addition to security, another use of the DAO Workspace object is to
implement transactions.

Personally, I don't mind using either ADO or DAO for most data manipulation,
but I much prefer DAO over ADOX for data definition.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"Jim Carlock" wrote in message
...
Hi David and Raylito,

These are the differences I see when using ADO with
Access versus DAO with Access.

Benefits for ADO:
ADO provides a way to use Events.

Against ADO:
It IS a little slower. I'll get to this at the end.

An Example:
Private WithEvents adbADODB As ADODB.Recordset

Where you can NOT declare DAO withevents.
Private adbDAO As DAO.Recordset

What events are missing? And what are events? And how
are they used?

If the record is being navigated, you will get a MoveComplete
event. It's like the mouse clicks on listboxes or comboboxes,
the Click event fires when you click inside a combobox.

This makes the ADO more useful, because you don't
have to create hacks and create your own events. They are
already there and can be raised by the base class so that
outer classes and/or forms can see them and then update
something accordingly. The example that Microsoft inside
the VB data wizard uses the MoveComplete event to
update the text within a label and give a visual notification
of the record number currently being looked at.

By default, VB6 automatically uses ADO controls inside
of their Form wizards. If you create a new database form
using the wizard, you will be asked if you want to use a
Class, an ADO Data Control or ADO code. If you
choose VB to create to the class, you will see how the
events are used and you will see the full selection of
events.

I'm not too familiar with OLEDB. As far as know, OLE
means ActiveX and ActiveX means multiple threads, or
a .dll or an .exe that exposes certain classes and/or events.

One last thing about the explanation that David provided
with Connection / Recordset, I must comment upon.

It IS naturally convenient to open a database before you
open the recordset. The ONLY thing I think of an ADODB
connection as is as a connection to the database. I think
David is flawed in describing it the way he did. But
perhaps I'm stuck in my old DAO ways. g

In ADODB, you ALWAYS set up some sort of connection
to the file being connected to. In DAO, it was the DBEngine
Workspace that provided the means to supply user
identification, where in ADODB it is provided through the
Connection. Unless you're providing secure access to the
database, you will NEVER have a need to use the DAO
Workspace (if I'm wrong, someone let me know).

Either way, I think of it in this manner. The Connection in
ADODB is the Database in DAO. And each is required.
I would never use an ADODB.Recordset without using
an ADODB.Connection. The connection provides the
path the database. The database (data-engine) holds
the tables (recordsets) inside of it. They are both needed.

When I create an ADODB.Connection, I make sure I
destroy that object as well. The same should be done
with DAO as well.

What does this mean as far as cost goes? The cost of
ADODB is that tends to be slower, but you won't notice
it if you don't have a big enough recordset, or don't do
enough processing. So is that a cost? And when it comes
to other types of files, DAO is only made to access .mdb
but there are ways around that. .mdb's can be made to
connect via ODBC and whole tables be brought across
inside of Access, and whole new tables created. In this
manner, there is a good chance that DAO could be faster.

Will you notice the difference? If you're not working with
millions of records, you'll likely not see any difference. I
myself, don't like messing with ADO. I like DAO and I
love Microsoft Access. But the two are so similar, that
I really don't see a difference. A connection is always
needed to use the recordset (the same as the database
is always needed to use the recordset).

Private adoPrimaryRS As ADODB.Recordset

Private Sub Form_Load()
Dim db As ADODB.Connection, sConn As String, sRS As String
Set db = New ADODB.Connection
sConn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mail\test.mdb;"
sRS = "select age,id,namefirst,namelast from tPeople Order by namelast"
db.CursorLocation = adUseClient
db.Open sConn

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open sRS, db, adOpenStatic, adLockOptimistic
End Sub

Now, this is the default class ADO that VB6 creates with their
wizard. I wouldn't code it that way myself, that's just Microsoft
programmers that coded it like that.

In DAO, it would be as follows:

Private db As DAO.Database
Private rs As DAO.Recordset

Private Sub Form_Load()
Dim sDB As String
Set db = Database.Open("C:\mail\test.mdb")
Set rs = db.OpenRecordset("tPeople")
End Sub

Private Sub Form_Unload()
If IsObject(db) Then
db.Close
Set db = Nothing
End If
If IsObject(rs) Then
rs.Close
Set rs = Nothing
End If
End Sub

And it's not too difficult to raise an event inside of a
class module...

RaiseEvent MoveComplete

:-)

--
Jim Carlock
Post replies to newsgroup.

"david epsom dot com dot au" wrote:
nagging doubt that it was created without good reason, and

MS had the choice of extending DAO, or creating a new object.
They chose to create a new object: the design of the new
object is more comfortable for C++ programmers, and less
comfortable for Basic programmers.

A DAO database has tabledefs, which have fields. This provides
a method of data access which does not involve SQL. MS wanted
to provide this kind of access to general data. DAO has a
connection syntax which allows it to connect to Jet IISAM
data suppliers (like the 'text IISAM' or the 'Excel IISAM')
or to any ODBC data supplier. MS wanted an object which
could be connected to an OLEDB data supplier. OLE is a kind
of standard Microsoft connection technology. MS could have
re-written and extended DAO to support the new kind of connection,
but instead they chose to create a new kind of object.

Of course it by re-writing and extending DAO, they changed
it and broke old code, there would have been many unhappy
people.

A DAO dbengine object has a 'createworkspace' method:
a workspace has an 'opendatabase' method: a database has
an 'openrecordset' method. That means that there is both
a forward and a backward interface from dbengine to recordset,
and from recordset to dbengine.

This is a convenient paradigm for users: you can start with
a dbengine and there is a method which leads you to a recordset:
you can start with a recordset and there are properties which
lead you back to the dbengine.

But it is a design paradigm which is disliked by the people who
have to design and support the object. In object design, you want
to create objects with 'high cohesion and low coupling': you want
each object to know as little about any other kind of object as
is possible, so that changes or bugs in objects don't affect other
objects.

In ADO, a connection object doesn't have an 'openrecordset'
method. Instead, you might create a recordset object, and
create a connection object, and then connect up the recordset
object and the connection object. This is the kind of behaviour
I would have in any object which I wrote myself, and it is
the kind of behaviour people who write objects expect to see
and feel comfortable with.

So there you have it: ADO, a data object technology that
supports OLEDB, using a popular object design paradigm.
More recent than DAO, with newer features (XML), and
some lack of overlap, where feature implementation is
not exactly the same.

Does that make ADO or DAO better? ADO, absolutely, if you
want newer features, support for OLEDB, and a particular
object design paradigm....

(david)

"raylitalo" raylitalo wrote:
I have been using DAO for quite a number of years now, in both
Visual Basic and in MS Access. In each new project I start now,
I quietly ask myself if I should concider the time it would take to
learn ADO. The little I've tried it out (without cracking a book)
I found no advantages to ADO, but I have a nagging doubt that
it was created without good reason, and am wondering if
anyone has strong opinions as to which is better, and why?

Thank you,

Ross

Here's an example that saves the contents of a recordset to an XML file,
then opens another recordset using that XML file as the source. You'll
notice that when using the XML file as the source (in the second procedure
below) the file name is passed as the first, 'Source' argument of the open
method. Nothing is ever passed to the second, 'ActiveConnection' argument,
or assigned to the ActiveConnection property. So the recordset really does
have no *explicit* connection. On reflection, though, I suppose that it is
possible, perhaps even probable, that ADO may be creating an implicit
connection.

Public Sub SaveRecordset()

Dim rst As New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.Open "SELECT CategoryID, CategoryName, Description FROM Categories"
.ActiveConnection = Nothing

'After running this procedure once, you'll need to uncomment the
following commented
'lines, or manually delete the Categories.xml file, if you want to
run it again.
'On Error Resume Next
'Kill CurrentProject.Path & "\Categories.xml"
'On Error GoTo 0
.Save CurrentProject.Path & "\Categories.xml", adPersistXML
.Close
End With

End Sub

Public Sub CountRecords()

Dim rst As New ADODB.Recordset

rst.Open CurrentProject.Path & "\Categories.xml"
Debug.Print rst.RecordCount
rst.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"Jim Carlock" wrote in message
...
Hi Brendan,

Thanks for clarifying that. I've not used XML much. It could
serve some future purpose for me.

Could you do me a favor and provide an example of using
XML. I'm thinking along the lines that the filename is thrown
in there in some manner and as such, represents the
connection.

After typing up that last night, I saw a couple mistakes with
what I typed in:

Private adoPrimaryRS As ADODB.Recordset

should read as:

Private WithEvents adoPrimaryRS As ADODB.Recordset

There was something less noticeable as well, but I'm not
seeing it at the moment.

Thanks.

--
Jim Carlock
Post replies to newsgroup.

"Brendan Reynolds" brenreyn at indigo dot ie wrote:
I don't usually enter into these discussions any more, because ADO vs. DAO
was something of a 'religious war' issue some years back, and it got to be
pretty boring listening to some people frothing at the mouth over it (not
that anyone is doing that in this thread, I hasten to add! :-)

In the interests of accuracy, though, I want to point out that an ADO
recordset does not *always* need an ADO connection. For example, no
connection is required if the source of the Recordset is an XML file. And
in
addition to security, another use of the DAO Workspace object is to
implement transactions.

Personally, I don't mind using either ADO or DAO for most data
manipulation,
but I much prefer DAO over ADOX for data definition.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"Jim Carlock" wrote in message
...
Hi David and Raylito,

These are the differences I see when using ADO with
Access versus DAO with Access.

Benefits for ADO:
ADO provides a way to use Events.

Against ADO:
It IS a little slower. I'll get to this at the end.

An Example:
Private WithEvents adbADODB As ADODB.Recordset

Where you can NOT declare DAO withevents.
Private adbDAO As DAO.Recordset

What events are missing? And what are events? And how
are they used?

If the record is being navigated, you will get a MoveComplete
event. It's like the mouse clicks on listboxes or comboboxes,
the Click event fires when you click inside a combobox.

This makes the ADO more useful, because you don't
have to create hacks and create your own events. They are
already there and can be raised by the base class so that
outer classes and/or forms can see them and then update
something accordingly. The example that Microsoft inside
the VB data wizard uses the MoveComplete event to
update the text within a label and give a visual notification
of the record number currently being looked at.

By default, VB6 automatically uses ADO controls inside
of their Form wizards. If you create a new database form
using the wizard, you will be asked if you want to use a
Class, an ADO Data Control or ADO code. If you
choose VB to create to the class, you will see how the
events are used and you will see the full selection of
events.

I'm not too familiar with OLEDB. As far as know, OLE
means ActiveX and ActiveX means multiple threads, or
a .dll or an .exe that exposes certain classes and/or events.

One last thing about the explanation that David provided
with Connection / Recordset, I must comment upon.

It IS naturally convenient to open a database before you
open the recordset. The ONLY thing I think of an ADODB
connection as is as a connection to the database. I think
David is flawed in describing it the way he did. But
perhaps I'm stuck in my old DAO ways. g

In ADODB, you ALWAYS set up some sort of connection
to the file being connected to. In DAO, it was the DBEngine
Workspace that provided the means to supply user
identification, where in ADODB it is provided through the
Connection. Unless you're providing secure access to the
database, you will NEVER have a need to use the DAO
Workspace (if I'm wrong, someone let me know).

Either way, I think of it in this manner. The Connection in
ADODB is the Database in DAO. And each is required.
I would never use an ADODB.Recordset without using
an ADODB.Connection. The connection provides the
path the database. The database (data-engine) holds
the tables (recordsets) inside of it. They are both needed.

When I create an ADODB.Connection, I make sure I
destroy that object as well. The same should be done
with DAO as well.

What does this mean as far as cost goes? The cost of
ADODB is that tends to be slower, but you won't notice
it if you don't have a big enough recordset, or don't do
enough processing. So is that a cost? And when it comes
to other types of files, DAO is only made to access .mdb
but there are ways around that. .mdb's can be made to
connect via ODBC and whole tables be brought across
inside of Access, and whole new tables created. In this
manner, there is a good chance that DAO could be faster.

Will you notice the difference? If you're not working with
millions of records, you'll likely not see any difference. I
myself, don't like messing with ADO. I like DAO and I
love Microsoft Access. But the two are so similar, that
I really don't see a difference. A connection is always
needed to use the recordset (the same as the database
is always needed to use the recordset).

Private adoPrimaryRS As ADODB.Recordset

Private Sub Form_Load()
Dim db As ADODB.Connection, sConn As String, sRS As String
Set db = New ADODB.Connection
sConn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mail\test.mdb;"
sRS = "select age,id,namefirst,namelast from tPeople Order by namelast"
db.CursorLocation = adUseClient
db.Open sConn

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open sRS, db, adOpenStatic, adLockOptimistic
End Sub

Now, this is the default class ADO that VB6 creates with their
wizard. I wouldn't code it that way myself, that's just Microsoft
programmers that coded it like that.

In DAO, it would be as follows:

Private db As DAO.Database
Private rs As DAO.Recordset

Private Sub Form_Load()
Dim sDB As String
Set db = Database.Open("C:\mail\test.mdb")
Set rs = db.OpenRecordset("tPeople")
End Sub

Private Sub Form_Unload()
If IsObject(db) Then
db.Close
Set db = Nothing
End If
If IsObject(rs) Then
rs.Close
Set rs = Nothing
End If
End Sub

And it's not too difficult to raise an event inside of a
class module...

RaiseEvent MoveComplete

:-)

--
Jim Carlock
Post replies to newsgroup.

"david epsom dot com dot au" wrote:
nagging doubt that it was created without good reason, and

MS had the choice of extending DAO, or creating a new object.
They chose to create a new object: the design of the new
object is more comfortable for C++ programmers, and less
comfortable for Basic programmers.

A DAO database has tabledefs, which have fields. This provides
a method of data access which does not involve SQL. MS wanted
to provide this kind of access to general data. DAO has a
connection syntax which allows it to connect to Jet IISAM
data suppliers (like the 'text IISAM' or the 'Excel IISAM')
or to any ODBC data supplier. MS wanted an object which
could be connected to an OLEDB data supplier. OLE is a kind
of standard Microsoft connection technology. MS could have
re-written and extended DAO to support the new kind of connection,
but instead they chose to create a new kind of object.

Of course it by re-writing and extending DAO, they changed
it and broke old code, there would have been many unhappy
people.

A DAO dbengine object has a 'createworkspace' method:
a workspace has an 'opendatabase' method: a database has
an 'openrecordset' method. That means that there is both
a forward and a backward interface from dbengine to recordset,
and from recordset to dbengine.

This is a convenient paradigm for users: you can start with
a dbengine and there is a method which leads you to a recordset:
you can start with a recordset and there are properties which
lead you back to the dbengine.

But it is a design paradigm which is disliked by the people who
have to design and support the object. In object design, you want
to create objects with 'high cohesion and low coupling': you want
each object to know as little about any other kind of object as
is possible, so that changes or bugs in objects don't affect other
objects.

In ADO, a connection object doesn't have an 'openrecordset'
method. Instead, you might create a recordset object, and
create a connection object, and then connect up the recordset
object and the connection object. This is the kind of behaviour
I would have in any object which I wrote myself, and it is
the kind of behaviour people who write objects expect to see
and feel comfortable with.

So there you have it: ADO, a data object technology that
supports OLEDB, using a popular object design paradigm.
More recent than DAO, with newer features (XML), and
some lack of overlap, where feature implementation is
not exactly the same.

Does that make ADO or DAO better? ADO, absolutely, if you
want newer features, support for OLEDB, and a particular
object design paradigm....

(david)

"raylitalo" raylitalo wrote:
I have been using DAO for quite a number of years now, in both
Visual Basic and in MS Access. In each new project I start now,
I quietly ask myself if I should concider the time it would take to
learn ADO. The little I've tried it out (without cracking a book)
I found no advantages to ADO, but I have a nagging doubt that
it was created without good reason, and am wondering if
anyone has strong opinions as to which is better, and why?

Thank you,

Ross

Dear all,

A DAO dbengine object has a CreateDatabase() method.
But What is the function to create an Access database in ADO?

I want to create a new MDB file in using ADO C++.

--
Regards,
SCM
Programmer


"Jim Carlock" wrote:

Hi David and Raylito,

These are the differences I see when using ADO with
Access versus DAO with Access.

Benefits for ADO:
ADO provides a way to use Events.

Against ADO:
It IS a little slower. I'll get to this at the end.

An Example:
Private WithEvents adbADODB As ADODB.Recordset

Where you can NOT declare DAO withevents.
Private adbDAO As DAO.Recordset

What events are missing? And what are events? And how
are they used?

If the record is being navigated, you will get a MoveComplete
event. It's like the mouse clicks on listboxes or comboboxes,
the Click event fires when you click inside a combobox.

This makes the ADO more useful, because you don't
have to create hacks and create your own events. They are
already there and can be raised by the base class so that
outer classes and/or forms can see them and then update
something accordingly. The example that Microsoft inside
the VB data wizard uses the MoveComplete event to
update the text within a label and give a visual notification
of the record number currently being looked at.

By default, VB6 automatically uses ADO controls inside
of their Form wizards. If you create a new database form
using the wizard, you will be asked if you want to use a
Class, an ADO Data Control or ADO code. If you
choose VB to create to the class, you will see how the
events are used and you will see the full selection of
events.

I'm not too familiar with OLEDB. As far as know, OLE
means ActiveX and ActiveX means multiple threads, or
a .dll or an .exe that exposes certain classes and/or events.

One last thing about the explanation that David provided
with Connection / Recordset, I must comment upon.

It IS naturally convenient to open a database before you
open the recordset. The ONLY thing I think of an ADODB
connection as is as a connection to the database. I think
David is flawed in describing it the way he did. But
perhaps I'm stuck in my old DAO ways. g

In ADODB, you ALWAYS set up some sort of connection
to the file being connected to. In DAO, it was the DBEngine
Workspace that provided the means to supply user
identification, where in ADODB it is provided through the
Connection. Unless you're providing secure access to the
database, you will NEVER have a need to use the DAO
Workspace (if I'm wrong, someone let me know).

Either way, I think of it in this manner. The Connection in
ADODB is the Database in DAO. And each is required.
I would never use an ADODB.Recordset without using
an ADODB.Connection. The connection provides the
path the database. The database (data-engine) holds
the tables (recordsets) inside of it. They are both needed.

When I create an ADODB.Connection, I make sure I
destroy that object as well. The same should be done
with DAO as well.

What does this mean as far as cost goes? The cost of
ADODB is that tends to be slower, but you won't notice
it if you don't have a big enough recordset, or don't do
enough processing. So is that a cost? And when it comes
to other types of files, DAO is only made to access .mdb
but there are ways around that. .mdb's can be made to
connect via ODBC and whole tables be brought across
inside of Access, and whole new tables created. In this
manner, there is a good chance that DAO could be faster.

Will you notice the difference? If you're not working with
millions of records, you'll likely not see any difference. I
myself, don't like messing with ADO. I like DAO and I
love Microsoft Access. But the two are so similar, that
I really don't see a difference. A connection is always
needed to use the recordset (the same as the database
is always needed to use the recordset).

Private adoPrimaryRS As ADODB.Recordset

Private Sub Form_Load()
Dim db As ADODB.Connection, sConn As String, sRS As String
Set db = New ADODB.Connection
sConn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mail\test.mdb;"
sRS = "select age,id,namefirst,namelast from tPeople Order by namelast"
db.CursorLocation = adUseClient
db.Open sConn

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open sRS, db, adOpenStatic, adLockOptimistic
End Sub

Now, this is the default class ADO that VB6 creates with their
wizard. I wouldn't code it that way myself, that's just Microsoft
programmers that coded it like that.

In DAO, it would be as follows:

Private db As DAO.Database
Private rs As DAO.Recordset

Private Sub Form_Load()
Dim sDB As String
Set db = Database.Open("C:\mail\test.mdb")
Set rs = db.OpenRecordset("tPeople")
End Sub

Private Sub Form_Unload()
If IsObject(db) Then
db.Close
Set db = Nothing
End If
If IsObject(rs) Then
rs.Close
Set rs = Nothing
End If
End Sub

And it's not too difficult to raise an event inside of a
class module...

RaiseEvent MoveComplete

:-)

--
Jim Carlock
Post replies to newsgroup.

"david epsom dot com dot au" wrote:
nagging doubt that it was created without good reason, and

MS had the choice of extending DAO, or creating a new object.
They chose to create a new object: the design of the new
object is more comfortable for C++ programmers, and less
comfortable for Basic programmers.

A DAO database has tabledefs, which have fields. This provides
a method of data access which does not involve SQL. MS wanted
to provide this kind of access to general data. DAO has a
connection syntax which allows it to connect to Jet IISAM
data suppliers (like the 'text IISAM' or the 'Excel IISAM')
or to any ODBC data supplier. MS wanted an object which
could be connected to an OLEDB data supplier. OLE is a kind
of standard Microsoft connection technology. MS could have
re-written and extended DAO to support the new kind of connection,
but instead they chose to create a new kind of object.

Of course it by re-writing and extending DAO, they changed
it and broke old code, there would have been many unhappy
people.

A DAO dbengine object has a 'createworkspace' method:
a workspace has an 'opendatabase' method: a database has
an 'openrecordset' method. That means that there is both
a forward and a backward interface from dbengine to recordset,
and from recordset to dbengine.

This is a convenient paradigm for users: you can start with
a dbengine and there is a method which leads you to a recordset:
you can start with a recordset and there are properties which
lead you back to the dbengine.

But it is a design paradigm which is disliked by the people who
have to design and support the object. In object design, you want
to create objects with 'high cohesion and low coupling': you want
each object to know as little about any other kind of object as
is possible, so that changes or bugs in objects don't affect other
objects.

In ADO, a connection object doesn't have an 'openrecordset'
method. Instead, you might create a recordset object, and
create a connection object, and then connect up the recordset
object and the connection object. This is the kind of behaviour
I would have in any object which I wrote myself, and it is
the kind of behaviour people who write objects expect to see
and feel comfortable with.

So there you have it: ADO, a data object technology that
supports OLEDB, using a popular object design paradigm.
More recent than DAO, with newer features (XML), and
some lack of overlap, where feature implementation is
not exactly the same.

Does that make ADO or DAO better? ADO, absolutely, if you
want newer features, support for OLEDB, and a particular
object design paradigm....

(david)

"raylitalo" raylitalo wrote:
I have been using DAO for quite a number of years now, in both
Visual Basic and in MS Access. In each new project I start now,
I quietly ask myself if I should concider the time it would take to
learn ADO. The little I've tried it out (without cracking a book)
I found no advantages to ADO, but I have a nagging doubt that
it was created without good reason, and am wondering if
anyone has strong opinions as to which is better, and why?

Thank you,

Ross