ADO vs DAO 
Author Message
 ADO vs DAO

Hi All,

I am currently working on an application using VB6.0 that uses a Microsoft
Access database. The application has extensive database use and is currently
using DAO. I am aware that DAO is an "Older technology" than ADO but will I
see a significant difference if I convert the code to use ADO?

Can anyone point me to a resource/site where the 2 are compared? What are
the main differences?
All opinions are greatly appreciated!

Thanks in advance,

Brian Flynn

--
Brian Flynn
Dymaxion Research Ltd.



Sun, 22 Sep 2002 03:00:00 GMT  
 ADO vs DAO
http://msdn.microsoft.com/library/techart/daotoadoupdate.htm

--
Andrew Grillage
http://vbdata.iwarp.com


Quote:
> Hi All,

> I am currently working on an application using VB6.0 that uses a Microsoft
> Access database. The application has extensive database use and is
currently
> using DAO. I am aware that DAO is an "Older technology" than ADO but will
I
> see a significant difference if I convert the code to use ADO?

> Can anyone point me to a resource/site where the 2 are compared? What are
> the main differences?
> All opinions are greatly appreciated!

> Thanks in advance,

> Brian Flynn

> --
> Brian Flynn
> Dymaxion Research Ltd.




Sun, 22 Sep 2002 03:00:00 GMT  
 ADO vs DAO
Brian,

go to http://msdn.microsoft.com and use in search "dao x ado". Youll see at
least two good articles.

Jairo Marques


Quote:
> Hi All,

> I am currently working on an application using VB6.0 that uses a Microsoft
> Access database. The application has extensive database use and is
currently
> using DAO. I am aware that DAO is an "Older technology" than ADO but will
I
> see a significant difference if I convert the code to use ADO?

> Can anyone point me to a resource/site where the 2 are compared? What are
> the main differences?
> All opinions are greatly appreciated!

> Thanks in advance,

> Brian Flynn

> --
> Brian Flynn
> Dymaxion Research Ltd.




Sun, 22 Sep 2002 03:00:00 GMT  
 ADO vs DAO
I suggest you do some benchmarking on your existing databases. If you
have lots of records and 15 or more fields, you may find ADO recordset
creation quite slow relative to DAO. I'm new to ADO so the code shown
below may not be optimized for speed, but I was able to use it to verify
that ADO would not introduce large time delays for an app that I'm
developing. But, I was also able to verify that, on large recordsets,
DAO can be 10 or more times faster.

Private Sub cmdAdoRS_Click()

  Set cnn = New ADODB.Connection
  cnn.CursorLocation = adUseClient
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path &
"\gcp.mdb"

  Set rsADO = New ADODB.Recordset
  With rsADO
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
  End With

  lngStart = GetTickCount()
'  rsADO.Open "SELECT * FROM hydrants where hyd_num = '" & 1000 & "'",
cnn
  rsADO.Open "SELECT * FROM hydrants", cnn
  Set DataGrid2.DataSource = rsADO
  lngStop = GetTickCount()
  Text1 = CStr(lngStop - lngStart)
End Sub

Private Sub cmdDAO_Click()
  Dim db As DAO.Database
  Dim rsDAO As DAO.Recordset

  lngStart = GetTickCount()
  Set db = OpenDatabase(App.Path & "\gcp.mdb")
  Set rsDAO = db.OpenRecordset("hydrants")
  Set Data1.Recordset = rsDAO
  Data1.Refresh
  lngStop = GetTickCount()
  Text2 = CStr(lngStop - lngStart)
End Sub



Sun, 22 Sep 2002 03:00:00 GMT  
 ADO vs DAO
Like I said, ADO is new to me. I experimented with my code and found
that adUseClient always returns an adOpenStatic cursor. Of course, this
is pointed out several times in Andrew Grillage's ADO FAQ. I just need
to reread more often.

However, I would be interested in learning more about how it is possible
to have two local cursors associated with one recordset.


Quote:
> Two things....

> A similar discussion is at

http://news.devx.com/cgi-bin/dnewsweb.exe?cmd=article&group=talk.editors
.vbp
Quote:
> j&item=5615&utag=
> I'm not sure if you need to be a member to get this so apols in
advance if
> so.

> Secondly, my understanding of any access to .mdb is that a local
cursor is
> created as a matter of course by the Jet/OLEDB driver.
> Using an adStatic ADO cursor creates another local cursor ie two
cursors on
> the one PC for one set of data.  This can't be good.
> Please correct me if I'm wrong.

> OTOH a static cursor to a SQL data source makes sense, especially when
> disconnected functionality is required.

> Some obvious reasons for DAO as opposed to ADO:

> - Jet easier/smaller to distribute (no 6+ MB mdac_typ.exe)
> - In spite of the above, I've found DAO _marginally_ faster than the
> ADO/Jet.OLEDB.4.0 combination if both approaches are coded
>   sensibly
> - DAO doesn't change every 2 months ;-)

> Still, where practical I use ADO just for asynchronous processing.

> In summary, Ihaven't made up my mind yet ;-)  But I'm not convinced
> performance is as big a factor as it's made out.

> --
> Adios,

> Vic Djajamihardja
> vic-at-jaratech-dot-com-dot-au


message

> > I suggest you do some benchmarking on your existing databases. If
you
> > have lots of records and 15 or more fields, you may find ADO
recordset
> > creation quite slow relative to DAO. I'm new to ADO so the code
shown
> > below may not be optimized for speed, but I was able to use it to
verify
> > that ADO would not introduce large time delays for an app that I'm
> > developing. But, I was also able to verify that, on large
recordsets,
> > DAO can be 10 or more times faster.

> > Private Sub cmdAdoRS_Click()

> >   Set cnn = New ADODB.Connection
> >   cnn.CursorLocation = adUseClient
> >   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
App.Path &
> > "\gcp.mdb"

> >   Set rsADO = New ADODB.Recordset
> >   With rsADO
> >     .CursorType = adOpenStatic
> >     .LockType = adLockOptimistic
> >   End With

> >   lngStart = GetTickCount()
> > '  rsADO.Open "SELECT * FROM hydrants where hyd_num = '" & 1000 &
"'",
> > cnn
> >   rsADO.Open "SELECT * FROM hydrants", cnn
> >   Set DataGrid2.DataSource = rsADO
> >   lngStop = GetTickCount()
> >   Text1 = CStr(lngStop - lngStart)
> > End Sub

> > Private Sub cmdDAO_Click()
> >   Dim db As DAO.Database
> >   Dim rsDAO As DAO.Recordset

> >   lngStart = GetTickCount()
> >   Set db = OpenDatabase(App.Path & "\gcp.mdb")
> >   Set rsDAO = db.OpenRecordset("hydrants")
> >   Set Data1.Recordset = rsDAO
> >   Data1.Refresh
> >   lngStop = GetTickCount()
> >   Text2 = CStr(lngStop - lngStart)
> > End Sub



Sun, 22 Sep 2002 03:00:00 GMT  
 ADO vs DAO


Quote:
> Two things....

> A similar discussion is at

http://news.devx.com/cgi-bin/dnewsweb.exe?cmd=article&group=talk.edit...

Quote:
> j&item=5615&utag=
> I'm not sure if you need to be a member to get this so apols in advance if
> so.

No membership required there.

Quote:
> Secondly, my understanding of any access to .mdb is that a local cursor is
> created as a matter of course by the Jet/OLEDB driver.
> Using an adStatic ADO cursor creates another local cursor ie two cursors
on
> the one PC for one set of data.  This can't be good.
> Please correct me if I'm wrong.

No this does not sound right to me. If you use ADO to access a Jet database
then you specify whatever cursor type you wish to have. You will not
necessarily get given that cursor since Jet does not support all cursor
types but the default is server side not client side. And using a static
cursor does not automatically mean a client cursor either ( the other way
round, yes ). Also, when opening a recordset you get one cursor. You cannot
have two.

Quote:

> OTOH a static cursor to a SQL data source makes sense, especially when
> disconnected functionality is required.

> Some obvious reasons for DAO as opposed to ADO:

> - Jet easier/smaller to distribute (no 6+ MB mdac_typ.exe)

These days, distributing Jet is distributing MDAC.

Quote:
> - In spite of the above, I've found DAO _marginally_ faster than the
> ADO/Jet.OLEDB.4.0 combination if both approaches are coded
>   sensibly

True.

Quote:
> - DAO doesn't change every 2 months ;-)

Niether does ADO. MDAC is updated regularly but Jet and DAO are part of MDAC
too.

Quote:

> Still, where practical I use ADO just for asynchronous processing.

> In summary, Ihaven't made up my mind yet ;-)  But I'm not convinced
> performance is as big a factor as it's made out.

This is certainly true in most cases.

--
Andrew Grillage
http://vbdata.iwarp.com



Sun, 22 Sep 2002 03:00:00 GMT  
 ADO vs DAO
Two things....

A similar discussion is at
http://news.devx.com/cgi-bin/dnewsweb.exe?cmd=article&group=talk.edit...
j&item=5615&utag=
I'm not sure if you need to be a member to get this so apols in advance if
so.

Secondly, my understanding of any access to .mdb is that a local cursor is
created as a matter of course by the Jet/OLEDB driver.
Using an adStatic ADO cursor creates another local cursor ie two cursors on
the one PC for one set of data.  This can't be good.
Please correct me if I'm wrong.

OTOH a static cursor to a SQL data source makes sense, especially when
disconnected functionality is required.

Some obvious reasons for DAO as opposed to ADO:

- Jet easier/smaller to distribute (no 6+ MB mdac_typ.exe)
- In spite of the above, I've found DAO _marginally_ faster than the
ADO/Jet.OLEDB.4.0 combination if both approaches are coded
  sensibly
- DAO doesn't change every 2 months ;-)

Still, where practical I use ADO just for asynchronous processing.

In summary, Ihaven't made up my mind yet ;-)  But I'm not convinced
performance is as big a factor as it's made out.

--
Adios,

Vic Djajamihardja
vic-at-jaratech-dot-com-dot-au


Quote:
> I suggest you do some benchmarking on your existing databases. If you
> have lots of records and 15 or more fields, you may find ADO recordset
> creation quite slow relative to DAO. I'm new to ADO so the code shown
> below may not be optimized for speed, but I was able to use it to verify
> that ADO would not introduce large time delays for an app that I'm
> developing. But, I was also able to verify that, on large recordsets,
> DAO can be 10 or more times faster.

> Private Sub cmdAdoRS_Click()

>   Set cnn = New ADODB.Connection
>   cnn.CursorLocation = adUseClient
>   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path &
> "\gcp.mdb"

>   Set rsADO = New ADODB.Recordset
>   With rsADO
>     .CursorType = adOpenStatic
>     .LockType = adLockOptimistic
>   End With

>   lngStart = GetTickCount()
> '  rsADO.Open "SELECT * FROM hydrants where hyd_num = '" & 1000 & "'",
> cnn
>   rsADO.Open "SELECT * FROM hydrants", cnn
>   Set DataGrid2.DataSource = rsADO
>   lngStop = GetTickCount()
>   Text1 = CStr(lngStop - lngStart)
> End Sub

> Private Sub cmdDAO_Click()
>   Dim db As DAO.Database
>   Dim rsDAO As DAO.Recordset

>   lngStart = GetTickCount()
>   Set db = OpenDatabase(App.Path & "\gcp.mdb")
>   Set rsDAO = db.OpenRecordset("hydrants")
>   Set Data1.Recordset = rsDAO
>   Data1.Refresh
>   lngStop = GetTickCount()
>   Text2 = CStr(lngStop - lngStart)
> End Sub



Mon, 23 Sep 2002 03:00:00 GMT  
 ADO vs DAO
My experience is that there is a lot more functionality using ADO but not
enough for me to convert what I have. If I started something new I would
definitely go ADO. I have tested ADO and DAO on an access database (about 50
mg) just tables no forms etc. and DAO processed 5 tables and various other
VB functions about 5-6 times faster than a very simple ADO connection
connected to one table in the database.

Bruce


Quote:
> Hi All,

> I am currently working on an application using VB6.0 that uses a Microsoft
> Access database. The application has extensive database use and is
currently
> using DAO. I am aware that DAO is an "Older technology" than ADO but will
I
> see a significant difference if I convert the code to use ADO?

> Can anyone point me to a resource/site where the 2 are compared? What are
> the main differences?
> All opinions are greatly appreciated!

> Thanks in advance,

> Brian Flynn

> --
> Brian Flynn
> Dymaxion Research Ltd.




Mon, 23 Sep 2002 03:00:00 GMT  
 ADO vs DAO
There have been many posts discussing the perf issues (and ADO *is* usually
slower than DAO).

Not much mention has been made about the lack of feature parity in ADO when
running against Jet dbs:

**Cannot create users or groups with PIDs in ADOx
**Cannot span multiple databases in a single transaction
**Cannot lock whole tables from write with a single open type
**Cannot lock whole tables from read or write with a single open type
**Cannot create updateable ODBC links
**Cannot change permissions on "Access" objects at all
**Cannot retrieve implicit permissions eithout enuming all explicit perms,
etc.
**Cannot retrieve whether index exists due to FK rels in Jet
**Cannot create "prevent deletes" replicas in Jet 4.0
**Cannot create JPM properties
**Cannot usually even access JPM properties, with only a few specific
hard-coded exceptions.

The list goes on, new items being found all the time.

So, if you need the new features, fine. If you do not, then the lack of
support for all the OLD features is pretty glaring. The fact that instead of
the one ref, you have to reference three things (ADO, JRO, ADOx) to get less
functionality is hard to swallow.

If you need the thread safety, for a web app, or if you are writing against
multiple potential backends and want to try to write code that will run
against any of them (for example Jet and SQL Server), then maybe the OLE DB
provider will appeal. Otherwise, think about the above list. And don't
rewrite stuff just because the cattle barrons in Redmond say you should.

--
MichKa
(insensitive fruitarian)

random junk of dubious value, a multilingual website, the
54-language TSI Form/Report to Data Access Page Wizard,
and lots of replication "stuff" at the (no scripts required!)
http://www.trigeminal.com/


Quote:
> Hi All,

> I am currently working on an application using VB6.0 that uses a Microsoft
> Access database. The application has extensive database use and is
currently
> using DAO. I am aware that DAO is an "Older technology" than ADO but will
I
> see a significant difference if I convert the code to use ADO?

> Can anyone point me to a resource/site where the 2 are compared? What are
> the main differences?
> All opinions are greatly appreciated!

> Thanks in advance,

> Brian Flynn

> --
> Brian Flynn
> Dymaxion Research Ltd.




Mon, 23 Sep 2002 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. ADO vs DAO vs RDO

2. ADO vs DAO

3. ADO vs DAO

4. ADO vs DAO

5. ADO Vs DAO

6. ADO vs DAO

7. ADO vs DAO

8. Confused about performace test, ADO vs DAO, please help

9. ADO vs DAO

10. Confused about performace test, ADO vs DAO, please help

11. ADO vs DAO

12. ADO vs DAO,Can Anyboby Comment

 

 
Powered by phpBB® Forum Software