Advice needed: Should we upgrade MS Access 2000? And if so to what?

[ship]

Hi

We need some advice: We are thinking of upgrading our Access database
from Access 2000 to Access 2004.

How stable is MS Office 2003? (particularly Access 2003).

We are just a small company and this is a big decision for us(!) It's
not just the money it's committing to an new version of Access!

We have been runnning MS Access 2000 (from MS Office 2000) for the last
4 years. We are thinking of upgrading to MS Office 2003 (or possibly
Office 2002 ??).

Is there much diffence between the three MS Access versions 2000, 2002,
2003 - and if so what?!

We now have a huge volume of data (c.80MB +160MB of data) on our
customer behaviour, customer transactions etc etc on MS Access 2000. We
use it to do mail shots and all sorts.
That said we arent very advanced programmers - very much self taught.
So we probably arent using the more advanced features of Access.

But we our business is UTTERLY dependant on Access!

We find Access 2000 somewhat slow and clunky. And it has a habit of
crashing now and again. Our PCs are all running WindowsXP but are also
rather old (about 3.5 years old)...

So it's probably time we upgraded the software be we remain nervous...

- How hard is it to install the upgrade to MS Office 2003?

- We have been offered a 10 user license, but it's an OEM upgrad
version. Is this likely to be a problem.

- Is 2003 Access better than 2000 in any *significant* ways?
(e.g. faster? better functionality? more reliable? less bloated?)

- Or would we be better off just using Access 2002 not 2003?!

* * * * *

Finally any top tips on a cheap, honest, reliable place to buy the
software from?

All advice gratefully received...


Ship
Shiperton Henethe

 

[Alex White MCDBA MCSE]

Can I have a copy of Access 2004?, only joking, no such thing. Access 2003
is the latest version. Well Office 2003 is the most stable version yet of
the whole office suite. A lot of people say that Access 2002 and Access 2003
are more stable than Access 2000. The size of the data you are talking about
is medium size well within was access can cope with, how many users are you
running?

Your Access 2000 databases will run unconverted within Access 2003, the
newer versions of office are much more feature rich than Access 2000. To run
Office (any version 2000,2002,2003) you should have a minimum 256MB ram on
your workstations it does make a difference. If it all fails you can install
Access 2000 with Office 2003 but you will loose out on some of the
integrated features.

If you have been offered an OEM licence for Office they are almost certainly
not legit, as OEM should be provided with NEW hardware, also OEM has no
upgrade path at all. If you are trying to reduce costs then think about
this, Buy one full copy of Office 2003 Pro, Buy all the other copies as
Office 2003 standard, buy the Access Developer Extensions, this allows you
to run the runtime version of Access on the other computers, you would not
be able to do any modifications of forms etc from the other computers but
you would be able to run the database and enter/update/delete records and
run forms etc.

Hope it helps.

 

[Chuck Grimsby]

If you haven't already, read Alex's message. It's dead on target!

Only one (sniggly) point. Access 97 was (is) the most stable version.

Upgrading to Access 2003 will reduce how often you databases crash,
depending upon your _network_. It's usually the network more then
anything else that causes Access to crash. Assuming you're running
your databases over a network, that is! (D'oh!)

The upgrade is a snore for any network administrator, and only slightly
more exciting for a user. (Mostly due to excitment or fear then
anything else.) Once the upgrade is in place however, even the most
scared user wonders why they felt that way. (Cudos to the Access
Development Team for that!!!!!)

 

[Larry Linson]

We need some advice: We are thinking
of upgrading our Access database
from Access 2000 to Access 2004.

You've had some good advice on whether or not to upgrade.

Access 2000, provided you have applied _all_ released Service Packs (3, if I
recall correctly) and other patches, has reached a state of reasonably good
stability.

I would NOT _suggest_ distributing your applications with the runtime
support -- there are enough unobvious restrictions (a common statement is,
"Whoever would have thought _that_ was a design view feature?") and, of
course, your users would not have Access to use for their own purposes. It
can be done successfully, but IMNSHO, should only be done by experienced and
skilled developers -- if the users don't have any other use for Access.

If your users have full Access now, you'd be well advised to search out the
..MDB and .MDE files, just for the record. Remote searches in many companies
turned up a very surprising number of Access databases created by individual
users, some temporary and transitory, but others with data that needed to be
captured as a corporate asset.

There were a few real changes between Access 2000 and 2002 (obviously
nothing you couldn't live without) but very, very little change between
Access 2002 and Access 2003. Still, the changes in both cases were so minor
that, by default, they create databases in Access 2000 file format, so that
any new features just don't work. It's an easy option to change them to
2002/2003 file format if you don't have any users still using the older
version.

One other consideration: Access 2000 is now "out of support" at Microsoft,
so tech support is not required to answer questions about it. In practice,
this is not quite as drastic a problem as it may sound -- to determine how
it will affect you, consider how many times you have had to make support
calls to Microsoft about Access 2000 in the last <whatever period you decide
is pertinent>.

The current version of Access, and thus the one that will continue to be
supported for the longest time, is Access 2003. Its Help assumes primary
Help is online, so you will want your users to have (preferrably high-speed)
Internet access when using it; Access 2002's Help assumes local help
information is primary. Otherwise, there is almost no difference.

Because it is not current, you may be able to get a better deal on Office XP
Pro if you search various sources, including the online auction sites -- if
you are willing to "trade" a shorter official support period for a better
price.

Larry Linson
Microsoft Access MVP

 

[David W. Fenton]

We have been runnning MS Access 2000 (from MS Office 2000) for the
last 4 years. We are thinking of upgrading to MS Office 2003 (or
possibly Office 2002 ??).

What benefit are you expecting to get from updating your version of
Office?

I have plenty of clients running Office97.

I have other clients running Office 2K, 2K2 and 2K3, but using
Access97 with the other Office apps.

I also have clients using A2K and A2K3 (nobody using A2K2, though).

I don't really see much of a difference from an end user's point of
view.

And I haven't programmed in A2K3, so I don't know about the
programmer's point of view directly, but have heard that it's
better.

I would consider upgrading Access and leaving the rest of the Office
suite alone, unless there are features in the new versions of the
other apps that are important. Personally, I've seen very little in
the recent versions of Word or Excel that would justify spending 10
cents upgrading.

If you use Outlook (and you shouldn't), then the new version of that
is improved in terms of security.

But since Office97, there just hasn't been any innovation in the
Office suite as a whole that would justify the upgrade.

On the other hand, if you're getting it preloaded on new PCs, then,
by all means, get the latest version of Office. It's so unchanged
that I've got clients who switch back and forth among Office 97,
Office 2K and 2K3 without blinking an eyelash.

 

[Alex White MCDBA MCSE]

A little more to add,

In nearly all the upgrades that I have done from Access 97 to 2000 (+) have
been sold on Access 97 being unstable e.g. databases crashing etc, the
interesting thing that keeps on coming out of these upgrades is there is
some form of data corruption, of course upgrading did not solve this problem
and the data had to be cleaned, so there is an argument that the upgrade did
not make things anymore stable. When I am asked to advise on what version, I
will always go for 2000 unless there is a compelling reason for going with
either of the other two versions, why would I choose 2000, well it's simple
I have had the pain with 2000, I understand all the bugs (all software has
bugs) that affect me, I know exactly how to structure and 100+ form project
running on 100+ desktops so why change is my view.

I think before upgrading look at your hardware of your PC's, memory is the
number 1 issue, processor number 2. The clunky feel to your database could
be how it is structured, in a multi-user environment, split the database
front-end/back end (I don't know if you already have).

You have no choice but to purchase Office 2003 (Legitimately), but MS has a
downgrade rule, allowing you to run a previous version, (how many versions
back I don't know). I will reinforce my point about OEM, don't buy it you
are not entitled to it (unless you upgrade your machines at the same time,
you need to check with MS on the OEM rules), it is as legitimate as stealing
a copy off the internet.

On the point of how easy is the upgrade, well I never upgrade, I like to
completely get rid of the previous versions first, there is a tool for doing
that job on the MS Office website. I never install off CDROM, copy the CDROM
to a share on the server and install from there a lot less trouble, the
workstation never has the horible message "Now insert your Office CD in the
drive".

 

[CCR]

Alex,

You seem quite knowledgeable in Access. You said something that caught my
attention and I would like to ask you to elaborate:

"...in a multi-user environment, split the database front-end/back end "

What does this mean and how do I do it?

I have 6 or 8 users working in my A2K3 db over a network and would like to
be sure it is functioning at its best.

Thanks,

CCR

 

[Alex White MCDBA MCSE]

No problem, one of the problems with Access is this, a lot of the projects
start small single tables couple of forms, and as life goes on people start
sticking more tables, forms, queries etc with no real plan just 'we need a
table to do this', this breeds the unmanageable project situation a few
years in.

One of the problems is this the database starts its life as a single user
system, and then other people want to use it at the same time, but things
have not been planned that way. You end up copying the database to the
server so that more than one person can use it at one time, great it works,
but it have become slower because the database is not local any more it's on
the server, you have then creating a lot of network traffic just opening
forms etc, also Access is good at multi-user only at a database level not
forms etc.

So the answer to the problem is the database splitter within Access what
this does is split you Access database into 2 mdb's one with the data (BE =
Back-end)(and only the data, with the database rules for relationships). The
other mdb is all the forms,queries,modules etc. What you then do is give
every user a local copy (FE= Front-End) you copy this to the local computers
and the users use this mdb to access the data, from a user perspective
nothing has changed, but from a network perspective, the only traffic going
across the network is actual data from the database not forms etc, much
better in terms of performance and reliability in a multi user environment.

Hope that helps.....

 

[David W. Fenton]

In nearly all the upgrades that I have done from Access 97 to 2000
(+) have been sold on Access 97 being unstable e.g. databases
crashing etc, the interesting thing that keeps on coming out of
these upgrades is there is some form of data corruption, of course
upgrading did not solve this problem and the data had to be
cleaned, so there is an argument that the upgrade did not make
things anymore stable.

Who is advising an upgrade from Access97 to Access2K to increase
stability? Only someone who is incompetent and has never really used
both versions to any extent would be fool enough to think that A2K
was *more* stable than A97.

The other point: if the upgrade is in response to instability
issues, anything that's causing instability in A97 is going to cause
instability in A2K (or any later version of Access), and it will
probably be worse.

There are perfectly valid reasons for upgrading.

Stability is not one of them, ever.

 

[Alex White MCDBA MCSE]

Hi David,

I totally agree, that is the point I was trying to make, out there in the
field amongst the user base there is the perception that the latest version
is going to be faster/more reliable I know that is not true, in my personal
view what made Access 2000 a 'better' (for want of a better word) was
suddenly the integration into SQL seemed much deeper. I inherit most of my
work from other programmers who seem to have run out of steam, their excuse
for their inability to deliver a working system was to blame the version of
Access. The most important thing to a good running Access database power of
the computers that it runs on, and that they are reliable. The point I was
trying to make is proven by my never advising on Access 2002 or 2003 unless
there is a compelling reason. We all have those jobs where they are
compacting a repair a bit too often, e.g. one a day, last year I had one
such client 1.4GB data, 15 concurrent users, running Access 2000, all I did
in that situation (as they were an SBS client, fully entitled to SQL) was
upsize the back-end to SQL, no one has had to get involved in that system
since. Having used every version of Access my favourites being v2,v97,v2000
it is horses for courses.

So in complete agreement with you,

 

[Larry Linson]

"...in a multi-user environment, split
the database front-end/back end "

What does this mean and how do
I do it?

Best collection of info and links about multiuser performance and avoiding
corruption that I know about is MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm. Browse around there and you'll find a
world of information, including advice about splitting.

The "why" is: If you have multiple users logged in to the same front-end or
monolithic database, you significantly increase the probability of database
corruption.

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

"Alex White MCDBA MCSE" wrote

Having used every version of Access my
favourites being v2,v97,v2000
it is horses for courses.

See my comments re: v2000 in comp.databases.ms-access in this thread. My
posting host for USENET doesn't carry all the microsoft.public.newsgroups.

 

[Alex White MCDBA MCSE]

Hi Larry,

Read your comments, it is interesting the different views we all have,

The funny thing for me is this, I wrote an Access 2000 ADP project in 1999
and with permission from MS I was allowed to deploy this application before
the release of Office 2000, this application has run faultlessly since
deployment and none of the service packs were applied in response to
specific problems, more out of the client insisting that they have the
latest SP's something that I have no control over. I would go as far as to
say that in the 13 years I have been developing Access applications it could
be said that, the specific application has had more man hours of users
beating it in every direction given it is run by 150+ users 6 days a week
and the whole business relies up on it and I don't ever get phone calls to
support it, ok the data is stored in SQL7 and that does make a difference in
high volume/large amounts of concurrent users.

I am interested in what makes Access 2000 not a good product, I do agree
with Access 95 I had all sorts of problems with that.

 

[David W. Fenton]

I am interested in what makes Access 2000 not a good product, I do
agree with Access 95 I had all sorts of problems with that.

I don't do ADPs, nor do I ever intend to, so I don't know anything
about them beyond what I've read.

But with MDBs and Jet data sources, A2K is completely unusable in
any release before SR1 (Larry says Office SP3, but I've found that's
not necessary, nor desirable for sites using Outlook who want to
avoid the Draconian Outlook "security" "fix"), and with any version
of Jet 4 before SP6. The latter is now less of an Access-specific
issue, since Jet 4 is now shipped with the OS (since Win2K), as it
is used for storing the Active Directory database. If you've
service-packed your OS, you've already probably gotten a decent
version of Jet 4.

The problems with pre-SR1 Access and Jet 4.0 before SP6 are severe
enough that I log the versions of MSACCESS.EXE and MSJET40.DLL for
each user logon to any of may applications where I don't have full
control over the configuration of the desktops. That way I can
inform the sysadmins when a machine has reverted to a bad version of
Access 2K.

The problems with Jet 4.0 before SP6 were quite severe, real
show-stoppers.

 

[(PeteCresswell)]

Per Alex White MCDBA MCSE:

ok the data is stored in SQL7 and that does make a difference in
high volume/large amounts of concurrent users.

I'd also say that it makes it immune to backend DB corruptions - which I've seen
be a major problem if/when something in the LAN environment is not right.

 

[Alex White MCDBA MCSE]

Hi David and Peter,

I'm glad I asked the question now, because in both instances I don't have
the problems that both of you have experienced, for one I believe that
Outlook is one of the worst programs that MS ever wrote, and I never have
anything to do with it in a programming sense I fully understand the issues
with SP3 in office as I support more than 4000+ desktops with varying
applications, got loads of workarounds. I have completely written my own
emailing functions that I have been using for years now due to my complete
lack of respect for Outlook for anything other than a simple email
front-end. The Jet engine is great and I have many programs out there in the
field that just run and run, where I feel it lacks is in high volume
data/users and in those situations it's a SQL back-end every time, but I
have been doing that with every version of Access, it's just with 2000 (or
better) SQL was better integrated. As I said in an earlier post it is
interesting the different views/experiences we all have personally I am not
happy with either of the newer versions of Access beyond 2000 and I struggle
to find compelling reasons to upgrade to either from Access 2000. I have a
working solution that does it for me every time, Windows2000
Server/Windows2003 Server WinXP SP2 clients, Office 2000 With SP3, Latest
MDAC's latest Critical Updates, and on bigger installations SQL 2000, I put
these jobs in a don't here about any problems with my work, if anything I
phone them every 5-6 months asking if it is all going well.

With respect our views differ, but that what these newsgroups are about, if
we all thought the same, then just one person could answer all the
questions.

 

[David W. Fenton]

Per Alex White MCDBA MCSE:

I'd also say that it makes it immune to backend DB corruptions -
which I've seen be a major problem if/when something in the LAN
environment is not right.

But that happens very, very seldom. I've only seen actual corruption
to the point of data loss a couple of times in 10 years of Access
development (all Jet back ends). And that happened in only two
different kinds of cases:

1. the client was doing no maintenance on the db at all, and storing
it on a workstation running unstable software that was constantly
crashing. The most memorable of these was back in the days of
Win3.x, and the client was running WordPerfect 6.0 on the
workstation where the Access 2 data file was stored.

2. in Access 2000, before the arrival of Jet 4 SP6, I had one client
who did lose 3 records to corruption, ones that couldn't be
recovered after a corrupted primary key index.

I also once corrupted a client's database by running a huge append
operation against the live data file, and then killing the Access
process. I intended to run it against test data, but made a mistake.
My second mistake was killing the process. The file had to be sent
to Peter Miller for recovery and all the data was recovered.

I currently have a client with an unreliable network who is getting
"disk or network error" messages frequently, and they have not as
yet experienced any corruption of their replicated Jet 3.5 back end.

So, I'd say that Jet is not all the prone to corruption at all.

Of course, I'm defining corruption to be restricted to
non-recoverable corruption, since the only reason server databases
don't lose data is because you can run the transaction logs to
rebuild a corrupted data store. That's not the same as never
corrupting the data store (which does, in fact, happen), so, since
you're giving the benefit of the doubt to the server back end, I'm
giving the same to Jet.

 

[David W. Fenton]

I'm glad I asked the question now, because in both instances I
don't have the problems that both of you have experienced, for one
I believe that Outlook is one of the worst programs that MS ever
wrote, . . .

As an Internet email client, yes, it's garbage. And given its
dependencies on IE, it's pretty insecure. But. . .

. . . and I never have anything to do with it in a programming
sense I fully understand the issues with SP3 in office as I
support more than 4000+ desktops with varying applications, got
loads of workarounds. . . .

Well, I'm not the sysadmin for all of my clients, so I don't get to
choose whether or not they use Outlook. Thus, I have to recommend
the least invasive procedures for getting Access to run safely. And
that means NO SERVICE PACK 3 for those who are using Outlook, unless
the client decides they want to apply it themselves.

If I were in control, they wouldn't be using Outlook at all!

. . . I have completely written my own emailing
functions that I have been using for years now due to my complete
lack of respect for Outlook for anything other than a simple email
front-end. . . .

It sucks as a standalone email client.

It is only a good program when used in conjunction with Exchange
Server, where you get lots of useful functionality that just isn't
there in the standalone configuration.

. . . The Jet engine is great and I have many programs out
there in the field that just run and run, where I feel it lacks is
in high volume data/users and in those situations it's a SQL
back-end every time, but I have been doing that with every version
of Access, it's just with 2000 (or better) SQL was better
integrated. . . .

This is of no use to developers like me who don't develop apps to
run against SQL Server. If you did more pure-Jet development, you'd
be just as wary of Access 2000.

 

[Alex White MCDBA MCSE]

David,

Thank you for you comments, read both of your posts, the difference between
your thoughts and mine are not worlds apart, we both live in the real world
not the one that MS prescribes for us (I am an MCDBA,MCSE the company that I
own is a MS gold Partner so I don't take my relationship lightly with MS),
but I will stand up and be counted on a number of issues related to their
products. What I will say in their defence is this, they strive to make the
best software products in the world, and when they are clearly behind they
will try a partnership or buyout, I have no problem with that, they also DO
listen to what people want, sometimes that can take a couple of versions, I
am prepared to wait. I will stand here and say I totally believe that Access
is one of the best (if not the best) product(s) they have ever been involved
in. As a programmer in a number of different systems (only one not MS being
perl currently) I am in the business in providing solutions to my clients
that solution has to be based on a few points, functionality, reliability,
cost (some people think this is the most important point, they all learn the
hard way), speed of delivery.

What the point of my reply is this,

A good database design is everything, without it, walk away from the
project, stability is in the foundations not the front-end, Normalizing to
3NF seems to produce the best all round results (base on time/cost). I came
from the DBase III world, Clipper (Still got loads running in the field)
Access was a revolution to me. I will stand by my original statement about
Access 2000 as all of my clients (that I have provided Access 2000 solutions
too) are still with me and they don't think I have had them.

In my humble view the only database that don't suffer the corruptions (at
the level) of MDB's are network level DB's like SQL as they are far more
robust due to network level transactions. Nearly all the true file level
databases corruptions I have seen could be associated with poor networks etc
not specific versions of Access. This is not me saying the products are
without fault, it's understanding the faults.

Good to speak....

I will respect others views, I may not agree with them.

 

[ship]

I must confess that I am slightly out of my depth trying to follow this
tread. But am I correct is thinking the receieved wisdom so far here
is:

a) Access2000 is fine.
Anything beyond Access2000 is no faster/no more reliable and may not be
worth upgrading to (unless you need the new features)

b) For larger installations use SQL.
(not sure what this would be involved to do this - does a good enough
version of SQL come with Access? [Jet something or other??] Or are you
better to buy some other engine er "middleware"[??] - sorry to ask
dumb questions)

c) Split the database.
For increased speed and stability the database should be "split" so
that each user has a copy of all the forms locally.

Correct so far?


But what about OUTLOOK?!

We use msOutlook extensively - including to do mailshots to our
customers. (Database size c. 30K).

Outlook seems to be extremely slow to run and always has to be helped
though this process because it keeps crashing as it creates the
outgoing emails.

Aside: Outlook's search facility is absolute garbage. Slow,
counter-intuitive and without syntax!! But I get round this by using
Google Desktop which finds everything in a trice.

Personally I *hate* msOutlook with a passion. I find the entire system
particularly the menuing structures massively counter-intuitive.
Furthermore the Rules for sorting out the emails seem to have bugs in
them. (e.g. Try filtering incoming emails on contact Group!)

I also have the problem of having one machine that is already running
MS Office2002, whereas the rest of our PCs in the office (ie. c.10
machines) are still running MSOffice2000. I work part time in two
physical places, on two different PCs. So I simply copy my entire
"mailbox.pst" file from one machine to another (using an iPod FWIW!).
This more or less works. BUT the big problem is that the RULES always
seem to get corrupted whenever I copy between two machines and have to
be re-entered more or less from scratch.

So... even if MSAccess2002/2003 is not better than Access2000, I guess
we were hoping that the corresponding Outlook versions might be better.



Our local harward/software supplier want to sting us about GBP65 *per*
*PC* (!!), plus the MSAccess2003 software cost. We certainly can't
afford GBP600 for... ...essentially nothing - so forget that!

But maybe if we could install MSAccess2003 *ourselves* to save money
(or possibly MSAccess2002? to save further money), then that would be
worth doing.

The risk here is that our entire business now hinges on this ms Access
database, and if the upgrade goes wrong in anyway then
we would be in quite a lot of trouble!

==> Any thoughts?


Ship
Shiperton Henethe