DAO or ADO ?

Martin

unread,

Feb 27, 2010, 9:51:01 AM2/27/10

to

I have some apps written in VB6 that use an MSAccess database. I use
both DAO and ADO coding to manipulate the data. But I use DAO for the
majority of it because it's my understanding that DAO is faster than
ADO.

I'm going to make a fundamental change in the architecture of this app
by using a MySQL database instead of MS Access.

So, my question is: would DAO still be faster than ADO?

Anyone have any experience with this situation? Any advice?

MikeR

unread,

Feb 28, 2010, 6:02:52 AM2/28/10

to

I use Access and DAO in my apps. I'm very pleased with that combo. What prompts you
to change?

Mike

ralph

unread,

Feb 28, 2010, 4:04:34 PM2/28/10

to

On Sat, 27 Feb 2010 07:51:01 -0700, Martin <ironwoo...@gmail.com>
wrote:

If used under the same circumstances - a local MySQL database on the
same box as the application - you will not experience much of a
difference. DAO in most cases will still be as fast or faster.

The reason is, DAO is in-process and uses ODBC. ADO is out-of-process
and requires an OLE DB Provider (or a OLE DB/ODBC Provider which adds
layers). MySQL uses ODBC Drivers, therefore if you stay DAO you will
reduce the layers.

There are several MySQL OLE DB Providers out there, but AFAIK none
have been very stable. At least none released since 2008. That may
have changed.

The reason why a DAO/MySQL combination may not be as fast as a
DAO/MSAccess combination is because DAO, Jet engine, Jet formats, and
ODBC are all joined at the hip. They literally grew-up together from
birth. But it all depends on your problem domain.

The bigger concern changing from MSAccess to MySQL will be the
occasional subtle differences in behavior between queries (for example
Seek and Joins) for the two. Expect to make some code changes - but
not that much.

Also you will, unfortunately, run into a totally non-technical issue
that may be worth considering - out-side of C/C++ programmers who have
more direct access to ODBC - you will find a general almost universal
belief that DAO is dead and one needs to be using ADO instead. So
getting help with a specific problem with DAO will take a little extra
digging. My advice - when seeking MySQL help never mention DAO, just
the particular ODBC driver you are using.<g>

However, if you are changing your environment from a local database to
a distributed one that is accessed by multiple users, then definitely
assume a change to ADO is in your future.

hth
-ralph

Martin

unread,

Mar 1, 2010, 9:16:32 AM3/1/10

to

On Sun, 28 Feb 2010 12:04:34 -0900, ralph <nt_cons...@yahoo.net>
wrote:

Thanks for the excellent reply, Ralph.

At this point in time I'm not being forced to do anything. I'm mainly
trying to plan for the future and am doing some R & D.

I'm going to give the ADO approach a try. My programs use a
browser-based user interface so I'm accessing the database from not
only VB6 but also from some PHP scripts. I'm anticipating the eventual
demise of VB6, so, at some point, another development language will be
involved.

I also want to provide for database access from other computers on a
LAN.

Thanks again.

Paul Clement

unread,

Mar 4, 2010, 8:55:28 AM3/4/10

to

On Sat, 27 Feb 2010 07:51:01 -0700, Martin <ironwoo...@gmail.com> wrote:

� I have some apps written in VB6 that use an MSAccess database. I use

�

DAO is probably fine for Microsoft Access. It should be a little bit faster performance-wise since
there's an additional layer of software when using ADO/OLEDB. In addition, DAO exposes a few more
database specific features than ADO.

Paul
~~~~
Microsoft MVP (Visual Basic)