Excel Querytable ODBC Connection Issue

Database
Enthusiast

Excel Querytable ODBC Connection Issue

I am using this connection string in an Excel Querytable connection for a Querytable add method.
I would like to connect to the teradata database directly without an ODBC system name set up.
I have the IP address of the servers.
Can someone help please.

sConn = "ODBC;DSN=myDSN;UID=myID;PWD=myPWD;DATABASE=myView; "
Set oQt = Workbooks(myWB).Worksheets(myWS).QueryTables.Add(Connection:=sConn, Destination:=Workbooks(myWB).Worksheets(myWS).Range(strDestinationCell), sql:=strSQL)

James Coutre
5 REPLIES
Teradata Employee

Re: Excel Querytable ODBC Connection Issue

Driver={Teradata};DBCName=myDBCName;UID=X;PWD=Y;Database=foo;

I assume ODBC at the very beginning is used internally by Excel and not passed to Driver. Therefore add ODBC; to the very beginning of the connection string.
Enthusiast

Re: Excel Querytable ODBC Connection Issue

Thanks,

I will test this. What I am a little confused by is the DBCName

So this: sConn = "ODBC;DSN=myDSN;UID=myID;PWD=myPWD;DATABASE=myView; "

Becomes this: sConn = "ODBC;Driver={Teradata};DBCName=uskihdbs1.ktr.store.com;UID=myID;PWD=myPWD;Database=myView;

Would that work then. Is the DBCName correct?

Jim
Teradata Employee

Re: Excel Querytable ODBC Connection Issue

DBCName is the name of your Teradata system.
For example if your system name is TestBox and the IP address for COP1 is 101.100.99.98 then the following connection strings are all valid.

Driver={Teradata};DBCName=TestBox;UID=X;PWD=Y;Database=Foo;

or

Driver={Teradata};DBCName=101.100.99.98;UID=X;PWD=Y;Database=Foo;

or

Driver={Teradata};DBCName=TestBoxCOP1;UID=X;PWD=Y;Database=Foo;

I recommend the first one.
Enthusiast

Re: Excel Querytable ODBC Connection Issue

Works great. Thanks a million.....
N/A

Re: Excel Querytable ODBC Connection Issue

Hi

I'm having a similar sort of problem, but I can't create a new post here in work.

I have a VBA macro which runs several Teradata SQL queries using an ODBC connection. However, the user is prompted to enter their login details for each query - I'd like them to enter them once at the outset and then each of the subsequent queries to use their credentials. I've created a procedure to establish a connection at the outset which seems to work fine, but I've tried various combinations of values for the QueryTables.Add(Connection...) string and continue to get the ODBC dialog box each time! I've checked the combinations of the strings mentioned above but can't seem to get the right combination of values so that everything is captured to create the single connection at the start and then reused for the subsequent queries without the user having to be prompted to choose or enter anything else.

Any ideas??

Thanks