Corrupt Access 2 Database? (Version 2 vs 2003)

Topic

New Reply

I’ve recently been asked to help fix a problem with an old Access Version 2 database. It had been used routinely and reliably for years as a turnkey “buttoned-up” application via the Access runtime application (MSARN200.EXE) — completely form driven, userid/password protected, etc. It continued to work fine for years with no problems so I guess no one bothered to convert it to more recent versions of Access.

Well, a few weeks ago a new set of Window XP security patches were “pushed” on our network (this may be entirely coincidental) — thereafter the database has not worked properly. It can be started up OK (login form) and navigation forms load fine, but when any button is clicked that triggers access to a table (or query), an error is displayed; for example “Can’t open database ‘Q_EMPLOYEE1’. It may not be a [database name] database, or the file may be corrupt.” and control returns to the calling form.

I’ve tried the /repair switch on the command line, but this doesn’t help (do all the .mdb files get repaired; both .mdb and dat.mdb?)

Also, I’ve considered trying to convert this database (or import it) into Access 2003, but I haven’t figured out a way to get through the Access 2 security (permissions required) when I try to import it while in Access 2003.

Needless to say, I don’t have a full blown Access 2 program around (I didn’t even start using Access until Access 95 and that was many moons ago).

Does anyone have any ideas on how I can salvage this database? If the file’s indeed corrupt, I can probably get our IT folks to retrieve a backup made before the problem was first appeared. If we were “living on borrowed time” anyway, using an Access 2 database with Windows XP, then I need to figure out a way to get through the security to convert it to Access 2003. Otherwise I may be forced to “redevelop” it from scratch in Access 2003 using the system spec documents (there are some but I don’t know how complete they are and whether they reflect the version we’ve been using). The original developers are long gone, I suspect.

Thanks for your help.

Reply | Quote

Replies

You’re definitely living on borrowed time. I believe you would need to convert the Access 2 database to an Access 97 format before you could move it into 2003, so if you have a copy of 97 around, that might save you. The big problem will be figuring out what all those cryptic DoMenuItem statements in the Access 2 code and macros equate to, since the menus have changed significantly over the years. You might be able to keep the interface running for a while if you migrate it, but sooner or later you’re going to have to rewrite a bunch of the code.

The repair will only apply to the mdb referenced in the command line. You would need to do a repair on both front and back end. I seem to recall that the tried and true method for 2.0 was compact/repair/compact, but it’s been a very long time since I used it. You might get IT to restore a backup (after copying the current version to be on the safe side) and see if you see the same problems. I don’t think we’ve even tried to run an Access 2 app on our XP machines, although we did on Windows 2000 occasionally, so I can’t tell you whether the recent spate of patches could be the problem.

Reply | Quote

Charlotte,

Thanks for your quick response. After I posted the message, I tried a few other things (e.g., renaming the .ldb files — apparently they don’t go away when the application closes — thought they might be corrupt and things would be cleaned up when they got re-created; no such luck). I then changed the command line to open the backend db (data.mdb) — I got a message on startup that the file was corrupt… and do I want to repair. I said yes, it repaired successfully and things started working! This jibes with your advice that the /repair switch only affects the database referenced in the command line.

Although my customer should be pleased as punch that I (we) got their database working again, I will suggest that it might be wise to get the thing converted up to the latest and greatest… one way or another. They’ve probably reached their credit limit in the borrowed time department. If they agree, I’ll have to figure out a way to get through the Access 2 security to convert it into Access 2003. When you say Access 97 will convert Access 2, will it recognize the Access 2 security? I’ve already noticed (when I unsuccessfully tried to import tables, queries, forms, reports, macros, etc. into Access 2003) that the application has a slug of macros — like you say, there may be some fun converting…

Thanks again.

Reply | Quote

FYI, I seem to recall that the security for Access 2 wasn’t quite as robust as it became when they introduced the MDW version of the security file. I don’t recall the details, but I suspect they still are around somewhere if you need to crack the security. I believe the security file was called System.MDA by default – and I don’t think there have been any changes in the command line structure.

Reply | Quote

Wendell.

Thanks for your help. I do have the mda file, so once I scare up a copy of Access 97 I may be able to make some headway with the conversion.

Reply | Quote

Access 97 will convert both the database and the workgroup file (which is a database BTW), whichever you aim it at, to the 97 format. You’ll need to deal with bringing the code and macros up to date in 97 before you try to move it to a later version or be plagued with a long series of compile errors that will drive you crazy trying to fix. The 2.0 version of the security file had an mda extension. It wasn’t until the switch from AccessBasic to VBA that it acquired the mdw extension. In 97 you can save a macro as code. It still needs a lot of hand work to clean up that horrible wizard-built code, but it gives you a leg up at least. Each macro will convert to a single module, so you’ll need to do some grouping there as well. You’ll also need to go through and explicitly declare object variables as DAO. to avoid problems when you move into the 2000+ arena where ADO also plays and has some objects with the same names but entirely different properties and methods.

Reply | Quote

Charlotte,

Thanks for taking the time to prepare a conversion “road map” for me. I think I can resurrect Access 97 from CD onto an old computer, long enough to do the conversion and reworks needed before moving it up to 2000+.

That macro conversion stuff sounds like fun (grin) — way back, I did some fairly sophisticated project scheduling type stuff using Quattro Pro macro programming (gasp). I’ll be curious to see if this is worse than that. If nothing else, it’s a little dose of continuing adult education (not that I’ll ever need it again…)

PS: I astonished a number of people at work by bringing their database back to life. They agreed that it would be prudent to bring the thing out of the dark ages…

Again, thanks.

Reply | Quote

I’m glad they’ve seen the light now, before it’s entirely too late. You’ll find that Access macros are entirely different from QuattroPro macros, but they will at least be a bit more comprehensible when converted to code. Unfortunately, the macros will be converted to code that is not really the right way to do most things since they are converted to their direct code equivalents, with no updating. Then it will be up to you to figure out what on earth they are doing and whether there is a better or simpler way to do it using VBA. Good Luck. I once converted a user-built database that was entirely macro driven and had dozens of macros that had to be executed in a particular order to so something that didn’t really need to be done at all, let alone that way. I had to convert them to code and wade through them figuring out what each one did and whether it was even useful. It takes a while, but there really is no other way except redesigning the application from scratch and hoping you didn’t miss anything.

Reply | Quote