How to connect to a Teradata database on a remote server

[Szurya]

Hello friends,

I need help in connecting to a Teradata database on a remote server. I could comeup with a VBA for connecting to 'Teradata' database locally with in that server and want to move my solution a step further where the user would be able to connect to the database in a server and get the result into the Excel sheet in their local system.

This is what I'm doing now
1. Connect to the server via 'mstdc' in windows, enter User ID and Password Manually.
2. Once connected, open an excel sheet and run this below VBA script, which connects to the Teradata database and runs a query(built in the VB script) and provide the result.

My VB Script:

Sub CommandButton1_Click()


CreateObject ("ADODB.Connection")
Dim conn As ADODB.Connection
Dim rec1 As ADODB.Recordset
Dim thisSql As String


Set conn = New ADODB.Connection


conn.Open ("DRIVER={Teradata};DBCNAME=xxx.xx.xx.xx;UID=xxxx;PWD=xxyy;QUIETMODE=YES;")


thisSql = ""
thisSql = thisSql & " SELECT         calendar_date, a.Section_Number,a.class_number,a.subclass_number, "
thisSql = thisSql & " cast(SUM(Sales_Value) as decimal(18,4)) sales_value, "
Set rec1 = New ADODB.Recordset
rec1.Open thisSql, conn


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


End Sub

Thanks
Surya