References changing between different platforms - DLL hell returns!

[Rob Nicholson]

The problem:

o Excel VBA workbook developed on Windows 2000 using/referencing Microsoft
ADO Ext. 2.7 for DLL and Security library

o Load and save workbook on Windows XP system

o Re-open the workbook on Windows 2000 system and VBA won't run as it's
missing the reference to Microsoft ADO Ext. 2.8 for DLL and Security library

What's happening is that when it's opened on the Windows XP system, Excel is
automatically upgrading the reference to v2.8 of the library. This is then
saved in the workbook meaning it can't be opened/used with an earlier
version of the library.

Actually, Windows 2000/XP isn't the issue - it would happen on two Windows
2000 systems if they had different versions of the library.

The same doesn't happen with plain ADO - we reference v2.5 and this stays as
v2.5 even when used on Windpws XP running a later version of ADO.
Interesting, the file name for ADOX v2.7 and v2.8 is the same: c:\program
files\common files\shared\ado\msadox.dll

Is there a fix??

Thanks, Rob.

 

[Rob Nicholson]

Is there a fix??

Later...

Okay, the information about the original reference under ADOX 2.7 is as
follows:

Reference {00000600-0000-0010-8000-00AA006D2EA4}
Major 2
Minor 7
Broken False
Full path C:\Program Files\Common Files\System\ADO\msadox.dll
Name ADOX
Description Microsoft ADO Ext. 2.7 for DDL and Security

But when the workbook has been opened under ADOX 2.8, the reference is
changed to exactly the same as above except the minor version is 8 as
opposed to 7, i.e. 2.8 versus 2.7. The GUID is the same for both versions.

So I thought - simple, at program startup walk through the references
looking for the broken reference, remove the reference and add back in the
original. But the following code doesn't work - well it works all the way
until we try and remove the broken reference when Excel throws an error:

-----
Run-time error '-2147319779 (8002801d'

Object library not registered.
----

Of course it's not registered!! That's why I'm trying to remove it. So the
question now is "HOW DO YOU REMOVE A MISSING REFERENCE IN VBA".

Cheers, Rob.

Private Const ADOX_2_7_GUID = "{00000600-0000-0010-8000-00AA006D2EA4}"

Sub RebindADOX()
Dim TargetWorkbook As Workbook
Set TargetWorkbook = Workbooks("Lung Cancer Trial Tracker.xls")
Dim TargetProject As VBIDE.VBProject
Set TargetProject = TargetWorkbook.VBProject
Dim VBReference As VBIDE.Reference
For Each VBReference In TargetProject.References
If VBReference.IsBroken And VBReference.GUID = ADOX_2_7_GUID Then
TargetProject.References.Remove VBReference
*** FAILS HERE ****
TargetProject.References.AddFromGuid ADOX_2_7_GUID, 2, 7
End If
Next
End Sub

 

[Rob Nicholson]

question now is "HOW DO YOU REMOVE A MISSING REFERENCE IN VBA".

I've read some more on Google and I think I'm onto a non-starter here - the
question has been asked before and the answer that I've found so far is "You
can't remove a missing reference from VBA, the functionality doesn't exist".

The answer is to use late binding which isn't half-way as nice but at least
it'll work.

Now what's the CreateObject syntax for ADOX catalog please?

Thanks, Rob.

 

[Peter Huang [MSFT]]

Hi

Here is some code snippet for your reference.
Dim cat As Object
Dim tbl As Object
Dim col As Object
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "E:\test\excel\test2.xls;Extended Properties=Excel 8.0"
Set tbl = CreateObject("ADOX.Table")
tbl.Name = "TestTable"
Set col = CreateObject("ADOX.Column")
With col
.Name = "Col1"
.Type = adDouble
End With
tbl.Columns.Append col
Set col = Nothing
Set col = CreateObject("ADOX.Column")
With col
.Name = "Col2"
.Type = adVarWChar
End With
tbl.Columns.Append col
cat.Tables.Append tbl

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.