Query database through Excel VBA

Question

I am a beginner in Excel VBA. I want to query data from Teradata database and give the output into the rows of an excel sheet. When i write the below code:

Private Sub CommandButton1_Click()
    Dim conn As Connection
    Dim rec1 As Recordset
    Dim thisSql As String
    Set conn = New Connection
    conn.Open "Driver=Teradata; DBCName=" & DBCName & ";UID=" & UID & ";PWD=" & PWD
    thisSql = "simple select qyery here"
    With .QueryTables.Add(Connection:=conn, Destination:=.Range("A1"))
        .Sql = thisSql
        .Name = "data"
        .FieldNames = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

I am getting the error saying 'Compiler error: User-defined type not defined'

how to overcome this error? Do i need to include anything in the code?

Please help

I am using MSVisualBasic 6.5 editor

showing the error at the line: Dim conn As Connection

SrinR
– SrinR

2012-05-31 07:45:44 +00:00
Commented May 31, 2012 at 7:45 — SrinR
– SrinR, Commented May 31, 2012 at 7:45

Jim Sjoo · Accepted Answer · 2012-05-31 07:48:11Z

4

Hi I guess it would need a recordset as the connection object when using QueryTables.Add. I modified your code and tried it as following:

Dim conn As adodb.Connection
Dim rec1 As adodb.Recordset
Dim thisSql As String

Set conn = New adodb.Connection

conn.Open your_connection_string

thisSql = "your query here"

Set rec1 = New adodb.Recordset
rec1.Open thisSql, conn

With Sheet3.QueryTables.Add(Connection:=rec1, Destination:=Sheet3.Range("A1"))
    .Name = "data"
    .FieldNames = True
    .Refresh BackgroundQuery:=False
End With

answered May 31, 2012 at 7:48

Jim Sjoo

3362 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SrinR Over a year ago

Hi Jim... Thanks for the reply... I have changed the code...but im still facing the same error.. My code is not identifying 'Connection' as a keyword... I feel

Jim Sjoo Over a year ago

Strange~ Did you check the reference to Microsoft ActiveX data Objects library on your project? I tested the above the code and succeeded.

SrinR Over a year ago

Thank you Jim.. tat suggestion helped. I've checkd the ActiveX lib in the reference for the project. I have a question here: Actually i first tried to check activeX, i was unable to see it.Then I opened other VBAProject which I know & is working fine(with database) and it was having the ActiveX checked..then I was able to check the ActiveX ref for my project also.

SrinR Over a year ago

This is in continuation with the above comment: So,if I uncheck ActiveX lib for my prjct and i want to check it again.then Iam unable to see the ActiveX lib in the references ..in this case I need to open other project and then Check ActiveX reference in my project and i am saving it. any help on how to include references if there are not available in the lit box of the references?

Jim Sjoo Over a year ago

checking the library beforehand is called 'early binding'. when you open the reference dialog box, it shows the checked libs on the top. I guess you might have had difficulty finding the proper lib. beside the early binding, there is late-binding. you should use CreateObject function for late-binding. for more info, plz google it and you can find tons of pages on the web.

|

Collectives™ on Stack Overflow

Query database through Excel VBA

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related