Solved: Compile error

[lifeson]

I have copied this from another project where it works fine but in a new project I get a compile error

"User-defined type not defined"

The code is in a module

[VBA]
Sub GetTable(ws As Worksheet, filter As String)

Dim DBFullName As String
Dim Cnct As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Application.Cursor = xlWait
' Database information
DBFullName = "D:\FieldSalesApplication\Local Database\PremierPlusField.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset

With Recordset
' filter is passed from called function
src = filter
.Open Source:=src, ActiveConnection:=Connection
' Write the field names
'worksheet (ws) is called from function

ws.Select
Cells.Clear
For Col = 0 To Recordset.Fields.count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
Set Recordset = Nothing
End With

Connection.Close
Set Connection = Nothing
Application.Cursor = xlDefault

Application.ScreenUpdating = False

End Sub
[/VBA]

What have I done wrong?

[Bob Phillips]

You don't have a reference to the ADO type library. Either set it in Tools>References, or use late binding

[vba]

Sub GetTable(ws As Worksheet, filter As String)

Dim DBFullName As String
Dim Cnct As String
Dim Connection As Object
Dim Recordset As Object
Dim Col As Integer
Application.Cursor = xlWait
' Database information
DBFullName = "D:\FieldSalesApplication\Local Database\PremierPlusField.mdb"

' Open the connection
Set Connection = CreateObject("ADODB.Connection")
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = CreateObject("ADODB.Recordset")

With Recordset
' filter is passed from called function
src = filter
.Open Source:=src, ActiveConnection:=Connection
' Write the field names
'worksheet (ws) is called from function

ws.Select
Cells.Clear
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
Set Recordset = Nothing
End With

Connection.Close
Set Recordset = Nothing
Set Connection = Nothing
Application.Cursor = xlDefault

Application.ScreenUpdating = False

End Sub
[/vba]

[lifeson]

I've said it before and I'll say it again this is by far the most helpful forum on any subject I have encountered.

Thanks XLD, hope you know how much I appreciate your your help and advice