Compiler Error: User-defined types not defined

31
votes

I get the compile-time error "User-defined types not defined" on this line:

Dim cn As ADODB.Connection

What could be wrong?

Code:

Sub test()

    Dim cn As ADODB.Connection

    'Not the best way to get the name, just convenient for notes

    strFile = Workbooks(1).FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    Set cn = CreateObject("ADODB.Connection")
    'For this to work, you must create a DSN and use the name in place of

    'DSNName

    'strSQL = "INSERT INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " & "Select AnyField As NameOfMySQLField FROM [Sheet1$];"
     strSQL = "SELECT F1 FROM [Sheet1$];"
     cn.Execute strSQL
End Sub
3
vbaexcel
Possible duplicate of 'User Defined Type Not Defined' error - Zev Spitz

3 Answers

55
votes

I had forgotten to add a reference to "Microsoft ActiveX Data Objects 2.5 Library": This reference is required for early binding.

How to get to that reference:

Tools > References > Check the checkbox in front of "Microsoft ActiveX Data Objects 2.5 Library"

Other libraries that work include:

Microsoft ActiveX Data Objects 2.6 Library

Microsoft ActiveX Data Objects 2.7 Library

Microsoft ActiveX Data Objects 2.8 Library

Microsoft ActiveX Data Objects 6.1 Library

17
votes

You can use late binding:

Dim cn As Object

will make the problem go away. VBA will make the reference automatically when the Set cn = CreateObject("ADODB.Connection") statement is executed.

-3
votes

I tried adding Microsoft ActiveX Data Objects 2.5 and 2.8 library, but it did not work out. But when I tried creating new object like below it worked.

Set cn = CreateObject("ADODB.Connection")