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 said:
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)
