PDA

View Full Version : User-defined type not defined



JimFl78
03-17-2008, 03:11 AM
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


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


The problems occurs on this line

New ADODB.Connection


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
03-17-2008, 03:16 AM
Look for Microsoft ActiveX Data Objects 2.n Library

JimFl78
03-17-2008, 03:31 AM
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
03-17-2008, 06:00 PM
Have a look at this KB Item (http://vbaexpress.com/kb/getarticle.php?kb_id=267)