Hi, All

I have inherited some VB6 applications which use Access 97 MDB databases.
The MDB has a couple of dozen tables and queries.  The size can be from 1MB
(small) to 500MB (very large) depending upon the amount of user data.  The
VB6 code uses DAO 3.51 to access the data.  The end user does not use Access
on the data, only the VB6 programs access the MDB.

I have increased pressure to upgrade/convert from Access 97 to either
Access 2000 or Access 2002.  I gather the reason is mostly because of
database
performance.  Does anyone have experience with increased MDB performance
going from Access 97 to either Access 2000 or Access 2002?

What might be the "real" reasons for moving from Access 97 to a newer
version?  I'm not so sure this pressure is based on any sound technical
reasons but pure conjecture.......

If it turns out that performance is a major determining factor, is it
possible to just upgrade to the newest version of DAO and Jet and still use
Access 97 and realize a performance gain?

If upgrading is the best solution, do I have to upgrade DAO and Jet? And if
I do, how do I determine what must be delivered (and installed) to the end
user?

Thanks greatly for your patience.  This is a new subject for me.

Ed Lyons

I've worked with several applications that use Access DBs as the back-end.
I've not seen any performance differences of any kind. (Of course there
might be all sorts of subtle problems that might have appeared had the
upgrades not been in place, but I'm not aware of them...)

There are significant differences if you are developing applications in
Access itself, but these are irrelevant to VB.

First, I can't remember when upgrading to the next version of ANYTHING
caused a increase in performance,.

I been using computers since DOS 2.0, and on upwards all the way to windows
2000. Virtually every single piece of software that I have upgraded to
always took more memory, and took more processing.

You would have to be about the first person on planet Earth that is going
admit that upgrading from a previous version of something to the next
version performaned better!

I don't recall any version of ms-word running faster then than the previous
version. I don't recall any version of Excel running faster then the
previous version.

There are those rare and notable exceptions. For example, when the people at
FoxPro introduced rush-more technology to the desktop database, they made a
very large increase in some query speeds. In fact, several years later, the
folks at Microsoft purchased FoxPro, and incorporated the Rushmore
technology into the JET database engine used with ms-access.

So, for my 20+ years in the computing industry, virtually every new version
of anything I have ever purchased generally needed more disk space, more
memory, and also required more processing!

I would have to assume that your experience is the same (or perhaps you are
new to computers...and have not noticed that windows 3.1 ran very well with
4 megs of ram, and with 8 megs was great). The min for windows winXP is
right now at 128 megs (that is 32 times the memory requirements of windows
3.1!).

Thus, I think you kind of already know the answer as to if access 2000, or
2002 runs faster. Can you guess based on your personal experiences with
computers? I would bet that I don't really have to tell you anything new, or
anything that I tell you will not differ from your own personal experience.
Hence, anything now that I tell you will not surprise you!

I a some what perplexed as to why you think the newer versions of office
might run faster? What other software have you purchased where you noticed
this trend?

a2000, and a2002 runs slower. In addition, the data is now saved as
Uni-Code. That means that ms-access fully supports all international
languages. It also means that like all newly written Microsoft applications,
the data is NOT STORED as ASCII anymore. Hence, it also means for each
letter (character) stored in your database, there is in fact now TWO
characters stored! This actually means that database file in fact can grow
to 2 x the current size that you have with a97.

There are some options in ms-access versions that come after a97 to compress
the data in an attempt to reduce this very large increase in data
requirements. In fact, it does control the increase in size by a good
amount. On the hand, the processor now has to compress, and de-compress
data. There are some general performance hits, but the hardware today runs
circles around the hardware that office97 was designed for (I think a p60,
with 32 megs of ram was top of the line back then?). We are now at p2500
Mhz, and typical memory today is 256 megs of ram. Typical hard disks back
then were 200 megs!

A good example is the access97 runtime. It is about 10 megs in size. The
replacement in a2000 is about 150 megs. Hence, if you want to use the
developers edition of office, you will find a huge increase in size.

However, due to the cost of disk drives today, it is FAR CHEAPER to install
office 2002 today, then it was way back when office 97 came out.

As for reasons to upgrade?, Well, #1 is of course the improved international
support. (with Uni-code you can use ms-access with any language). There was
also some improvements the areas of replication. With a2002 also some
improvements with web based stuff.

Also, for the last two versions, ms-office shipped with a compatible sql
server based engine. This allowed you to create  a native connection to sql
server (no local tables in ms-access). Hence, the largest change and
addition of new features was the addition of what is called a ADP project.
This really is almost a whole new system. It is designed for applications to
work with SQL server. If you use the included data engine, then you can
start writing server side code, and it is 100% compatible with sql-server.

So, I guess I have to ask why did you upgrade from the 1984 version of
ms-word to the current version? Actually, lets be more real here, and ask
why did you upgrade from word in windows 3.1 to word97? I am not kidding on
this question! For most users, the general features in word for windows 3.1
(word 5.0) are not much different. Even the mail-merge wizard in windows 3.1
is VERY similar to what we have today. In addition, word executable could
fit on a floppy disk! (the support files totalled about 10 megs max, and of
course you could not run word from a floppy...but the executable was very
small).

So, why did you upgrade to word 97? Is there a particular feature? Or was it
due to the fact that everyone else started sending you word97 docs, and you
could not read them? That being the case, much of the same will apply to
office97 to XP.

For me, a97 was great leap forward because it threw out the old word macros,
and replaced whole thing with Visual Basic 5.0. (this was great leap forward
for developers...but not much for users). I know exactly why I wanted
word97, and could not wait! ms-word also became a com object. Again...real
nice!

So yes, you will take a performance hit...but it is not too much, and
hardware today is much improved.

As for reasons to upgrade....they probably are the same reasons as to why
you upgrade from Word 5.0 (windows 3.1)

If you don't know the reasons why you upgraed in the past...then I can't
come up with  new set of reasons for you.

--
Albert D. Kallal
Edmonton, Alberta Canada

What about going straight to MSDE, Then if needed it should make it esier to
upgrade at a latter date to SQL Server.

I think they have done some minor tweaks to improve performance but in the
real world I don't believe they would be noticeable.

The main difference I can see is that Access 2000 & 2002 increase the
database size limit from 1GB to 2GB but I'm not sure whether it has
increased robustness to cope with the extra size!

I've not seen anything to warrant migrating from A97 to A2000 myself, but I
do intend to migrate to using the MSDE that comes with A2000.