Upgrade Access 97 mdb to Access 2010 mdb/mde? Experiences?

Question

I have a really old, Access 97 app which needs to be modernized. I'd like to make something more modern, but it would likely be a bit to expensive to rewrite the whole applikation to a real winapp (or webapp). So the idea now is to upgrade to Access 2010. However we tried the automatic upgrade tool, and that broke. The app is rather big with 40+ forms and modules.

I just want to know if someone have a real world experience from a similar situation and/or has any advice as if it's a likely dead end to try to upgrade. Or better - should be possible.

Also - I might need some help, if someone can advice of a company that are good at these kinds of tasks.

Solution 1

There have been a lot of changes between Access 97 and 2010. Each upgrade (97 to 2003, 2003 to 2007, etc.) had quirks that you needed to be aware of. The conversions weren't usually simple tasks, especially when you had a complicated database with lots of things going on. Taking the leap from 97 directly to 2010 will be almost impossible in your situation. However, it can be done if you take it slow and address the problems at each stage. The issue is that it might take more time than you are willing to give to it.

In my search for answers for you, I came across companies that did this type of conversion for you. This, to me, is one more indication that this isn't something you just let a wizard do (otherwise, who would pay for the service). Here is one of the companies I found:

http://www.consultdmw.com/convert-access-97-database.htm

I've never used them so I'm not endorsing them. I'm just using them as an example.

There are also resources out there of people that have walked down this path and shared their experience. These might be valuable to you in determining if you want to go down this path and if so, what to look out for. Here is an example:

http://chentiangemalc.wordpress.com/2011/03/24/upgrading-access-97-application-to-access-2010/

Solution 2

I don't have Access 2010 and don't know about that automatic upgrade tool you mentioned. With Access 2007, I can import objects into a new empty MDB from an earlier version database in stages.

First import tables, and verify data imported correctly.

Next import standard and class modules. Run Debug->Compile, and fix any errors the compiler complains about. Might have to add references as needed.

Then import the forms and run Debug->Compile again.

Same for reports, if any.

Best case, you would have few fixes needed to get it running with 2010. If the amount of fixup work is unreasonable, then consider farming it out to a service.

But I suggest you at least try the manual import method first. You shouldn't have to invest too much time to see whether it's worthwhile to continue.

Comments

[Yinda Yin]

Also, "It broke" isn't much of a description.

[lanartri]

Fixing the "broken" parts can be fairly simple. Just compile your code and see where that brakes, for starting. I never had "real" problems with upgrading Access dbs, and I did a lot of them.

[David-W-Fenton]

My bet is that it's broken in A97. I would start by making sure it fully compiles there before even beginning to try to upgrade it. I would also make sure I had only the minimum number of references. A97 apps very often are old enough to have been converted from Access 2 and often have the compatibility library loaded. That needs to be removed before any conversion can succeed.

[David-W-Fenton]

Ridiculous. This is something that anybody with reasonable Access experience should be able to do. Indeed, if you're going to be managing the resulting upgraded database, you'd really be much, much better off doing the conversion yourself, since then you can decide what's important to convert and what can be discarded.

[joeriks]

Well, I'm still interested in a consultant. It was quite some time since I did my mdb work, and honestly I like other stuff I have on my dev list better. Also if I find someone professional - I expect him/her to do the job with close contact with me to be able to prioritize.