teradata driver upgraded from 12 to 14; excel vba connection string wont work now

Connectivity

teradata driver upgraded from 12 to 14; excel vba connection string wont work now

Recently changed from 12 to 14, a macro we had to auto connect to Teradata that worked fine with 12 now gives the error [Teradata][ODBC Teradata Driver] Not enough information to log on.

Is there something that needs to be modified?  VBA code is below, with comment on where error is occuring...

Sub qryTeradata(userid As String, password As String, XLSQL As String, exceltab As String)

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

Dim cmdSQLData As ADODB.Command

Set cmdSQLData = New ADODB.Command

'Open connection with Teradata THIS IS WHAT IS CAUSING THE ERROR

   cn.Open "Data Source=ntl_prd_allvm;" & _

           "Database=ntl_prd_allvm;" & _

           "Persist Security Info=True;Session Mode=ANSI;" & _

           "Uid=" & userid & ";" & _

           "Pwd=" & password & ""

4 REPLIES

Re: teradata driver upgraded from 12 to 14; excel vba connection string wont work now

Update...looks like the issue lies in userid and password.  I have them saved in ODBC administrator (User DSN tab...configure option); when I had 12 installed they would be read automatically, now they are not.  Is there a way to get them read without explicitly putting it in the VBA?

Re: teradata driver upgraded from 12 to 14; excel vba connection string wont work now

Was able to figure it out...I just commented out the pieces that requested userid and password and it seems to have worked ok.

Re: teradata driver upgraded from 12 to 14; excel vba connection string wont work now

Hi Pirrejo, 

Can I get the syntax you used to connect Terdata from VB Excel. I am using the below code and it is giving error "Application User not Defined"

Sub RunSQL()

Dim UserID As String

Dim Pass As String

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

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

Dim strConnection As String

Dim cnnTeradata As ADODB.Connection

strConnection = " Provider=Teradata;DBCName=DWSANA;Database=U_UKRBI_PERFRPT_DVLDB; User ID = & UserID & ;Password= & Pass"

Set cnnTeradata = New ADODB.Connection

cnnTeradata.Open strConnection

Dim Query As String

Query = "select * from U_UKRBI_PERFRPT_DVLDB.MDA_AUTOREG_AGGREGATE"

Dim rs As New ADODB.Recordset

rs.Open Query, strConnection

Sheet1.Range("A1").CopyFromRecordset rs

'

End Sub

Teradata Employee

Re: teradata driver upgraded from 12 to 14; excel vba connection string wont work now

ADO uses an OLE DB Provider; in this case "Microsoft OLE DB Providr for ODBC" + "ODBC Driver for Teradata".

Follow the steps in section 3 of

http://developer.teradata.com/blog/netfx/2011/12/setup-sql-server-2008-r2-linked-server-to-the-terad...  

then open the File with a text editor and copy the connection string. The connection string will be: "Provider=MSDASQL;Password=Y;Persist Security Info=True;User ID=X;Data Source=Odbc_DSN_name;"