Running Teradata Queries From MS-Excel 2007 Using VBA

Rating 3.80 out of 5
[?]

Here is a simple tutorial to connect to a Teradata database system using Teradata ODBC driver and manipulate data using VBA and ActiveX Database Objects(ADO). This can be used to automate some of your task like running queries multiple times.

First of all, we have to create three variables of the types:

  1. ADODB.Connection- To create an open connection to the data source
  2. ADODB.Recordset – This is the table/query level variable .After running the query data fetched from database will be stored in this variable.
  3. ADODB.Error -This contains data access error details created during the execution of query.

The VBA code given below has a function dbConnect() which connects to teradata database, execute the query and write the results to excel. You can try by copy pasting the code to your Visual Basic Editor(you can get VBE from Developer tab of excel).

Public cn As ADODB.Connection 'Connection...
Public Rec_set As ADODB.Recordset 'Recordset...
Public Err As ADODB.Error
Public Sub dbConnect()

Set cn = New ADODB.Connection
Set Rec_set = New ADODB.Recordset
cn.ConnectionTimeout = 0 'To wait till the query finishes without generating error
Dim ip As String, db As String, Uid As String, pass As String, Row As Integer
Row = 1
ip = "30.120.12.123"
db = "T23_EMPLOYEE_INFO"
Uid = "admin"
pass = "password"
'Connect to database
cn.Open "Driver={Teradata};" & _
"DBCName=" & ip & ";" & _
"Database=" & db & ";" & _
"Uid=" & Uid & ";" & _
"Pwd=" & pass & ""
'Check for errors...
If cn.State = adStateOpen Then 'If connection is success, continue
Rec_set.Open "select * from EMPLOYEE;", cn 'Issue SQL statement
While Not Rec_set.EOF
Cells(Row, 1) = Rec_set.Fields(1).Value 'Writing fetched data to excel
Rec_set.MoveNext
Row = Row + 1
Wend
Rec_set.Close 'Close the recordset
Else

'Display errors..
For Each Err In objConn.Errors
Debug.Print Err.Description
Next
End If

'Release connection to database
cn.Close

End Sub

Word of caution
Before running,take Visual Basic Editor. Go to Tools->Reference. Make sure that you check the boxes as shown below:

Otherwise you will get an error like:

That’s it! Hope this helps you. Have a nice day!

Bookmark the permalink.

12 Comments


  1. even after Microsoft activex in tools, i’m getting the same error. Can you help me on this.


    • Hi Sudhan,thanks for commenting..
      Please check if you have checked “Microsfot ActiveX Data Objects” with highest version number. Also if “Microsoft DAO 2.5/3.51 object library” is checked,uncheck it.Hope this helps.


  2. Hi, In the same way..

    Can you please provide me the code to connect to Netezza Database…

    Thanks in Advance..


  3. Hi Anu,
    Thanks for writing to us. I am unsure of how to connect to Netezza database, but I think the only difference should be the driver details. So please take a look into that from your end.


  4. Thank you for the Reply Anshuman.Could you please let me know how to execute Teradata stored procedure from excel using VBA..Appreciate for the Quick response and plz provide sample script any .if you can..

    Thank you in Advance!!!!


  5. I am not able to establish the connection. It says,
    ‘[Microsoft][ODBC Drive Manager]Data source name not found and no default driver specified’
    I am not connected to my Teradata databases through ODBC.Net. Is that causing the error?
    Please let me know how I can get this resolved.


  6. Superb! perfect example for the beginners. Its working.. Thanks


  7. i was searching for this for a quite a long time.Thank you so much for the share.will get back to you after exxecuting it.. 🙂


  8. Hi,

    I am getting below error ‘Item Cannot be found in the collection corresponding to the requested name or ordinal’

    Cells(Row, 1) = Rec_set.Fields(1).Value ‘Writing fetched data to excel

    can anyone help me to sort out this issue?


  9. how can i get the IP address?

Liked it? Share Your Thoughts!

This site uses Akismet to reduce spam. Learn how your comment data is processed.