Lib Problem

[Guest]

I am using access 2002 for the network database. Lately, after someone
installed a higher version of MS Excel, I started having a problem with the
MS Excel 5.0 object lib. every time the higher version of excel is used,
access checks ms excel 10.0 object lib and then all users get the standred
"missing Library" message. then I have to go the the references and uncheck
the Excel 10 and check Excel 5.0 object lib. Is there a way to do this
automatically and get rid of this problem once and for all.
thanks
Al

 

[Dirk Goldgar]

I am using access 2002 for the network database. Lately, after someone
installed a higher version of MS Excel, I started having a problem
with the MS Excel 5.0 object lib. every time the higher version of
excel is used, access checks ms excel 10.0 object lib and then all
users get the standred "missing Library" message. then I have to go
the the references and uncheck the Excel 10 and check Excel 5.0
object lib. Is there a way to do this automatically and get rid of
this problem once and for all.
thanks

There are two solutions to this problem.

1. It sounds like your users are all opening the same database on the
network. They should not be. If you split your database into back-end
(tables only) and front-end (everything else, with tables linked to the
tables in the back-end), then you give each user their own copy of the
front-end. That way, once the references are correct in each user's
copy of the front-end, that copy's references won't get messed up.
Plus, you can work on changes to your development copy of the front-end
without affecting anyone else, and then roll out the new version by
copying it to each user's PC, re-linking the tables if necessary, and
updating the references if that's necessary.

2. If you program your Excel automation using late binding, you don't
need a reference to Excel at all.

 

[Guest]

1. That is the case, I have already a front end and back end
2. Could you explain more about the late binding. can you give me an
example? I have couple of forms that use excel frequently but nothing else in
the database needs it.
thanks
Al

 

[Dirk Goldgar]

1. That is the case, I have already a front end and back end

But is each user opening their own copy of the front-end? If so, once a
user's copy has its references set right, they shouild stay right until
a new copy (with incorrect references) is installed.

2. Could you explain more about the late binding. can you give me an
example? I have couple of forms that use excel frequently but nothing
else in the database needs it.

Late binding avoids any object- and type-library references by declaring
all objects simply as Object and defining any required constants
in-line. Also, it uses CreateObject or GetObject to acquire references
to objects in place of Dim objX As New ...". For example, instead of

' Example of early-binding code

Dim xlApp As Excel.Application
Dim wbWorkbook As Excel.Workbook

Set xlApp = New Excel.Application
Set wbWorkbook = xlApp.Workbooks.Open("C:\Temp\temp.xls")

' ...

wbWorkbook.Close
xlApp.Quit

You would use this:

' Example of late-binding code

Dim xlApp As Object
Dim wbWorkbook As Object

Set xlApp = CreateObject("Excel.Application")
Set wbWorkbook = xlApp.Workbooks.Open("C:\Temp\temp.xls")

' ...

wbWorkbook.Close
xlApp.Quit

Also, if your code used any of the "xl..." constants defined in the
Excel library, you would define them in your code instead, or else just
supply their literal values wherever they are called for.

If you use late binding, you eliminate the need for the library
reference, so you remove it. A reference that isn't present can't be
broken. In exchange for this advantage, you lose a bit in execution
speed, which is generally insignificant, and you lose the "intellisense"
in the VB editor. That's a bit burdensome, so what most people do is
write the original code using early binding, with the library reference
in place, and then modify the code to use late binding once it's
working, and remove the library reference. Recompiling after removing
the reference helps you catch those places where you forgot to modify
your code.