Compiler Error: User-defined types not defined

Question

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

Possible duplicate of ['User Defined Type Not Defined' error](http://stackoverflow.com/questions/24261557/user-defined-type-not-defined-error) — Zev Spitz, Dec 29 '16 at 08:35

score 60 · Accepted Answer · edited Aug 14 '18 at 19:46

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

Jean-François Corbett · Answer 2 · 2017-04-11T09:33:41.883

18

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.

edited Apr 11 '17 at 09:33

answered Mar 19 '11 at 11:45

Jean-François Corbett

37,420
30
139
188

@Jean-FrançoisCorbett Seems like the only way I can get this working is by adding the reference... Possible that I don't understand your solution. – Shrout1 Sep 06 '13 at 18:26
Tested `Dim cn As Object: Set cn = CreateObject("ADODB.Connection")` at this end, works fine. Are you really getting the same error as before? – Jean-François Corbett Apr 19 '15 at 18:28

score -3 · Answer 3 · edited Sep 26 '15 at 14:48

-3

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")

edited Sep 26 '15 at 14:48

ZygD

22,092
39
79
102

answered Sep 26 '15 at 02:12

Aradhana Mohanty

11

2

This does not offer any new solution. This is already said in the other answer. This method is called late binding. – ZygD Sep 26 '15 at 14:52

Compiler Error: User-defined types not defined

3 Answers3

Linked