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:
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
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.
I am still getting error on "Dim rs As New ADODB.Recordset" as User-defined type not defined... Can you help on this?
In VB, Go to Tools>References.
Tick the latest version of "Microsoft ActiveX Data Objects" library that you have.
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
And I don't quite understand how the FOO.UDL file connected to the excel file, seems like they are independent.