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

user20416
– user20416

2016-12-29 08:35:33 +00:00
Commented Dec 29, 2016 at 8:35 — user20416
– user20416, Commented Dec 29, 2016 at 8:35

learnAsWeGo · Accepted Answer · 2018-08-14 19:46:33Z

63

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

edited Aug 14, 2018 at 19:46

learnAsWeGo

2,2823 gold badges15 silver badges21 bronze badges

answered Mar 18, 2011 at 8:43

Parth Bhatt

19.5k28 gold badges137 silver badges216 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jean-François Corbett · Accepted Answer · 2017-04-11 09:33:41Z

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, 2017 at 9:33

answered Mar 19, 2011 at 11:45

Jean-François Corbett

38.7k30 gold badges144 silver badges192 bronze badges

2 Comments

Shrout1 Over a year ago

@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.

Jean-François Corbett Over a year ago

Tested Dim cn As Object: Set cn = CreateObject("ADODB.Connection") at this end, works fine. Are you really getting the same error as before?

ZygD · Accepted Answer · 2015-09-26 14:48:53Z

-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, 2015 at 14:48

ZygD

24.8k41 gold badges106 silver badges144 bronze badges

answered Sep 26, 2015 at 2:12

Aradhana Mohanty

11

1 Comment

ZygD Over a year ago

This does not offer any new solution. This is already said in the other answer. This method is called late binding.

Collectives™ on Stack Overflow

Compiler Error: User-defined types not defined

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related