References code placement

[Wylie C]

I am working on an Access 2000 project. If the user tries to open the db,
there is a reference missing. I have the following code that I believe should
reset the reference but cannot figure out where to put it to reset the
reference when the project opens. I have put it in the OnOpen, OnLoad of the
startup form, put as the first function at the beginning of the Autoexec
macro. Any suggestions?

Public Function ProjAddRef()
On Error GoTo Handle
Dim ref As Variant 'variant for ref only type that works with ref
collection
For Each ref In Application.VBE.ActiveVBProject.References 'iterate through
collection

With ref 'this will fix any broken references in project
If Application.VBE.ActiveVBProject.References.Item("ADODB").IsBroken
= True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{00000201-0000-0010-8000-00AA006D2EA4}",
2, 1) = True 'adodb ref C:\Program Files\Common
Files\System\ADO\msado21.tlb full path
End If

' Debug.Print .Name & " " & "Name" '''these print to
debug window
' Debug.Print .Major & " " & "Major"
' Debug.Print .Minor & " " & "Minor"
' Debug.Print .BuiltIn & " " & "built in"
' Debug.Print .Guid & " " & "Guid"
' Debug.Print .FullPath & " " & "full path"
' Debug.Print .IsBroken & " " & "is broken"
' Debug.Print "***********************************************"
End With
Next
Exit Function
Handle:
Select Case Err.Number
Case 91
Stop
Resume
Case Else
MsgBox Err.Number & vbNewLine & Err.Description
End Select
End Function

 

[Douglas J. Steele]

See whether what MichKa has at
http://www.trigeminal.com/usenet/usenet026.asp helps.

This assumes, of course, that you're dealing with an MDB: it's not possible
with an MDE.

 

[Guest]

Assuming this is an MDB,

1) I would re-write this as a macro. No error handling and
meaningless message on failure, plus immediate problems
with 'macro security', but more likely to work.

2) I see a coding error in your sample code: you test
the ADODB ref, remove a different ref, then add a ref from
GUID. Look again: what is the ref loop for? Why are you
removing the ref pointed to by the ref loop? If ADODB is
broken, this code would try to remove the Access library ref,
and would fail.

3) You can define a ref as Access.Ref, or as Object

4) But if you want it to work, recode it as a macro.

(david)