Error user defined type not defined

[myforum4excel]

Hi,
i m using below code to fetch table from oracle db and populate in excel.
but getting compilation error --> user defined type not defined

please provide me solutoin

Sub FetchRecordSetQuery()
      
    'Dim DBcon As ADODB.Connection
    
    Dim DBrs As ADODB.Recordset
    Set DBcon = New ADODB.Connection
    Set DBrs = New ADODB.Recordset
    Dim DBHost As String
    Dim DBPort As String
    Dim DBsid As String
    Dim DBuid As String
    Dim DBpwd As String
    Dim DBQuery As String
    Dim ConString As String
        Dim intColIndex As Integer
    On Error GoTo err
' DB connectivity details. Pass the correct connectivity details here
    DBHost = "13.38.21.18"
    DBPort = "1345"
    DBsid = "CSTHR"
    DBuid = "Readonly"
    DBpwd = "read_0123"
'Open the connection using Connection String
    DBcon.Open (ConString) 'Connecion to DB is made
    DBQuery = "Select ename,empno from emp"
    'like UPDATE, DELETE, INSERT etc.
'below statement will execute the query and stores the Records in DBrs
    DBrs.Open DBQuery, DBcon
    If Not DBrs.EOF Then 'to check if any record then
' Spread all the records with all the columns
' in your sheet from Cell A2 onward.
        Sheets("Getdata").Range("A2").CopyFromRecordset DBrs
'Above statement puts the data only but no column
'name. hence the below for loop will put all the
'column names in your excel sheet.
        For intColIndex = 0 To DBrs.Fields.Count - 1  ' recordset fields
            Sheets("Getdata").Cells(1, intColIndex + 1).Value = DBrs.Fields(intColIndex).Name
        Next
    End If
'Close the connection
    DBcon.Close
    Exit Sub
err:
    MsgBox "Following Error Occurred: " & vbNewLine & err.Description
    DBcon.Close
End Sub

 

[pgc01]

Hi
Welcome to the board

Next time please post which line is highlighted when the error occurs

In this case set the reference to the most recent Microsoft ActiveX Data Objects.

BTW, you missed 1 declaration:

...
Dim DBcon As ADODB.Connection
...

 

[myforum4excel]

Hi,
i was getting error at this line only so i commented in my code still getting same error

Dim DBcon As ADODB.Connection

 

[pgc01]

Did you set the reference?

 

[myforum4excel]

Hi,
i m not sure how to set reference, can you fix above code error.

 

[RoryA]

No - you have to set the reference. In the VB Editor, click Tools - References, and locate the entry for Microsoft ActiveX Data Objects library (there may be more than one, it shouldn't really matter which you choose) and tick it.

 

[myforum4excel]

Hi,
it was already enabled, still getting error..

Microsoft Forms 2.0 object library
Microsoft Office 14.0 object library
Microsoft Excel 14.0 object library

 

[RoryA]

None of those are the reference I mentioned.