Procedures for Converting an Access 97 MDB to Access 2002 
Author Message
 Procedures for Converting an Access 97 MDB to Access 2002

 I am a contractor who supports a client with an Access 97
application. This application was originally written in
Access 2 (or prior). It currently runs on Windows 95 in
Access 97. It was converted three years ago from Access 2
to Access 97. The application has many thousands of lines
of VBA code with many dozens of forms, queries, modules,
etc. The client wants it converted to run as an Access
2002 application (under Office XP) on Windows NT 4.0 SP6.
 Before embarking on the conversion, the client wants an
estimate of the size of the effort (in person-hours). In
order for me provide this estimate, it would be helpful to
get a detailed discussion of exactly the things that need
to get converted. I know that at a high level:
- The old DAO used in the application has to get replaced.
The client wants it to get converted directly to ADO.
- The VBA Object Library is changed, and some things are
no longer supported. For example, the application current
uses Format$ which causes an error in Access 2002.
- There are some additional reserved words which may cause
problems with variables that have the same names.
 I suspect that there should exist some sort of detailed
white papers or other procedures to help out in this
effort. So far my web searches have only turned up high
level discussions and a reference to a software product
(OfficeConverter) that may or may not be worthwhile.
 Are you aware of any detailed procedures that would:
- Describe how to convert DAO to ADO.
- List the things that are no longer supported (e.g.,
Format$), or supported differently in Access 2002 (vs.
Access 97).
- List the new reserved words in Access 2002?


Sat, 10 Sep 2005 04:25:29 GMT  
 Procedures for Converting an Access 97 MDB to Access 2002

Quote:

> - The old DAO used in the application has to get replaced.
> The client wants it to get converted directly to ADO.

I wonder why they want to do this? There is no real reason to convert this
code to ado. There is certainly no performance advantage. If you have a lot
of DAO code, then you will have to re-write a lot of code for absolute zero
benefit, and good chance that many bugs and fixes you have made over the
years will be lost. I see no reason why to convert the code from dao to ado.
You will still be using JET, and that thus means you have DAO installed on
your pc.

It is your responsibility to inform the client of this. I suppose if you
need lots more work then you can most certainly re-write your dao code, but
I see no reason to convert the code.

Quote:
> - The VBA Object Library is changed, and some things are
> no longer supported. For example, the application current
> uses Format$ which causes an error in Access 2002.

Hum, you might want to show what that format command looks like. I have had
ZERO trouble with the format in a2002.

Quote:
> - There are some additional reserved words which may cause
> problems with variables that have the same names.

There is not many. I just converted a access97 application to a2002.

The follow stats (lines of VB code) are:

forms code        = 15822
reports code      = 2127
code in Modules   = 8072
                  -----------
total lines of code = 26021

number of Forms   = 158
number of Reports = 68
number of tables  = 62
number of modules = 22
number of Queries = 175

So, you can see that I had 22 modules of code (3 class objects), and 26,000
lines of code.

The whole application converted flawlessly without even so much as a hiccup.
That 26,000 lines of code EXTENSIVELY used dao, and it all works just fine.

That above code even has some old legacy QuickBasic code hat uses the old

open "some file" for input as #1

The above type of code worked on the first gw-basic that came out for the
IBM pc in 1982. So, while I can't say that all conversions from a97 to a2002
will be as flawless as the above, not ONE error has come to light so far.

About the worst thing I saw was that a2002 uses the VB message box, and a97
uses a different msg box (its own). The stupid "application title" in a2002
is thus NOT the default for a msg box when you leave it blank. So, I will
eventually have to get around to fixing some of the msg boxes. I fixed about
12 of them in 5 minutes. The result of this is that you see "Microsoft
access" in the title bar of the msgbox when not set (it used to show your

anymore also. (you can use the Eval() function to still use the old msgbox,
but that is really silly). So, the msgbox title problem is hardly a bad
thing, and does not effect operation of the application.

The fixing of the msgbox stuff can wait as that is such a trivial thing.
While the msgbox was trivial, it was the worst problem I encounter!!

Having said all of the above, you do seem to be doing some planning, and
testing before the conversion. Hats off to your for taking a VERY serious
approach here. Often people think that it is simple conversion, and most of
the time this is the case. However, some testing as you are doing is
certainly recommended. Any company upgrading the office suite has to sit
down and plan the migration, as it can be a lot of work.

The other issue that came to light was that the mail merge to ms-word does
not now show the "Insert Merge Field" (it is now a icon in wordxp...dumb
change!!).

Right now, the conversion for me was 100% trouble free, and as mentioned,
the only thing that I *will* change is the msgbox code.

I consider myself a good developer, and course things like word automation
code did use late binding, but all good developers use late binding anyway.
I had just finished writing a calendar 8 replacement, and I had thus removed
the calendar 8 control from the a97 application before converting. (again,
probably would not have been a problem, but I seen WAY too many activeX
problems..and thus avoid them).

Besides, if I was a bad developer, and stuff like word automation code was
not late binding, I bet it would have worked anyway. (however, I can't admit
to ever wanting to find this out).

At the end of the day, if you followed good solid development practices,
then the conversion will go well.

here is one article of use for you:

Office 97 to Microsoft Office XP Migration Issues
http://www.microsoft.com/office/ork/xp/journ/Xpdelta.htm

Good luck, as my conversion was a very positive experience.

--
Albert D. Kallal
Edmonton,  Alberta Canada

http://www.attcanada.net/~kallal.msn



Sat, 10 Sep 2005 10:22:53 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Converting VB Code from Access 97 to Access 2002 or higher

2. Converting Access 2.0 to Access 97/2002

3. Access 97 upgrade to Access 2000 or Access 2002

4. Tips on migration from Access 97 to Access 2002

5. Access 97 vs Access 2002 (XP)

6. TransferDatabase method from Access 95 mdb to Access 97 mdb

7. Converting mdb file to Access 97

8. convert Access 2 in Access 95 and Access 97

9. Create MDE with Access 97 / 2002 problem

10. Missing Libraries Access 2002 vs. 2000 and 97

11. Automatic conservion or importing to Access 97 from Access 2.0 MDB

12. Access 97 vs 2002 (XP)

 

 
Powered by phpBB® Forum Software