We help IT Professionals succeed at work.
Get Started

Setting references problem

wileedingo
wileedingo asked
on
2,726 Views
Last Modified: 2012-06-27
Hello,

I have a database which includes references to the Word and Excel object libraries. It has been developed in Access2000 format using Office xp (ver 10) and there are no problems running it as-is in Office 03 (ver 11). The problem has come up where someone is running the database in Office2000 (Access has been upgraded to 2002).

I have modified a routine from the MS web site which removes broken references. After that, it figures out the verson of office and resets the references. Code is further below...

When running it on the Office2000 machine I get the following error messages. I'm thinking the version numbers are all wrong...

"Your [database name] database or project contains a missing or broken reference to the file 'msword.olb' Version 8.2"

and

"Your [database name] database or project contains a missing or broken reference to the file 'excel.exe' Version 1.6"

Here's the code...

    Dim loRef As Access.Reference
    Dim intCount As Integer
    Dim intX As Integer
    Dim blnBroke As Boolean
   
    On Error Resume Next

    'Count the number of references in the database
    intCount = Access.References.count
 
    'Loop through each reference in the database
    'and remove broken references
    For intX = intCount To 1 Step -1
      Set loRef = Access.References(intX)
      With loRef
        blnBroke = .IsBroken
        If blnBroke = True Or Err <> 0 Then
          With Access.References
            .Remove loRef
          End With
        End If
       End With
    Next
   
  Set loRef = Nothing

 ' now set the references

If (Dir("c:\Program Files\Microsoft Office\Office11\msword.olb") <> "") Then
    With Access.References
        .AddFromFile "C:\Program Files\Microsoft Office\Office11\msword.olb"
    End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office10\msword.olb") <> "") Then
    With Access.References
        .AddFromFile "C:\Program Files\Microsoft Office\Office10\msword.olb"
    End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office\msword9.olb") <> "") Then
    With Access.References
        .AddFromFile "C:\Program Files\Microsoft Office\Office\msword9.olb"
    End With
End If

If (Dir("c:\Program Files\Microsoft Office\Office11\excel.exe") <> "") Then
    With Access.References
        .AddFromFile "C:\Program Files\Microsoft Office\Office11\excel.exe"
    End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office10\excel.exe") <> "") Then
    With Access.References
        .AddFromFile "C:\Program Files\Microsoft Office\Office10\excel.exe"
    End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office\excel9.olb") <> "") Then
    With Access.References
        .AddFromFile "C:\Program Files\Microsoft Office\Office\excel9.olb"
    End With
End If

Any ideas on how to fix or improve this?

wileedingo
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE