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 

Answer 1

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

Answer 2

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.