User-defined type not defined

[JimFl78]

Hi,

The code below works fine in my version of Excel but if I try to run on multiple other versions on different computers I get this error

Compile error:
User-defined type not defined

[vba]
Dim cn, strSQL, rst, i
Set cn = New ADODB.Connection
cn.Open myConnStr
i = 2
strSQL = "select * from table"
Set rst = cn.Execute(strSQL)
Do While Not rst.EOF
With ActiveSheet
ActiveSheet.Cells(i, 1).Value = rst(1)
ActiveSheet.Cells(i, 2).Value = rst(2)
ActiveSheet.Cells(i, 3).Value = rst(3)
End With
i = i + 1
rst.MoveNext
Loop

cn.Close
Set cn = Nothing
[/vba]

The problems occurs on this line

[vba]New ADODB.Connection
[/vba]

I have looked at the References for the VBAProject on the computer where it is not working and there are no references that say they are missing.

I have looked for the ADO type library but cant seem to locate it on the list - Does anybody know the exact name used?

I realise this problem exists more than once on this forum but cant seem to get my example to work. Can anybody help with this please.

Many thanks in advance

[Bob Phillips]

Look for Microsoft ActiveX Data Objects 2.n Library

[JimFl78]

Yep that seems to have done the trick. I have used the Microsoft ActiveX Data Objects 2.8 Library assuming that this will work on all machines. I have tested it on several machines and it seems to work fine.

Many thanks

[mdmackillop]

Have a look at this KB Item