Access 2000/2002/2003 Interchange Problems? (1 Viewer)

[jimbrooking]

I have been doing some consulting for a NJ (USA) firm on a fairly complex database that was originally developed by the firm in Access 2003. They outsourced a carefully spec'd upgrade to an off-shore developer, and about a month ago I got to start picking up the pieces from that debacle.

Our problem is this: I am developing on Access 2000. The consulting firm is using Access 2003. A potential customer is using Access 2002. There is something about the database that prevents Access 2003 from opening it after it's been opened with Access 2002, and vice versa. I (Access 2000) can sometimes open in after Access 2003 has opened it, but this hasn't been well-tested, as mostly my code goes to one of the other versions.

When any of us (the different Access versions) gets a copy that won't open, the solution (per MS) is to define a new database, and import all the "corrupt" DB's objects into it. 'Course that necessitates fooling with Startup options, VBA References, etc, and so is a royal pain. Also, sending the DB to a new customer doesn't inspire confidence when the thing fails to open and magic needs to be performed to make it work.

In summary, with this database, opening it with one version of Access seems to render it not openable in the others. I was wondering if anyone had seen this before, and better, had some ideas about what the cause and cure might be.

Many thanks,
Jim

 

[KenHigg]

Jim, For all the headaches, why this and why that, it would seem the best solution would be for everyone to be using the same version!

kh

 

[The_Doc_Man]

Jim, the most common cause of this sort of problem is references.

You seem to be savvy, but I'll belabor the obvious in order to make the point.

Access stores its references in the WINDOWS registry. Which means that each machine potentially can act differently - EVEN WITH THE SAME VERSION OF WINDOWS AND ACCESS - if the install options were different. 'cause that's how the registry gets loaded... at install time.

Further complications are the DAO/ADO nightmare. If one system is set up for DAO usage while the other is set up for ADO as the primary way of doing things, odds are your references need updating.

The thing that happens when you open an Access DB from a higher version of Access is that it (probably) has to recompile the code and convert any references that it can. That it ever succeeds in converting an arbitrary DB on its own is already a testament to many midnight hours by a bunch of underpaid, underappreciated, and probably undersexed programmer types. (I.e. still too young to have a sense of proportion... like me 30+ years ago).

But the factor that counts here is that the newer version of Access uses the newer versions of every reference. Now when you try to go backwards, ... Whoops!

I don't have a good solution for you. There are some awkward ones, though...

First, you might split the DB into a front-end/back-end situation. The BE is pure data and many versions of Access can use the same BE. Then you would only need one version of the FE for each version of Access.

Second, you could determine the references you need for the lowest common denominator, then do some registry searches to find the keys you need, then use the GetSettings routine to examine your registry. Use that to print out a list of registry settings for your references on the working Ac2K case. It would be a starting point if you have to reset references for the other machines.

 

[jimbrooking]

Ken,

Can't really tell a major organization (which may have other Access apps) to upgrade their Access clients to run our app. We are talking large hospitals and other players in the healthcare industry. That would be a heavy price to pay for our software, above and beyond the licensing cost.

Jim

 

[jimbrooking]

The_Doc_Man-

Thanks for whese very useful comments. I will look at my references and maybe try to package up the appropriate DLLs with the distribution version in the installer and see if that helps.

We already have provisions for splitting the DB and auto-linking at startup.

Don't know if the DAO/ADO thing will bite - I'm already packaging DAO360.dll with the installer.

Thanks again - very helpful.

Jim

 

[KenHigg]

Hum... Then maybe you could maintain separate versions and distribute them accordingly...


kh