Removing reference generates type mismatch error

[quartz]

I am using Office 2003 on Windows XP.

When distributing programming solutions I have encountered a situation in
which one user has Office 2003 and another user of the same file has a
previous version of Office. All users have at least Office 2000 or greater.

I have a function that creates a reference to ADO programmatically and it
works fine, unless an Office 2003 user is the last user of the file. ADO
references will go forward, but not backward. So I need to remove the ADO
reference on exit, then re-reference ADO on open each time.

I can work all this out, except my removal function gives me a "Type
mismatch" error.

1) My functions follow, can someone please correct my code and/or post code
of your own to deal with this?

2) Also, does it seem as though my approach will thoroughly fix my issue?

Public Function ADOReferenceAdd()
'PROGRAMMATICALLY ADD AN ADO REFERENCE
On Error Resume Next
ActiveWorkbook.VBProject.References.AddFromFile "C:\Program Files\Common
Files\System\ado\msado15.dll"
On Error GoTo 0
End Function

Public Function ADOReferenceRem()
'PROGRAMMATICALLY REMOVE AN ADO REFERENCE
On Error Resume Next
ActiveWorkbook.VBProject.References.Remove "C:\Program Files\Common
Files\System\ado\msado15.dll" '<type mismatch error here (fix line wrap)
On Error GoTo 0
End Function

Thanks much in advance for your assistance.

 

[quartz]

Actually, I just got it working. Thanks for reading.
If anyone would like to see my solution, post a request.

 

[Philip]

Hi,

One easy solution is to use late-binding...when distributing accross
different versions (where you may have different versions of ADO installed)
it pays to use late-binding.

Then you won't need to add the reference - especially as you have completed
the development and no longer need the intellisense!

so instead of:

dim adoRS as new adodb.recordset, adoCN as new adodb.connection

use:
dim adoRS as object, adoCN as object

set adoRS=vba.createobject("adodb.recordset")
set adoCN=vba.createobject("adodb.connection")

this guaratees that if ado is installed theen vba will always instantiate
the one in the registry.

HTH

Philip

 

[RB Smissaert]

It looks you found a solution and you have the option of late binding, but I
find this works:


Sub AddADO()

Dim r

For Each r In ThisWorkbook.VBProject.References
If r.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And r.Major = 2
Then
Exit Sub
End If
Next

On Error GoTo NOTFOUND

'although usually the ADO version will be higher, doing Minor:=0 will
install
'the higher version if available. On the other hand when you specify
Minor:=5
'and only a lower version is available, this can't be installed
'----------------------------------------------------------------------------
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _
Major:=2, Minor:=0
Exit Sub

NOTFOUND:
On Error GoTo 0

End Sub


Sub RemoveReference(strReference As String)

Dim r As Reference

For Each r In ThisWorkbook.VBProject.References
If r.Name = strReference Then
ThisWorkbook.VBProject.References.Remove r
Exit Sub
End If
Next

End Sub


Sub Test()

RemoveReference "ADODB"

End Sub


RBS

 

[quartz]

Thanks Philip, yes I always forget about late binding, but I think my current
work around will prevent me from having to change my code around at this
point...
Thanks for the tip...