Connection String Basics, VBA to Teradata SQL Assistant

Connectivity

Connection String Basics, VBA to Teradata SQL Assistant

I'm trying to write VBA code that will allow me to run a macro in Microsoft Excel 2007 that connects to Teradata SQL assistant (version 13.10), runs SQL (it says ODBC, if that is meaningful), and brings back the results to Excel.  I've spent a few hours looking around on the internet for how to do this and discovered that it is done via connection strings.  Unfortunately, I am a bit out of my depth with this, and don't know how to create a connection string that properly connects to the SQL database.  I've developed the following VBA code:

Sub RunSQL()

Dim UserID As String

Dim Pass As String

    UserID = InputBox(Prompt:="Enter your User ID.")

    Pass = InputBox(Prompt:="Enter your Password.")

Dim strConn As String

strConn = "Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=OSWSQLP01;Integrated security=SSPI;Initial Catalog=AEDWPROD;User ID =" & UserID & ";Password=" & Pass

Dim Query As String

Query = "select * from DB_WRK_ACT.CERF_datevar"

Dim rs As New ADODB.Recordset

rs.Open Query, strConn

Sheet1.Range("A1").CopyFromRecordset rs

'

End Sub

However, when I run this code I get the error message that login failed for my user ID (at the rs.Open step).  This leads me to believe that my connection string is close to being correct, but is either missing something or using the incorrect provider and/or data source.  I'm not experienced enough to identify what I'm missing, and I stole the provider/data source from someone else's excel file and am not sure they are correct.

My questions are:

1. Is my connection string structured correctly, or am I missing/including something I shouldn't be?

2. How can I find out what the correct provider/data source fields to use are? (e.g. if SQLOLEDB.1 is the correct provider, how would I find that out for myself?

Thank you!

5 REPLIES
Teradata Employee

Re: Connection String Basics, VBA to Teradata SQL Assistant

You are trying to use ADO + "Microsoft OLE DB Provider for ODBC" + "ODBC Driver for Teradata" .

1- User ODBC Administrator to setup an ODBC Data Source. You might have one already if you are using the Teradata SQL Assistant with the ODBC Driver for Teradata.

2- Use Windows Explorer to create a new empty text file. Rename the file to FOO.UDL

3- Double Click FOO.UDL. You will see the "Data Link Properties" dialog box. Click Provider Tab; select Microsoft OLE DB Provider for ODBC; select connection tab; select "Use Data Source Name"; enter User Name; enter Password; Click Test Connection; click Allow saving password; Click OK.

4- Open Foo.UDL using Notepad.exe;

5- You will see the connection string.

6- Remove your password from the Foo.UDL file.

Re: Connection String Basics, VBA to Teradata SQL Assistant

I am still getting error on "Dim rs As New ADODB.Recordset" as User-defined type not defined... Can you help on this?

Re: Connection String Basics, VBA to Teradata SQL Assistant

Try this:

In VB, Go to Tools>References.

Tick the latest version of "Microsoft ActiveX Data Objects" library that you have.

Fan

Re: Connection String Basics, VBA to Teradata SQL Assistant

I followed this discussion because I want to run sql assistant from EXCEL VBA, but it keeps giving me an error message like this which I don't know why

[DBNETLIB][ConnectionOpen(Connect()).]SQL Server doesnot exist or access denied.

Do I need to do any set up except for the ones shown above? Thank you

Fan

Re: Connection String Basics, VBA to Teradata SQL Assistant

And I don't quite understand how the  FOO.UDL file connected to the excel file, seems like they are independent.