Access 95 Upgrade to SQL Server

[dorpnospam]

We have an old but very critical application that was written in VB 6
against Access 95 dbs. We need to ditch this decrepit old unstable db
platform but we are trying to determine the best migration path to SQL
Server.

I am researching options. I started by upgrading a sample db to Access
2003 and update the VB 6 app to use DAO 3.6. That works well.

Next I tried upsizing the application to SQL Server and that works as
well but it is very, very slow.

Next I tried optimizing the code by using Pass Thru queries where ever
possible, but many, many of the queries are opened as dynasets and are
used for updates so it is still not performing well.

My next thought was to port the applications to ADO and ditch Access
all together.

Is there any other suggestions anyone can give me?

Thanks,

Jon

[Lyle Fairfield]

do********@insightbb.com wrote in news:1161912321.520674.147470
@k70g2000cwa.googlegroups.com:

We have an old but very critical application that was written in VB 6
against Access 95 dbs. We need to ditch this decrepit old unstable db
platform but we are trying to determine the best migration path to SQL
Server.

I am researching options. I started by upgrading a sample db to Access
2003 and update the VB 6 app to use DAO 3.6. That works well.

Next I tried upsizing the application to SQL Server and that works as
well but it is very, very slow.

Next I tried optimizing the code by using Pass Thru queries where ever
possible, but many, many of the queries are opened as dynasets and are
used for updates so it is still not performing well.

My next thought was to port the applications to ADO and ditch Access
all together.

Is there any other suggestions anyone can give me?

Thanks,

Jon

For critical applications it's a good idea to hire an experienced and
capable developer.

--
Lyle Fairfield

[Larry Linson]

<do********@insightbb.comwrote

Next I tried upsizing the application to SQL
Server and that works as well but it is very,
very slow.

A well-designed, well-implemented single-user database application with
extra users thrown it isn't necessarily (not even likely to be) a
well-designed, well-implemented multi-user database application; nor is a
well-designed, well-implemented multi-user database application with its
tables moved to SQL Server and linked is not necessarily (and only a little
more likely to be) a well-designed, well-implemented client-server database
application. And, as you see, the "standard prescription" of converting
everything to stored procedures isn't a cure-all.

One key factor in client-server performance is to reduce the amount of data
being passed across the network... while many single- or multi-user
applications open a form with a substantial recordset and allow scrolling
forward and backward, that will slow response. It is surprising how often
the number of records _actually_ needed is just one (if it exists) or none
(if it doesn't).

My next thought was to port the applications to
ADO and ditch Access all together.

You have already "ditched Access" because Access is the user interface and
development tool, which defaults to using the Jet DB engine (which you were
using, and may well still be using, with tabledefs linked to the SQL Server
tables). My guess is that "porting to ADO" will provide minimal performance
improvement without redesign of your database access scheme (but that's only
a guess, since we have no details). And, redesigning for the client-server
environment using DAO/Jet linked tables will likely give you dramatic
improvement, easier.

Is there any other suggestions anyone can give me?

I think I already did... Mary Chipman has a good book on apps using SQL
Server -- it's worth a read.

Lyle's advice is appropriate, too. Should you seek outside assistance, be
sure it is someone with good database and SQL Server credentials, not just
someone with a good reputation in (non-database apps in) VB.

Larry Linson
Microsoft Access MVP

[jdorp]

I am an experience developer and I work with experienced developers.
We now the long term solution but we have time constraints that are
pressing us to determine a phased approach to moving to SQL Server.

This application is actually a suite of over 10 applicaitons written in
VB 6 that move data in and out of many, customer specific Access DBs.

I was writing to see if you had any suggestions on that front.

You stated that moving to ADO will not buy us much. It was my
understanding that when working with DAO against linked tables Access
is processing much if not all of the data even though the data now
lives in SQL Server. Are you saying that moving to ADO calling SQL
Server directly will not improve performance over linked tables?

Again, I realize that nothing is a substitute for designing the
application from the ground up for efficient database usage. I do it
everyday. But, again, we are trying to find a way to move our data
from these old Access databases that have been problematic to a SQL
Server environment. Yes, the long term goal is to completely redesign
these applications using .NET, ADO.NET, and SQL Server with performance
in mind but I am trying to find the quickest path that gets us to SQL
Server to buy us time for the real project of re-engineering the whole
mess.

Jon
Larry Linson wrote:

<do********@insightbb.comwrote

Next I tried upsizing the application to SQL
Server and that works as well but it is very,
very slow.

A well-designed, well-implemented single-user database application with
extra users thrown it isn't necessarily (not even likely to be) a
well-designed, well-implemented multi-user database application; nor is a
well-designed, well-implemented multi-user database application with its
tables moved to SQL Server and linked is not necessarily (and only a little
more likely to be) a well-designed, well-implemented client-server database
application. And, as you see, the "standard prescription" of converting
everything to stored procedures isn't a cure-all.

One key factor in client-server performance is to reduce the amount of data
being passed across the network... while many single- or multi-user
applications open a form with a substantial recordset and allow scrolling
forward and backward, that will slow response. It is surprising how often
the number of records _actually_ needed is just one (if it exists) or none
(if it doesn't).

My next thought was to port the applications to
ADO and ditch Access all together.

You have already "ditched Access" because Access is the user interface and
development tool, which defaults to using the Jet DB engine (which you were
using, and may well still be using, with tabledefs linked to the SQL Server
tables). My guess is that "porting to ADO" will provide minimal performance
improvement without redesign of your database access scheme (but that's only
a guess, since we have no details). And, redesigning for the client-server
environment using DAO/Jet linked tables will likely give you dramatic
improvement, easier.

Is there any other suggestions anyone can give me?

I think I already did... Mary Chipman has a good book on apps using SQL
Server -- it's worth a read.

Lyle's advice is appropriate, too. Should you seek outside assistance, be
sure it is someone with good database and SQL Server credentials, not just
someone with a good reputation in (non-database apps in) VB.

Larry Linson
Microsoft Access MVP

[Larry Linson]

"jdorp" <do********@insightbb.comwrote

You stated that moving to ADO will not buy
us much.

The dramatic performance improvement will come from redesigning /
reimplementing to make your use of the server efficient, not from the
access method you use.

It was my understanding that when working
with DAO against linked tables Access is
processing much if not all of the data even
though the data now lives in SQL Server.

Unless you are doing something you have not described, Access is processing
nothing at all... you are using DAO, the Jet DB Engine, ODBC, and SQL Server
from your front-end. Access doesn't even have to be installed on either the
user's machine nor the server. It is Jet that handles the data in your
configuration.

Are you saying that moving to ADO calling SQL
Server directly will not improve performance
over linked tables?

I think you will be disappointed in moving to ADO, unless you also redesign
to take advantage of the C/S environment. I suspect it will be simpler and
easier to accomplish the redesign if you are not also facing the learning
curve of ADO at the same time, and you will get the vast majority of the
improvements. I have found DAO, Jet, ODBC, and MS SQL Server to be (perhaps
surprisingly) responsive over a number of years.

Again, I realize that nothing is a substitute for
designing the application from the ground up
for efficient database usage. I do it everyday.
But, again, we are trying to find a way to move
our data from these old Access databases that
have been problematic to a SQL Server environment.

Just changing the access method isn't going to make an inefficient design
more efficient. And, it's no "sin" to have designed properly for one
environment and then have performance glitches when you move that design to
another environment -- it's just the way things work, sometimes.

Yes, the long term goal is to completely redesign
these applications using .NET, ADO.NET, and SQL
Server with performance in mind but I am trying to
find the quickest path that gets us to SQL Server to
buy us time for the real project of re-engineering the
whole mess.

My guess is that learning classic ADO will be just a diversion from your
task at hand -- ADO.NET is built on a different object model, so you won't
get "a leg up" on that task by converting to ADO. If you analyze where you
are experiencing delays and make the design changes to improve that, you'll
be getting the best "bang for your buck." If, even so, you find that
performance is unsatisfactory, you can try using ADO, but I don't think the
difference between using DAO and ADO is going to "wow and amaze" you -- but
the only way to determine, I suppose, is to give it a try.

But, if it were my application, I'd try to improve performance first in DAO,
concentrating on the obvious improvements: don't open recordsets for full
tables, or full tables joined in queries; use a WHERE clause to limit the
records retrieved.

Larry Linson
Microsoft Access MVP