Upgrading from Access 97 - any gotchas?

[Wendy]

We are running Windows XP (Media Center or Home) on our computer and Access
97 just consumes too much of the operating memory when left up all day for
our usage. We are considering upgrading to 2003. Any words of advice or
recommendation would be appreciated

 

[Douglas J. Steele]

Allen Browne has a good summary at http://www.allenbrowne.com/ser-48.html

Are you sure, though, that there's really a problem? Access 97 did have a
flaw that it appeared to be consuming all available resources, but it always
gave them up. While http://support.microsoft.com/kb/160819 doesn't mention
XP, I would assume it applies to it as well.

 

[Wendy]

I installed 97 on a new computer earlier this week with XP Media Center
version. Access97 comes up with the "not enough memory" failure message and
will not open at all. I am not even trying to open a database at this point,
just want to get the software to launch. I used the Compabitility under
Properties and next told it to run as Windows 95, but that did not resolve
anything. I do not understand why I can get it to run just fine on 3 older
computers (older as in 1-2 years older) which are also on factory installed
Windows XP either Home or Media Center versions. What is Access looking for?
I'm trying to locate a reasonably priced set of the 2003 upgrade to
install instead, but really need to understand this particular problem.
Thanks in advance.

 

[Douglas J. Steele]

You need to install the service packs. See
http://support.microsoft.com/kb/161255

 

[David W. Fenton]

We are running Windows XP (Media Center or Home) on our computer
and Access 97 just consumes too much of the operating memory when
left up all day for our usage. We are considering upgrading to
2003. Any words of advice or recommendation would be appreciated

Allen Browne's page is good, but I ran into a problem recently where
I was running an append query that was drawing some of its fields
from controls on a form. It worked flawlessly in A97 for TEN YEARS,
and when converted to A2K3 (A2K format), it broke, because it
started resolving the control references as zero-length strings,
instead of the values that were actually in the controls.

I replaced the stored queries with dynamic SQL instead, so that the
control values were resolved before the SQL was run, but if you've
got any queries that draw values from controls on forms, you'd be
wise to check them. You can make them work by setting up parameters
and wrapping any controls that might Null in a
zero-length-string-to-null function (because the Null controls
return ZLS's, too, which can be a problem in an append query running
against a table whose fields disallow ZLS).

 

[Sprk Jonz]

We recently moved from 97 to 2003 and found a coup0le of gotchas:

1- Unlike 97, in 2003 a user cannot modify a form or report if someone else
is in the database. We have a small community of about 5 users, and when
things get buys, this difference rally slows us down. Short of working in a
copy, our developers have to yell: can everyone please get out of the
database?

2- Previously I could easily write queries to perform counts and then
reference these queries in other queries. In 2003, it is much more difficult
to reference aggregate functions and while it allows it, you really have to
index everything, dot your i's, etc to make it work.

3- I've had some forms get "buggy" due to field name tracking. If I have a
mix of fields directly from my query and others that have formulas
referencing fields, the form sometimes gets confused about if the calc should
reference the form object or the query fields. Sorry, hard to make clear for
ya, but just one thing I encountered

Anything I assumed would be fixed with the new version wasn't. Exports from
Access are still as Excel 95 docs and long file names will bomb the export &
db. While there have been a few benefits (object name change tracking, web
data pages to name two), there haven't been nearly as many benefits as I'd
expected.

I'll try to think if there is anything else we've encountered.

 

[Tony Toews [MVP]]

1- Unlike 97, in 2003 a user cannot modify a form or report if someone else
is in the database. We have a small community of about 5 users, and when
things get buys, this difference rally slows us down. Short of working in a
copy, our developers have to yell: can everyone please get out of the
database?

However you really want to put the FE on each machine or place in a
user specific directory on the server. This will help avoid some
weird error messages when users are changing the same forms record
source, filters and such as well as corruptions. It is also much
easier to implement a new version of the database with changed
queries, forms, reports and VBA code.

I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the Auto FE
Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

2- Previously I could easily write queries to perform counts and then
reference these queries in other queries. In 2003, it is much more difficult
to reference aggregate functions and while it allows it, you really have to
index everything, dot your i's, etc to make it work.

I'm not familiar with this problem.

3- I've had some forms get "buggy" due to field name tracking. If I have a
mix of fields directly from my query and others that have formulas
referencing fields, the form sometimes gets confused about if the calc should
reference the form object or the query fields. Sorry, hard to make clear for
ya, but just one thing I encountered

Turn Name AutoCorrect off. This might be part of your problem. But,
assuming I understand your problem, you can't have an alias name in a
query the same name as a field.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

We recently moved from 97 to 2003 and found a coup0le of gotchas:

Here's another one that I encountered this past summer, upgrading an
A97 app that's been in production use since 1998 to A2K3:

- saved queries using references to controls on forms to provide
values for a query that worked fine in A97 may return Nulls even
when the controls are populated, and may return zero-length strings
for Null controls. The solution is to declare a parameter for the
control reference, which then has no trouble resolving it. Or, you
can write your SQL on the fly in code and avoid the control
references in a saved query entirely (I did the first as a quick
fix, and the latter as the permanent fix).