PDA

View Full Version : Solved: Compile error



lifeson
03-04-2008, 12:46 PM
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


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


What have I done wrong?

Bob Phillips
03-04-2008, 12:55 PM
You don't have a reference to the ADO type library. Either set it in Tools>References, or use late binding



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

lifeson
03-05-2008, 12:05 PM
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

:clap: :clap: :bow: :bow: