DAO vs ADO

[dave h]

I'm new to Access but have some experience with VS.Net, VB.Net and ADO.Net.
My inclination is to build my Access application (which will probably not
exceed 5 or 6 concurrent users and 50K rows of data in about 30 tables)
using ADO. But, I'm wondering if the maturity of Access/Jet/DAO would argue
that this is a safer approach. Are there any real downsides to using ADO in
my VBA code?

 

[Douglas J. Steele]

There are no downsides that I can think of.

DAO is almost always more efficient when going against Jet databases (i.e.
MDB files), but the difference isn't all that noticeable.

 

[Nick Coe \(UK\)]

In dave h typed:

I'm new to Access but have some experience with VS.Net,
VB.Net and
ADO.Net. My inclination is to build my Access application
(which will
probably not exceed 5 or 6 concurrent users and 50K rows
of data in
about 30 tables) using ADO. But, I'm wondering if the
maturity of
Access/Jet/DAO would argue that this is a safer approach.
Are there
any real downsides to using ADO in my VBA code?

You can do a bit more in terms of db manipulation (admin,
definition and design) with DAO. ADO is great as a common
interface to varying back ends but for purely Jet db's (mdb)
I'd go for DAO. Speed differences are irrelevant and
negligable really.

An example would be hiding the database window and
substituting a homegrown one per the Access Developers
Handbook (Sybex) - you can do it with DAO but not with ADO
AFAIK.

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----

 

[Douglas J. Steele]

Should have added that you might find
http://www.trigeminal.com/usenet/usenet025.asp interesting reading.

 

[dave h]

but for purely Jet db's (mdb) I'd go for DAO.

Thanks for the comments. If you were going to use MSDE 2000, which would
you prefer?

Thanks again, Dave

 

[Douglas J. Steele]

MSDE is SQL Server, so ADO is probably better.

 

[Arvin Meyer]

As Douglas mentioned, ADO is slightly faster on SQL-Server databases. That's
generally true on tables with more than 80K to 100K rows and higher. Below
that the speed difference is generally under a quarter of a second when
returning a typical resultset.

You do have the advantage of using both of them if you go with an Access MDB
front-end against the MSDE/SQL-Server back-end. You must set a reference to
both and be sure to disambiguate references in your code (DAO.Recordset
instead of simply Recordset) because there are common objects between the 2.
The advantage of using both is that you can gain speed with ADO and still
use the greater functionality of DAO when it is missing in ADO. The
disadvantages are that extra care must be taken when using both.

Just my 2¢
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Brendan Reynolds]

MDB and ODBC-linked tables? DAO. ADP? ADO.

In an MDB, it doesn't have to be an either/or decision. You can use both.
Just be careful to disambiguate when objects have the same name in both
libraries, e.g. Dim rst As ADODB.Recordset or Dim rst As DAO.Recordset
rather than just Dim rst As Recordset.

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

 

[Dave Harney]

Thanks for all the great comments! Very helpful.