How Long Will Access 97 Work

[doyle60]

As I mentioned on a recent subject posted as "To Change Main Key Field
Input Mask After 10 Years," I have a very involved database, one that
has been growing and growing for 10 years now. It is extremely
complicated, involving over a thousand queries and hundreds of
tables. It is split and has over 50 users in different states and
uses ODBC to connect to a mainframe.

But the dang thing is Access 97. Management is concerned. How long
will we be able to use it and put it on new computers? I was told by
my tech 5 years ago to transfer the thing to a newer version of
Access. But I told him that it is not compatable and doing so would
mean rebuilding it from scratch. But here we are 5 years later and we
are going strong. No problems.

But the subject has come up again. I'd prefer not to do anything.

What should I do? I'd rather grow the database into new areas. I
have plenty of projects. I'd rather not go backwards and loose a
bunch of functionality.

Matt

 

[John Spencer]

On the other hand upgrading it to Access 2003 should be fairly easy to
do. Get a copy of Access 2003 (if you can) and convert a copy of your
database to Access 2003.
Try to compile it.
Fix any syntax errors.
Test it.

In all probability if it works in 97 it will work in 2003. You might
have a few reference problems, but those usually can be fixed easily.

Upgrading to Access 2007 may also work, but if you have custom menus you
will have additional work to do with them if you want to keep the look
and feel of the application the same.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[doyle60]

John,
I thought I could not just copy it over to Access 2003. I thought it
was incompatable. That is what this news group always told me.

But more importantly, do you agree with Chris, nonetheless, that it is
just not necessary. Of course, I'd always proceed in a test
environment, but I don't want to waste people's time, tech and all,
nore do I want to waste company money on something that is just
needless.

Also, I hope others would chime in too even if you do agree so I can
claim that x amount of experts say this.

Matt

 

[John Spencer]

There is nothing wrong in keeping the database in Access 97.

Eventually you will not be able to install it on new computers because
it will fail to work. HOWEVER, there is no way to know how far into the
future eventually is.

Last year, I moved my last Access 97 database to Access 2002. I wasn't
thrilled with that choice, but the customer no longer had installable
versions of Access 97 available and they had upgraded to Access 2002.

I was hoping for Access 2003 as I have found it to be more stable than
2002, but I usually do what the customer needs and try to avoid forcing
them into my solutions - I do try to persuade them to make a better choice.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dale Fye]

Matt,

As John mentioned, the major incompatibility you are likely to experience in
upgrading from 97 to 2003 is that ADO is the default data model in 2000 and
above, whereas DAO is the default in 97. If you do nothing after you
upgrade, you are likely to find a number of instances where you have used
methods and properties of DAO that are not compatible with ADO.

If you upgrade to 2003, the first thing I would do is go into the VBA code
window (this will be a change for you from '97) click on Tools, then
References, and then uncheck the reference to ADO. Then scroll down and
check the reference to Microsoft DAO.

*****************
If you keep references to both data models, then you will likely run into
problems with code that looks like:

Dim rs as Recordset

Set rs = currentdb.openrecordset(...)

Since both data models have a recordset property, you will need to
disambiguate this code by modifying the declaration to:

Dim rs as DAO.Recordset
***************************

Once you have removed the reference to ADO and checked the reference to DAO,
I would recommend that you recompile your code and fix any discrepancies,
like references to early versions of other Office applications. There may be
a few other inconsistencies, but I wouldn't expect them to be many.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[doyle60]

John,
I'm sorry but I'm a bit confused.

1) How do I change my database to a 2003 database? Do I just install
Access 2003 on my computer and take my Access 97 database and save it
as a 2003 database? I thought that that was just not possible. Are
you saying it is but you will have to fix the reference thing and
maybe a few other problems with some code?

2) Are you suggesting to only go up to 2003? Why not use the latest
version of Access? Isn't there an Access 2007?

3) Is there going to be a later version soon and should I wait for
that?

John wrote:
"Eventually you will not be able to install it on new computers
because
it will fail to work. HOWEVER, there is no way to know how far into
the
future eventually is."

Is it wiser to just wait until that time? It won't happen all at
once. It will only happen on the new computers and when we start to
see it, we should then update it, no? That way, we would be using the
latest version.

Dale wrote:
"If you upgrade to 2003, the first thing I would do is go into the VBA
code
window (this will be a change for you from '97) click on Tools, then
References, and then uncheck the reference to ADO. Then scroll down
and check the reference to Microsoft DAO."

Yes, I do this sort of thing already on some computers after they have
to do an update on something that I changed or added.

If I go over to a later version, is it only VBA code I need to worry
about, and not SQL code, that is, queries? I have hundreds of queries
but only 30 modules, though VBA code is all over individual forms, I
guess.

John and Dale and all,
If you were to advise management what to do, considering the enormity
of the database and costs, what would you advise? Wait? or change?

Matt

 

[John W. Vinson]

John,
I'm sorry but I'm a bit confused.

1) How do I change my database to a 2003 database? Do I just install
Access 2003 on my computer and take my Access 97 database and save it
as a 2003 database? I thought that that was just not possible. Are
you saying it is but you will have to fix the reference thing and
maybe a few other problems with some code?

Install 2003. Make a copy (!!! not the original !!!) of your A97 database.
Open it with A2003; accept Access' offer to convert the database. You might
not have ANY problems; if you do, correcting the references will probably fix
them, and that's easily done.

If anyone told you that it's impossible to upgrade A97 to a later version,
they were mistaken (or worse).

2) Are you suggesting to only go up to 2003? Why not use the latest
version of Access? Isn't there an Access 2007?

The user interface and security model changed dramatically with 2007. For
instance if you have custom menus or toolbars, they'd need to be completely
redone using the ribbon interface that's new with 2007, or else you'll need to
jump through some hoops to use the old interface. In addition, upgrading to
the new .accdb format from .mdb will make Access workgroup security
unavailable, if you're using that; Replication is also out the window.

3) Is there going to be a later version soon and should I wait for
that?

They're working on the new version now; it will have all the same barriers as
2007 does and more.

If I go over to a later version, is it only VBA code I need to worry
about, and not SQL code, that is, queries? I have hundreds of queries
but only 30 modules, though VBA code is all over individual forms, I
guess.

Your SQL should be fine. I've got queries that were written in Access 2.0 a
decade ago, and they work in 2003 and in 2007.

John and Dale and all,
If you were to advise management what to do, considering the enormity
of the database and costs, what would you advise? Wait? or change?

Both! Get 2003, or if you're ambitious 2007, for development use. Let your
users keep running 97 (probably the best version of Access *ever*); try it
with the more recent version, and decide which of the new features
(conditional formatting, different macro handling, the ribbon) you might want
to use to enhance the user experience. Maybe it's fine as it is; my guess is
that going to 2003 will be pretty painless, and the only real pain with 2007
will be your own getting used to the changes in the user interface.

 

[David W. Fenton]

Your SQL should be fine. I've got queries that were written in
Access 2.0 a decade ago, and they work in 2003 and in 2007.

A question that's come up for me recently in planning a project
that's going to need to produce DDL for schema creation that will be
compatible across as many db engines as possible:

Is it a good idea to start switching to SQL92 format? I loath the
idea of switching away from the old Jet-standard wild cards, but
would love to be able to get away from the horrid bracket syntax for
derived tables (which often limits your ability to even use a
derived table at all, and prevents nested derived tables).

 

[John W. Vinson]

A question that's come up for me recently in planning a project
that's going to need to produce DDL for schema creation that will be
compatible across as many db engines as possible:

Is it a good idea to start switching to SQL92 format? I loath the
idea of switching away from the old Jet-standard wild cards, but
would love to be able to get away from the horrid bracket syntax for
derived tables (which often limits your ability to even use a
derived table at all, and prevents nested derived tables).

Access/JET's peculiar dialect of SQL has never "played nice" with DDL, in my
experience - one of the hurdles I had to overcome coming into Access from the
Oracle world. I have not experimented much with switching formats, so I really
don't know what the issues might be! This would be worth a discussion on a
broader basis - perhaps one of the blogs?

 

[Rick Brandt]

Access 97 runs on Windows 95 through Windows XP. When the new pcs come
with Vista or later, if you install vm software (virtual machine) on it
for an earlier os, you can still run Access 97 apps.

Access 97 works on Vista. You might need to use a compatibility mode or
"run as administrator", but you certainly don't need to go as far as a
virtual machine to make it work.

 

[david]

I never got DAO/ODBC transactions to work properly with Jet 4.

In Access 2000/2003 using linked tables the transaction used multiple
connections and the connections deadlocked.

If your application has transactions, test to see if this has been fixed
before changing from A97 .


(david)

 

[David W. Fenton]

Access/JET's peculiar dialect of SQL has never "played nice" with
DDL, in my experience - one of the hurdles I had to overcome
coming into Access from the Oracle world. I have not experimented
much with switching formats, so I really don't know what the
issues might be! This would be worth a discussion on a broader
basis - perhaps one of the blogs?

Well, it's two different topics, i.e., DDL and SQL92. I've never
used DDL at all in Access (why would anyone need to?), but if you're
working in an enviroment that has to be flexible with the back-end
database engine, you need to use DDL at some level, and that's the
situation I'm finding myself in now.

But the SQL92 question is completely independent of that. As I said,
I can think of at least one advantage that comes with using it, but
wonder, other than better portability of Access queries written in
SQL92, whether there's any real advantages in using it.

 

[David W. Fenton]

:

I was told by
my tech 5 years ago to transfer the thing to a newer version of
Access. But I told him that it is not compatable and doing so
would mean rebuilding it from scratch.

What in your app is incompatible?

I just upgraded my last Access 2 app to 2003 last summer. The reason
it took so long was because I used two 16-bit OCX's (tab strip and
multiselect listbox) and it took a lot of work to replace those with
the built-in Access controls. But it was worth it, and didn't take
nearly as long as I'd expected it to take.

Maybe if you'd tell us about your specific issues, we might be able
to set your mind at ease.

 

[david]

I've never used DDL at all in Access (why would anyone need to?),

We use it to script database schema updates.

(david)

 

[doyle60]

Thanks David. I think I just misremembered a few things. I got Access
2003 mixed up with Access 2007.

I'm not technical about these matters. But if I am reading all here
correctly, it seems like a waste of time to upgrade to 2002 or 2003.
It does not seem it would ever do anyone any good but would just cost
a bunch of time and money.

I believe I should advise management that we should upgrade only when
we begin to see we have to. That day hasn't come yet and doesn't seem
like it will come too soon.

Matt

 

[David W. Fenton]

[quoting me:]

We use it to script database schema updates.

First off, an app that has frequent schema updates was released into
production use prematurely, or is badly designed.

Secondly, why not use DAO, which can do a lot more than DDL in terms
of changing Access-specific properties?

 

[david]

Because we also have SQL Server customers, it was convenient
to script schema changes as DELL.

I'm sure that your schema is badly designed, that wasn't the case here.

(david)

[quoting me:]

We use it to script database schema updates.

First off, an app that has frequent schema updates was released into
production use prematurely, or is badly designed.

Secondly, why not use DAO, which can do a lot more than DDL in terms
of changing Access-specific properties?

 

[David W. Fenton]

m:

But if I am reading all here
correctly, it seems like a waste of time to upgrade to 2002 or
2003.

Well, that all depends. Installing A97 on recent versions of Windows
requires some tweaks to security settings. And it doesn't run
properly on Windows Terminal Server, which to my mind is a
significant consideration.

Someday, you'll need to do a conversion. I think it's good to do it
in a version of Access that's relatively close to the one you're
starting from, and so I'd recommend an upgrade from A97 to A2K3.
There are significant differences between A97 and A2K that can cause
many things to break. The most obvious to me are that form
references used in queries need to be declared as parameters in A2K3
in order to be resolved correctly, and that the order of event
processing in A2K forms and later is not the same as in A97 (which
can lead to all sorts of things breaking).

You're going to have to convert some day.

My thought is that it's better to do it while there are still plenty
of resources around to help you do it. That is, 5 years from now,
there may not be very many people around who recall the process well
enough to help you. But right now, there are plenty of people who've
got the experience and can help.

 

[David W. Fenton]

I'm sure that your schema is badly designed, that wasn't the case
here.

Well, frack you.

<PLONK>

I recently encountered a question about updating the schema on
UtterAccess.com. It turned out it was a questionaire-type database,
and the developer was adding fields to tables as questions changed.
That is a typical schema error, and if it had been properly
designed, adding questions would not have required schema changes.

 

[Rick Brandt]

There are
significant differences between A97 and A2K that can cause many things
to break. The most obvious to me are that form references used in
queries need to be declared as parameters in A2K3 in order to be
resolved correctly, and that the order of event processing in A2K forms
and later is not the same as in A97 (which can lead to all sorts of
things breaking).

Can you provide details on these two issues? I have not encountered
either of them. Is the form reference thing only when using the
2002/2003 file format? I have lots of users running the 2000 file format
of apps developed in 97 and then converted, and I don't recall any issues
with queries.