Stop auto-update of references

[Antilles]

We use a few Access 97 databases in work.

One of the main problems we have with these databases is that people have all sorts of different MS Office setups on their PCs, so if someone accidentally opens an Access 97 database who doesn't have Office 97, their PCs seem to automatically update references in the background - a good example is references changing from Excel 8 to Excel 11.

Every time this happens, the 97 databases then stop working on Office 97 setups, because the references have changed.

Is there any way to prevent this automatic updating from happening? We are spread across a number of offices so the chances of installing all the correct libraries on everyone's PCs would be a no-no. Is there some way to do it in the database?

 

[Rabbie]

You can always try using Late Binding. Search the forums for more info. As a starter this thread has links I have just posted

 

[Antilles]

I thought about using late binding. But several of the databases would require massive numbers of changes for this to work.

We are considering this as a last resort option.

I was hoping to find a simple option or hack that will prevent it, to save myself some work.

 

[DCrake]

A stab in the dark but if you distribute it as an MDE I don't think it will change the references, but the app will fall over because it can't find the object or library reference. So no matter which tack you take you are going to run into issues. It seams to me that each user does not have a copy of the front end on their pc. The front end holds the reference so if the machine has Excel 11 it will change it to 11. someone with Excel 9 on there machine will not be affected as their copy uses version 9. If this is the case you need to distribute the front end to each user. Reading between the lines I think that all users open the same mdb from a central location. This is why you are having conflicts. Correct me if I am wrong, but thats what sticks out.

David

 

[Rabbie]

You can work round this as David says by build ing your .mdb on a computer with the lowest version of Access/Office. From my experience it can dynamically set up a reference to a later version without problems. Of course the guaranteed solution is to go down the Late Binding route but that seems a lot of work from what you say.

 

[Antilles]

Thanks for your help guys. My team have just inherited this mess and are trying to make sense of it all!

You're correct that a lot of our front end dbs are on network shares.

The MDE sounds like a good way to go as I would rather people are booted out of the dbs completely, as the PCs that access them are supposed to be an Office 97 build only.

Either way it's going to take some work to fix this I think - so I guess we'll have to choose which route we want to go down.

Someone suggested that we could put the required object libraries on each PC as part of our standard build (e.g. install the Excel 8 libraries on the Office 2003 PCs). Any ideas if this would work - would it find the Excel 8 libraries and not do a reference update?

 

[Pat Hartman]

Start by distributing the front ends to the PCs that use them. The back end databases stay on the server where they are shared but the front ends should ALWAYS be stored locally so that each user has a private copy. This will solve your problem.

Rather than distributing old libraries to all the computers, it would be better to standardize Office versions.

 

[Antilles]

Hi Pat

Thanks for that.

What do you mean by standardising the office versions? Do you mean upgrading the databases to the latest version of Office?

We were considering doing this but a lot of the VBA code uses early binding calls to 97 versions of the Excel and Word libraries. So I guess we'd have to test all this code to ensure it still worked with the latest office version, then fix any problems.

 

[Pat Hartman]

Generally upgrading to the next version of a .dll will not break VBA code. The .dll libraries are not backward compatible though. If VBA code breaks when converting to a new version, it is usually because the VBA code was flawed to begin with and the earlier Access version just let you get away with sloppy practices. I have encountered a change when moving backends from SQL 2000 to SQL 2005 though. It seems that SQL 2005 requires the argument dbSeeChanges when opening recordsets that include references to tables with autonumbers. This is a minor but annoying change since it may involve changing dozens of SQL statements.