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

[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

 

[Alex White MCDBA MCSE]

Hi,

a) yes

b) yes, Office comes with a cut down version of SQL server called the MSDE,
it is downloadable and free from MS it comes with no developer tools, and
you are expected to do all your development work within Access, or you can
purchase the developer edition of SQL server reasobly cheap, or the full SQL
version I don't know the price but it works out expensive once you start
adding CAL's (client access licences).

c) in a multi-user environment, always split the database, for performance
and reliability reasons.

Well I think you can see my view on Outlook, great as a end-user email
manager, absolutly usless as an intergrated emailing system within a
automated application. I have written several mailers that send anything
from 1 - 50,000 emails per go, for these systems to work, skip Outlook it
just does not have the mucle to do the job, writting a socket application
that communicates directly with an SMTP server is the best solution here.
Outlook 2003 is the best version of Outlook by far, I have many clients that
are running the latest version of Outlook with previous versions of Access,
one of the major problems with Outlook integration is this the data is
unstructured by design, Access on the other hand is structured data e.g.
open a table in access and every record has the same fields, this is not the
case with Outlook, each email/contact/others can have a different record
structure making life very difficult from a programming perspective.

The other problem with using Outlook is this send a large bulk email from
outlook, and your exchange server will get very upset with the workload.
Then try to find out who got the email and who did not, yes can be done but
difficult.

Your software vendor wants to charge you £65 per computer to install Office
2003 or is that price selling you a copy of the software?

So you have 11 workstations, how many servers?

what Operating System software do you have on the server(s)?


--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

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

 

[Brendan Reynolds]

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!

In that case, there's really only one thing to do - test your app, with a
copy of the data, under Access 2003 in a test environment before you even
think about rolling it out company-wide. Don't get me wrong, most upgrades
go smoothly. But if the business depends on the app, don't take chances, not
even small ones.

 

[adsl]

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.

 

[adsl]

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,

 

[adsl]

"Alex White MCDBA MCSE" wrote


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.

 

[adsl]

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.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

 

[adsl]

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.

 

[adsl]

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.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

 

[adsl]

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


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!


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.


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.

 

[adsl]

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.

 

[adsl]

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.

 

[adsl]

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

 

[adsl]

Hi,

a) yes

b) yes, Office comes with a cut down version of SQL server called the
MSDE, it is downloadable and free from MS it comes with no developer
tools, and you are expected to do all your development work within Access,
or you can purchase the developer edition of SQL server reasobly cheap, or
the full SQL version I don't know the price but it works out expensive
once you start adding CAL's (client access licences).

c) in a multi-user environment, always split the database, for performance
and reliability reasons.

Well I think you can see my view on Outlook, great as a end-user email
manager, absolutly usless as an intergrated emailing system within a
automated application. I have written several mailers that send anything
from 1 - 50,000 emails per go, for these systems to work, skip Outlook it
just does not have the mucle to do the job, writting a socket application
that communicates directly with an SMTP server is the best solution here.
Outlook 2003 is the best version of Outlook by far, I have many clients
that are running the latest version of Outlook with previous versions of
Access, one of the major problems with Outlook integration is this the
data is unstructured by design, Access on the other hand is structured
data e.g. open a table in access and every record has the same fields,
this is not the case with Outlook, each email/contact/others can have a
different record structure making life very difficult from a programming
perspective.

The other problem with using Outlook is this send a large bulk email from
outlook, and your exchange server will get very upset with the workload.
Then try to find out who got the email and who did not, yes can be done
but difficult.

Your software vendor wants to charge you ?5 per computer to install Office
2003 or is that price selling you a copy of the software?

So you have 11 workstations, how many servers?

what Operating System software do you have on the server(s)?


--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

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

 

[adsl]

In that case, there's really only one thing to do - test your app, with a
copy of the data, under Access 2003 in a test environment before you even
think about rolling it out company-wide. Don't get me wrong, most upgrades
go smoothly. But if the business depends on the app, don't take chances,
not even small ones.

 

[adsl]

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

 

[David W. Fenton]

But what about OUTLOOK?!

My view of it is that if you're not using Outlook with Exchange
Server then you shouldn't be using Outlook at all, since the whole
design is tied to Exchange and its capabilities. The Outlook
defaults and behavior and UI for Internet email suck and always
have, plus there's the dependency on IE (in rendering email and in
the UI), which makes it vulnerable to exploits in software outside
itself.

I've used Outlook in Exchange environments and it's really a
completely different program than standalone Outlook.

I long ago stopped thinking that automating Outlook from Access was
a good idea. It simply didn't work well enough to be worth the
effort. However, I never attempted it in an Exchange environment,
where it very well may work better.

 

[David W. Fenton]

I have written several mailers that send anything
from 1 - 50,000 emails per go, for these systems to work, skip
Outlook it just does not have the mucle to do the job, writting a
socket application that communicates directly with an SMTP server
is the best solution here. . . .

Aren't you running into problems with spam filters? I've given up on
sending email from a client PC, since it will end up trash-canned by
any ISP that uses one of the RBLs to classify mail as spam. I think
the only remaining viable way to send mass email is through mailing
lists.

. . . Outlook 2003 is the best version of
Outlook by far, I have many clients that are running the latest
version of Outlook with previous versions of Access, one of the
major problems with Outlook integration is this the data is
unstructured by design, Access on the other hand is structured
data e.g. open a table in access and every record has the same
fields, this is not the case with Outlook, each
email/contact/others can have a different record structure making
life very difficult from a programming perspective.

Well, every record *does* have the same fields, it's just that some
of them mean different things in different item types, and every
item type uses only a subset of the whole class of fields. It's a
very non-normalized data store, and using it from Access takes a
very definite change of mindset, at least for me.

My main problem with automating Outlook from Access is *speed*.
Because you're basically doing sequential access on non-indexed
recordsets, it's *very* slow with data sets of any size whatsoever.

 

[Alex White MCDBA MCSE]

Mass mailing is something to be done cautiously, when ever I provide a
solution to a client, Un-announced emails are a real problem in world wild
web, and I don't condone it at all, all my clients are using opt in systems,
with opt out systems for people that don't want the emails anymore. The
biggest problem with RBL's is Reverse NDR spamming and not real spammers. I
think RBL's are going to be a thing of the past soon, with SPF records in
DNS going to become the standard soon.

 

[adsl]

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.