Access 97/2000/2003 comparisons - VB6 application

[dcali]

I have a rather large application that uses an access 97 database (DAO). We
also have a version that works with access 2000, Oracle, msde, and SQLServer
(ADO). We would like to retire the DAO version of the product, but there are
places where the ADO version is much slower compared to where we run DAO
seeks (very noticeable when looping). So, I have a few questions.

1. Are there any tips/tricks to speed up ADO queries to compare with DAO
seeks?

2. Would there be any benifit in using access 2003 over previous versions of
the software besides the added features (xml support, etc).

3. Has anyone tried SQLExpress 2005, and what do you think about it as an
alternative to access? I figure that there would be less corruption, but we
already offer an Oracle version for larger customers.. I am more concerned
with the speed at this point.

Any help is greatly appriciated, and I appoligize if this has been posted in
the wrong forum.

 

[david epsom dot com dot au]

1. Are there any tips/tricks to speed up ADO queries to compare with

DAO seeks?

I have a rather large application that uses an access 97 database (DAO).
We
also have a version that works with access 2000, Oracle, msde, and
SQLServer
(ADO). We would like to retire the DAO version of the product, but there
are
places where the ADO version is much slower compared to where we run DAO
seeks (very noticeable when looping). So, I have a few questions.

1. Are there any tips/tricks to speed up ADO queries to compare with DAO
seeks?

Convert your VBA loops to T-SQL stored procedures in a database that
supports T-SQL stored procedures.

2. Would there be any benifit in using access 2003 over previous versions
of
the software besides the added features (xml support, etc).

No. The database engine and VBA product are the same.

3. Has anyone tried SQLExpress 2005, and what do you think about it as an
alternative to access? I figure that there would be less corruption, but
we
already offer an Oracle version for larger customers.. I am more concerned
with the speed at this point.

Painful to install and administer. No faster, but allows you to use T-SQL
and .Net assemblies. Breaks (Jet) transactions on linked tables.

Any help is greatly appriciated, and I appoligize if this has been posted
in
the wrong forum.

I'm sorry, but I think A2000+ is generally inferior to A97, and it
looks as A2000+ will be obsolete with the next version of Office.
If the A97 version still works, wait until Office 12 comes out before
you pick which of your versions you wish to retire.


(david)