How to handle multiple versions of MS-Access better

[anhn]

Hi All,

Some of my users have multiple versions of MS-Access installed on their PCs, such as 97, 2000 and 2003.
These versions of Access may be installed in any folders other than the default folders.

On distribution of a shared MDB file (of any version) that located on a known path, I want the users will open it with matching version of MS-Access.

If all versions of Access were installed in the same folders for all users such as:
Access 97 in C:\Program Files\Microsoft Office\Office
Access 2000 in C:\Program Files\Microsoft Office\Office 10
Access 2003 in C:\Program Files\Microsoft Office\Office 11
then it is easy as I can distribute a fixed shortcut with Target such as

"C:\Program Files\Microsoft Office\Office 11\MSACCESS.EXE" "X:\ABC\MyDatabase.MDB"

But that is not the case.

I came up with a silly solution, that is:
Create an Excel file with an auto-executed sub, that use VBA function
acApp = CreateObject("Access.Application." & ver)
where ver = 8, 10 or 11 (resp. for 97, 2000 or 2003),
then use acApp object to open the MDB file, the Excel file then close itself.
In case if CreateObject() fails then the user does not have the required version of Access, just make a prompt before closing the Excel file.

Do you have a better solution?

In case of an Access-97 MDB file, I also want to stop a user (who does not have Access-97) to open it with Access-2003 then choose the default option to convert it to Access-2000/2003 MDB file.
Even I can make the job of conversion fails by using an MDE file instead of an MDB file then change the extension .MDE to .MDB.

[Davadvice]

Hello,

I would firstly ensure that the applications where being accessed via a Shortcut. I know that you have an issue as the Access applications are distributed to different folders.

At present you are looking for a work around to a poorly organised application deployment (Access Ver) . I would look at fixing this in the first instance. In my experience once you start working around a problem like this you spend a great deal of time managing the problem.

If the above is not an option I would build an applications in Access to locate the Access application Directories on each users system and store the paths in a table and use this to launch the applications they require. As you also wish to control the conversion I would just compile the applications in to MDE files thus removing the ability to convert.

Thanks

Dav

[RobDog888]

Actually if your users are not making structural or object design changes then it leaves a wider door open for you. Its more about what fileformat the database is in vs what version of Access your opening it with.

The most commonly used fileformat is 2000 and 2002-2003 in second. Any version of Access can open any version of a database but may be limited to read only on structural or design changes but can still add records etc.

What is your fileformat of your database in? it will say at the top of the captionbar when its open.

[salvelinus]

Any version of Access can open any version of a database but may be limited to read only on structural or design changes but can still add records etc.

Don't you mean can open any earlier version? You can't open 2002 from 97, for example. You get an unrecognized file format error.

[RobDog888]

97 may be the exception and also 2007 database files ca not be opened by any other version. Guess I should have elaborated more

97 is 10+ years old so It really may not be a good example but 2000 can open up to 2003 file formats and anywhere inbetween. Same for the reverse. 2007 can open other fileformat versions and places them in compatibility mode and can be converted to 2007. 2007 can convert a 2007 accdb fileformat back to a compatible 2000, 2002, 2003 fileformats.

[anhn]

Hi All,

Thanks for your comments.

I am a quite experienced developer of Access databases as I have used Access since version 1.0,
except I haven't played much with new features of version 2007.

There is not much I can do in my company that relates to different versions of Access.
It's a big firm with >3000 people in my country only,
out of that there are about 400 people who use my databases,
many of these databases were developed up to 6 years ago with Access-97.
Some newer databases were developed with Access 2000/2003 for other teams who never used Access-97.

The problem comes when a few dozens of people who need to run both old and new databases and they have not much knowledge about the system.
They just double click an MDB file or a shortcut to it and usually as you already knew Windows may open it with any version of Access that they have and that depends on the previous use of Access.

In some occasions, this scenario happened:
- A user opens a shared 97-mdb by double click a shortcut,
- (Windows opens the file with Acces-2003)
- the user chooses the option to convert it to 2003-mdb
- save new converted mdb file to wherever they want such as C:\,
- update and add some records then
- close the database.
Next day, that user double click the same shortcut again,
- the same thing will happen and
- he cannot find the records he added yesterday then
- an email will be sent to say that "the database has a problem because I lost all of my records that I entered yesterday." and
- you don't know what really happened.

I know that the best solution is to use only one version of Access such as 2003 and my company is intending to make Access-2003 as standard version.
However, with existing Access-97 databases, that is a huge effort to convert all of them to Access-2003 and that must be done almost at the same time.

I put up this thread for opening a discussion and hope maybe someone has a better solution.

Cheers

[RobDog888]

Well for your scenerio there really isnt too many options other then those posted and there isnt really a "best" option due to the age of Access 97.

[anhn]

Thanks Rob.

[anhn]

Hi guys,

I have found myself a better way to use a link to an Excel add-in to deploy a shortcut to a shared MDB file of any version with matching Access version installed on any folder, and it also prevent novice users to double-click on the MDB file and open it with a wrong version of Access.

If anyone interest it, I will post to explain in more details.

[Ecniv]

I wrote some code a while back that searches through the registry for the Access location (checks for a specific key then uses the value held there to find another key that holds the location).

How to specify which mdb is which version ... ah dunno. Unless there is some embedded text/code in the file which you could check first...