Question About Library References

[FatMan]

Hello All,

This is my first post here, but I figured I'd give it a shot since I am
really stuck on a problem. Thanks in advance for any help that can be
provided.

The problem has to do with library references. I have a database which
is shared between one Access 2000 user and one Access XP user. Some of
the code in the database requires the Excel Object Model library to be
loaded. This can be easily done using the Tools...References menu in
the VBA editor. So far so good.

However, each time a user opens the database, it tries to load the
library associated with their version of Office. So, for example, if
the database is opened in Access 2000, it looks for the Excel 9.0
Object Model. If the database is opened in Access XP, it looks for the
Excel 10.0 Object Model. This is leading to errors, specifically when
the 2000 user attempts to open the database after the XP user and gets
a missing library error (since they don't have the Excel 10.0 Object
Model library).

I am trying to get around this whole mess by writing some simple code
to search for missing libraries, delete the references, and then
attempt to find the correct Excel library and load it. This code would
be the first thing that would run upon database open.

So far I've been able to loop through all of the references in the
database and check the IsBroken property to tell me what's not working.
I am also able to add a reference using the AddFromGUID method of the
References collection. However, I cannot seem to remove a broken
reference, no matter what I do. Here is a bit of sample code to give an
idea what I'm attempting...

Dim ref As Reference
For Each ref In References
If ref.IsBroken = True Then Application.References.Remove ref
Next

When I attempt to run this in the database containing a missing
reference, I get a "Object Library Not Registered" run-time error. Yet
I have no problem removing the reference manually.

Sorry for the long and rambling email, but I wanted to include as many
details as possible. I feel like I'm one step away from conquering
this whole problem, so if anyone could shed some light on how to
programmatically remove a missing reference, it would be greatly
appreciated.

 

[Steve Schapel]

FatMan,

It sounds like you have the two users accessing the same database on a
network. This is not a good idea. Access databases should always be
run on the local machine, with each user having their own copy, and
linking only to the core tables on the network. This would solve your
problem, I think. Even then, I would standardise, and put the Excel 9
library on the Access 2002 computer and set the reference to it rather
than to the Excel 10 library.

- Steve Schapel, Microsoft Access MVP

 

[Douglas J. Steele]

Not sure you can copy the Excel 9 library like that, Steve (and even if you
can, I'm not sure it's legal)

If they can't standardize to a single version, the easiest solution is
probably late binding. Tony Toews has some information about this at
http://www.granite.ab.ca/access/latebinding.htm

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

FatMan,

It sounds like you have the two users accessing the same database on a
network. This is not a good idea. Access databases should always be
run on the local machine, with each user having their own copy, and
linking only to the core tables on the network. This would solve your
problem, I think. Even then, I would standardise, and put the Excel 9
library on the Access 2002 computer and set the reference to it rather
than to the Excel 10 library.

- Steve Schapel, Microsoft Access MVP

 

[Steve Schapel]

Thanks for the heads-up, Doug, on the legality question. You are no
doubt right about this.

- Steve Schapel, Microsoft Access MVP

 

[Howhiareu]

I would try this...
I agree with Steve, that only the tables be located on the network.
would maintain the client application in Access2000 and distribute t
each machine's local drive, when opened - Access2002 should upgrad
references for itself on the fly. The main point, you shouldn't nee
to maintain 2 versions to remedy this situation.

Then you only have to worry about how to manage your linked tables