How to painlessly go: Access95 -> .... -> SQLserver?

  • Thread starter Thread starter Dean Dei Cas
  • Start date Start date
D

Dean Dei Cas

The general question I have is:

If one is starting with an Access95/VB/ASP/Crystal Reports
system, which several-months-in-the-future will be re-
written atop SQLserver with a very different database
design -- what is the least painful series of upgrade
steps to take?

More specific questions:

Prior to replacing Access 95 does it make sense to upgrade
the system to Acccess 97 (or 2000)? Is the difficulty of
conversion sufficently modest that the "cost" of upgrading
is payed back by the improvements in 97/2000? (Wow do I
hate Access 95!).

.... or is the least painful path to re-write the system
atop SQLserver and throw out the Access95 components when
they are re-written?

.... is the best way something in between these choices?

<whimper, whimper> ... it's not my fault we're using '95.
I just arrived at this client.
 
If you are just starting out, then you might consider using a2000 or later,
and consider using a adp project. Using a adp project means that here is
zero conversion when you move your data to sql-server. (these adp projects
are 100% compatible with sql server, and no changes need to be made when you
move your data to sql server).

Of course, if you have applications written already, that is a different
matter. I would most certainly migrate your current access applications to
whatever newer version of ms-access you plan to use with sql server.

You can certainly use ms-access with sql server (I am making the assuming
that this is what you plan to do). You do realize that Oracle, or sql server
don't come with any means to create the User interface right? They are just
the database engine. Any user interface has to be written in VB, or
ms-access, or even on the web server.

You should also note that ms-access is not really a database either, but
only a tool that connects to your database of choice. Thus, starting with
access 2000 and later, the office cd has shipped with two database engines.
The default database engine is JET, and the other one is the MSDE (desktop
version of sql server). So ms-access is just a tool that lets build a user
interface to a database system. That database might be Oracle, sql-server,
or JET.
 
Dear Albert:

A very nice answer indeed. Only one slight difference of opinion.

I would have said that Jet is the default engine for MDBs, while MSDE
or SQL Server are the default engine for ADPs. Of course, you knew
that, but it maybe didn't quite come out that way. But, I would NOT
agree that "The default database engine is JET" with respect to Access
as a whole, only with respect to MDBs.

Sorry to be so picky, but I'm sensitive in this respect. We've used
Access here with SQL Server more than 80% of our work the last 3
years, so, for us, SQL Server is more or less the overall "default"
engine. But, especially in this newsgroup, that's certainly the
minority! <g>

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Most developers moved to A97 and stayed there for a long time. A95 was one
of the "alternate version buggy apps". (A1.0, A95, A2000 were the weaker
versions followed by the stroonger ones A2.0, A97, A2002/3.)

Sorry but there is "painless" solution!
You do have to get off of A95 ASAP though.

I would jump straight to A2003 if possible.
Then I would see if you want to use .mdbs and linked tables or .adps and
direct connection to SQL Server.
There is a bit of a learning cure to .adps so a first step might be to
migrate the tables to SQL Server then convert the A95 front end to A2003 and
then link to the tables and see if it works. It should - I do it this way
often.
But it will NOT be optimized.
Now you need to tweak it to improve the user experience.
Get a book (Mary Chipman's is good) to learn how Access works with SQL
Server.

Once the app is running, try re-writing it as an .adp.
This way you aren't "under the gun" and can both learn and develop.
 
I would like to keep this going a little further:
I have heard a lot of noise about Jet vs SQL for several
years. My applications are still on 97 and Jet and seem
to be fine, but maybe I am missing out and do not know
it. Can you expand on:
benefits of SQL over Jet?
benefits of 2002/3 over 97, other than the stated
Microsoft added features?
 
Dear Albert:

My #1 test of whether you need to switch to SQL Server is whether Jet
is serving you well. There's a definite cost in making the switch.

After a couple of years writing queries for Jet, I began to find bugs
where I'd write a query that looked fine, but it wouldn't run. Also,
many things ran only slowly. Finally, connecting to a Jet back end
over a WAN can be horribly slow. SQL Server improves these things
dramatically.

So, if you're not having the above problems, it may not be worth a
switch.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top