checking and refreshing access references

  • Thread starter Thread starter Andreas Wöckl
  • Start date Start date
A

Andreas Wöckl

HI Group!

I have the following problem:

On my machine I am developing with Microsoft Office XP and use references to
Excel and Word in my adp file. We have also a second developers that makes
some changes in the file - he has office 2003. Now every time he alters the
file i have to refresh the references because my Computer does not have
office 2003. I know there is the chance to work with late binding but I am
sure it's mor comfortable to work with Excel and Word constants -> so it's
easier to reuse the code the macro recorder has produces. I would need a
function that checks all office references and tries to refresh/readd with
new references.

Anyone an idea?

best regards

Andreas Wöckl
 
Andreas,

Do you mean something like this:

Private Sub Form_Load()
On Error GoTo Err_Form_Load

Dim xl As Object
Dim ws As Excel.Worksheet

Set xl = GetObject(,"Excel.Application")
Set ws = xl.ActiveWorksheet

' More code goes here

Exit_Form_Load:
' Clean up
If xl Is Nothing Then
' Do nothing
Else
Set xl = Nothing
End If
If ws Is Nothing Then
' Do Nothing
Else
Set ws = Nothing
End If
' Exit the sub
Exit Sub

' Error Handler
Err_Form_Load:
' Check which error occured
Select Case Err
Case 91 ' Enter correct error number here!
' Check to see if references to the variables are ok
If xl Is Nothing Then Set xl = GetObject(,"Excel.Application")
If ws Is Nothing Then Set ws = xl.ActiveWorksheet
' Resume on the same line now that variables have been set
Resume 0
Case Else
' Display unknown error
MsgBox Err.Description, vbOKOnly+vbCritical, Err
' Exit the sub
Resume Exit_Form_Load
End Select
End Sub

The code above has not been tested, but the important thing to note is the
error handling section. If the Object Not Set error (i think it is 91)
occurs then the variables are reset and the code is executed from the line
that caused the error.

HTH,

Neil.
 
Hi Neil!

No it's not the problem of my code - it's only the problem with the
references.

So every time I get the adp from our second devoloper I have to go to the
references and correct them because my computer does not have office 2003
and so the .adp can not get a reference to the Microsoft Excel 11.0 Object
Library. I would need a code that checks if references are broken and tries
to rebuild them - a code only for office references would be enough for me!

thanks and cu

andy
 
Andy,

This is more complicated then.

Allen Brown Wrote in a message about the same problem 6/07/2004:

Provided the references are not broken, you can loop through the References
collection to enumerate them.

If they are broken, any code will fail unless you are extremely careful to
explicitly call references from the unbroken libraries only, e.g.:
SomeVariable = VBA.Left([MyString],4)

For more info on that, see michka's article:
How to guarantee that references will work in your applications
at:
http://www.trigeminal.com/usenet/usenet026.asp?1033


Have a look through there and see if you can find anything.

HTH,

Neil.
 
Back
Top