Access 97 vs Latest Version of Access

[Robert J Sims]

My day in and day out work assignment uses Access 97 for data repository and
reporting. I do pretty straightforward SQL queries and table development,
using your basic tables, joins, and
select / make / update queries.

Someone is "testing the waters" to see, after 7 years of use, if we
shouldn't upgrade to the latest Access version (Access 2003? or Access XP?).
Big picture... what am I missing in the latest version of Access that I'm
not getting in Access 97 (keep any web-enabled Wizards, web forms, etc out
of the analysis and VBA development environment)? Could I truly make a
business
justification of 30 folks buying upgrade licenses? The things that leap out
at me are:
1) more sophisticated table joins?,
2) Integration with Excel / Powerpoint for charts/reports?,
3) New types of queries (currently using select / make table, update,
delete, append, crosstab)?

Thanks for your advice...

Bob

 

[Allen Browne]

Not much, actually.

You are not interested in web-stuff, so DAPs are irrelevant (and do not work
well anyway).
You are not interested in VBA, so the integrated VB IDE is irrelevant.

Most of the stuff that is in A97 works just the same, but most of the newer
stuff does not work properly. Here are some examples.

1. The new databases attempt to track it if you rename objects, but there
are well over a dozen bugs with this feature, and it severely degrades
performance:
http://allenbrowne.com/bug-03.html

2. The new versions support conditional formatting of controls, which is a
nice feature except for the bugs:
http://allenbrowne.com/bug-05.html

3. There is a new Number field type called Decimal, but Access cannot sort
it properly:
http://allenbrowne.com/bug-08.html

4. Some of the existing properties have had their defaults changed and other
undesirable properties have been added, so you have to take the time to
reset these every time you create an object:
http://allenbrowne.com/bug-09.html

5. There are problems with libraries so that basic things like Date() and
Left() may not work, partly due to the fact that one of the basic libraries
that had always been in Access was not selected by default in Access 2000
and 2002, while another was introduced that had conflicting objects:
http://allenbrowne.com/ser-38.html

6. You mentioned queries, and there are some new features in JET 4, but may
of these do not work properly, or do not work in the interface (which was
not upgraded to support them), or can only be accessed through the ADOX
library (which is so incomplete and buggy as to be unusable).

7. Everything takes up double the resources, due to the move to Unicode.

8. Instead of just saving the object you changed like A97 does, it re-writes
the entire BLOB every time you save a change. Time consuming, and annoying.

9. The VB IDE has been completely separated from the Access window, and
there are issues with the way the 2 talk to each other.

10. There is a new security farce built into Access 2003 that warns the user
it is unsafe to use your database unless you purchase and pay annual fees
for a digital certificate which is actually unavailable for individual
developers.

11. It is really, really important to stay up with the service packs for
Office and for JET 4, since these have addressed many other serious bugs
such as duplicate AutoNumbers, corrupting a database just by importing old
code, and so on.

There is a useful Form_Undo event, but you said you were not interested in
code.

So, you are not missing much by staying with A97, except for all this stuff
that you would *want* to miss.