The Old Joel on Software Forum: Part 3 (of 5) - conversion tools for excel97 & access98 ->XP

conversion tools for excel97 & access98 ->XP

Hi all,

Do you have experience in the company you work when migrating users from Win9x/NT to Win2K/XP esp regarding the incompatibilities between Excel macros & Access from the older version to the Office XP version? How was the process?
Do you use any tools to perform conversion or a tools that provide monitoring where the incompatibilities might occurs (on line per line basis).
Or did your company outsource it to 3rd party to perform conversion?

FYI I work for 2000+ person company, mostly engineers therefore it will be a lot of macros & programs to be changed.

Thanks beforehand

Martin Krisnomurti
Wednesday, December 3, 2003

In theory, there shouldn't be any incompatibilities with macros between Office 97 and later versions -- the Office VBA model has maintained backwards compatibility. Of course, no guarantees.

Robert Jacobson
Wednesday, December 3, 2003

Again, never say never, but I have not had any issue with excel or access and we have a few people who do some really "outside the normal" type of stuff.

If you think you have a big concern, buy a copy for XP and just try the ones in question. You should be pleasantly surprised.

If you do have an issue, I would be interested in hearing it.

MSHack
Thursday, December 4, 2003

Never had problems with Excel, but every time I have upgraded Access databases break.

Every time. The open database and save as new version just seems to break stuff.

Tapiwa
Thursday, December 4, 2003

Access databases can break because Access 97 uses DAO (the JET-only database objects) by default and Access 2000+ uses ADO (the generic database access technology) by default. Thus, when you have VBA code that looks like:

Dim rst as Recordset

Access 97 interprets that as DAO.Recordset whereas Access 2000+ sees ADODB.Recordset .

So the fix to this is to either:

-Qualify all your Recordsets to be either DAO or ADODB

-Go to the references (CTRL-G, Tools->References) and rearrange your DAO/ADO references.

This is a painfully common error.

..
Thursday, December 4, 2003

Believe it or not, the newer office 2003 with ms-access now INCLUDES the dao JET reference by default.

In other words, despite the rumbles for the LAST 4 versions for office that JET is dead, the brand new spanking office XP 2003 includes a dao references by default.

Despite what people think, Microsoft is not stupid. Removing the reference caused a increase in support calls. In addition, developers complained. MS is not, and does NOT want to break previous compatibly. To do so would cause companies to resist upgrading to the next version of office.

So, as a result of the above two issues, the JET dao reface is now back by default in the new version of office.

They done the above despite the fact that JET is in maintenance mode only. A sign of a smart company indeed. (ie: just keep everyone happy).

Note that when you use ms-access to upgrade from a97 to a newer version , such as a2002, then the DAO reference is included and set for you. All versions since a2000 have done this automatically.

However, if you import the forms from a old version (not upgrade...but import), then the reference is NOT set for you automatically. So, when possible, let ms-access do the upgrade for you.

I taken some fairly extensive applications from a97, and they converted to a2002 without a hitch.

The only real change that got me was the fact that the MsgBox command is now a native VB msgbox command, and not a ms-access native one (as it was in a97). Thus, a few options like bold text in the box are now missing in a2002. However, all msgboxs did continue to function.

The other thing (again minor) was the application name appears in all msgbox commands in a97 (when the title pram is left blank), where as in a2000 and later, all msgbox commands when blank, are blank! Anyway, you can do a global search and replace when in the code editor, and this search will also traverse the forms/reports code too. I simply put in the title a global value that I set.

The above two issues could have well been left alone in the conversion process.

The real problems with conversion from older versions is often due to the fact that often the older ms-access application has compile errors and all kinds of junk stuff in the mdb file. To get around that, I always open the old version in the old version of ms-access, and then in the debug window do a compile and save all. If there are compile errors, then trying to upgrade the code to a newer version is not a very good idea. The errors in code should be fixed BEFORE trying to run the upgrade feature of ms-access

Of course, if compile errors exist in the old code, then this also means that the application has been running for years with compile errors, and that is a bad thing. The people who allow that should be sent to the salt mines for a few years.

So, if the older application was well designed and coded, then upgrading usually runs without a hitch. If the old application was a mess, then often the upgrade does choke.

Albert D. Kallal (Microsoft Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. Kallal
Friday, December 5, 2003