i am trying to connect to a database and to populate my spreadsheet but i am getting an error. i evoke the database connection with a click command on a worksheet.

here is my connection:

Sub pubDerConn()
Dim objConn As ADODB.connection
Dim objRS As ADODB.Recordset
Dim stSQL As String
Dim sql$, i&

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
Set ws = ThisWorkbook.Worksheets("intro")
objConn.Open "Provider=SQLOLEDB;data source=SQLSERVER;initial catalog=Data;UserId=PublicUser;Password=pass"
sql = "SELECT field FROM database"
rec.Open sql, objConn
While Not objRS.EOF
i = i + 1
ws.[a1].Cells(i) = rec!field
rec.MoveNext
Wend
rec.Close: conn.Close

End Sub

i get this error: compile error: user-defined type not defined;
and this line is highlighted: Dim objConn As ADODB.connection

thanks

You are mixing early binding with late binding..
Either reference the Microsoft ActiveX Data Objects 2.x library
in which case you dont need to CreateObject.

Or for late binding

maybe like this:

Sub pubDerConn()
    Dim sql$, i&
    Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    Set ws = ThisWorkbook.Worksheets("intro")
    objConn.Open "Provider=sqloledb;Server=SQLSERVER;Database=Data; UserId=PublicUser;Password=pass"
    sql = "SELECT field FROM database1"
    objRS.Open sql, objConn
    While Not objRS.EOF
        i = i + 1
        ws.[a1].Cells(i) = objRS!field
        objRS.MoveNext
    Wend
    objRS.Close: objConn.Close
End Sub