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?
"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?