Problem creating a ODBC Connect string to loop through

[Anonymous]

We have several databases with the same structure that I want to loop through instead of repeating the same code. I've tried the following code but get a 'Data source name not found ....' error message. Any ideas welcome or alternative method.

[Code]
SET Connect1 = 'abz (XUserId is abc, XPassword is zxz)';
SET Connect2 = 'sca (XUserId is abc, XPassword is xyz)';
LET iCount =1;

For iCount = 1 to 2
LET ConnectStr = 'Connect' & iCount;

ODBC CONNECT TO [$(ConnectStr)];
SELECT {Select Code}

Next
[/Code]

[#2 · Accepted solution]

Hi dendraw,

the value of your ConnectStr is (according to the LET of For..next) "connect1" or "connect2". But you want to get the value of the variable "connect1" or "connect2". So you need an additional statement (variable, sorry for the name ) like this:

For iCount = 1 to 2
LET TT= 'Connect' & iCount;
LET ConnectStr= $(TT);
//ODBC CONNECT TO [$(ConnectStr)];
//SELECT {Select Code}
Next

Regards, Roland

[Anonymous]

Hi Roland

Many thanks. I had to also remove the square brackets from around the string. Work great now. . Right now I'm not sure why it has to be done to get the value of the variable but will work that through.

Regards, Dave Andrew