Access 97 to Access 2002

[Praveen]

Hi,
I have a database prepared in Access 97 and now i want
to upgrade to Windows XP and Access 2002. Direct
modifications to the database would be done using Access
2002 directly. Does this upgrading to Access 2002 will
pose any problem with my existing applications(My
applications use Jet Engine and ODBC).
Please mail me at (e-mail address removed). Thanks in
advance.
Cheers
Praveen

 

[Wayne Morgan]

Replies are sent to the newsgroup so that everyone can benefit from the
answer.

For the most part, upgrading the files goes well. Make a backup copy first
to be safe. If you have problems with the upgrade, post back with the
error(s) received.

The one that occurs most is that Access 2000 and newer use ADO by default.
Access 97 and older use DAO. Access 2000 and newer can also use DAO, but
there is a catch. The References are used in the order that the checked ones
are listed in the References window under Tools|References in the VBA
editor. ADO and DAO have some object, such as Recordsets, that have the same
name, so Access goes with the first one it finds. By default, this will be
ADO. There are 2 ways to work around this.

1) Go into the References dialog and move DAO above ADO so that it is found
first.

2) The better option is to adjust your Dim statements. Open the VBA editor
and do a Search/Replace. The scope should be the entire project. Find What
should be "As Recordset" and Replace With should be "As DAO.Recordset". This
will explicitly specify which of the two collections you want to use,
removing the ambiguity. Do this for All DAO object. Be carefull when doing
the Replace. If you tell it Replace All, it will do just that. In this case,
it will probably be safe, but it may be better just to tell it Find Next
then click Replace on each one as they are found so that you can verify that
you are replacing what you want to.

 

[Allen Browne]

Most of the A97 stuff works just the same in the later versions, with a few
exceptions and modifications. However, most of the newer stuff is either of
no benefit for data stored in Access tables, or else does not work properly
or is a hindrance to productivity/performance.

Some pointers:
1. Consider keeping your A97 as well as A2000. You can do that with a custom
install of Office 2000. If you have any difficulty getting them to co-exist,
see michka's article at:
http://www.trigeminal.com/usenet/usenet019.asp?1033

2. If a database will not convert, use A97 to decompile it first. While
Access is not running, enter this at the prompt. It's one line, and include
the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

3. If you use ActiveX controls (such as calendar), the version will probably
break when converted. Fix it through References on the Tools menu (from a
code window).

4. By default, A2000 and 2002 do not include a reference the DAO library, so
you need to set up a reference to Microsoft DAO 3.6. More info. on
references:
http://allenbrowne.com/ser-38.html

5. A2000/2002 do include a reference to the ADO library, which duplicates
objects such as Recordset, Field, Property. Either remove the ADO reference,
or explicitly disambiguate everywhere in your application, e.g.:
Dim rs As DAO.Recordset

6. The "Name AutoCorrect" has myriads of bugs and performance problems. Turn
it off immediately you create any A2000 database. More info:
http://allenbrowne.com/bug-03.html

7. There are new properties that give performance problems (such as
SubDataSheetName for tables), and some existing properties have had their
defaults changed in a way that could compromise your validation code (e.g.
AllowZeroLength for code). More info:
http://allenbrowne.com/bug-09.html

8. There are serious problems with some of the new features in JET 4, such
as the Decimal data type. More info:
http://allenbrowne.com/bug-08.html

9. Access 2000/2 is incapable of saving just the object you are changing
like
A97 does. That means concurrency problems (2 people cannot work in different
objects in the same mdb at once), and it takes longer to save if the mdb has
many objects.

10. Instead of the integrated development environment in A97, A2000 and
later have a version of Visual Basic cobbled together with the main Access
window. That's handy if you with with dual monitors, but does cause other
problems from minor annoyances with way the windows behave through to Access
getting completely confused about what goes with what if you have different
versions of Access open at the same time, thereby corrupting the database.

11. It is absolutely essential to have the service packs for A2000/2. The
bugs
are so many and so drastic that simply importing code from A97 and closing
your database is enough to corrupt it if you do not have the service
packs!!! Details:
http://support.microsoft.com/?id=304548

12. Many of the known bugs in A97 have not been fixed. Hundreds more were
introduced in A2000, and many of those remain unfixed in A2003. In our
experience, you can expect to waste time with a couple of new bugs you have
not come across before in every mdb you develop. Some of these are listed
here:
http://members.iinet.net.au/~allenbrowne/tips.html#flaws

 

[Allen Browne]

This reply has been expanded, and is now available as a web page article at:
http:///ser-48.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Most of the A97 stuff works just the same in the later versions, with a few
exceptions and modifications. However, most of the newer stuff is either of
no benefit for data stored in Access tables, or else does not work properly
or is a hindrance to productivity/performance.

Some pointers:

[snip]