pass through query for ODBC links

[Guest]

hi

i use an autoexec macro to run several queries on a db.(db opens through a
scheduled batch file)

the queries use linked oracle tables (through ODBC's) to 12 different
databases in 12 different servers

the problem i have is that when i run the macro, the queries still opens a
popup window for the connections details:

'Microsoft ODBC Oracle Connect'
'User Name: ....
'Password:....
Server:....

obviously this problem require a user to input the passwords for each
connection, thus cannot use an automated option.

-i have tried using the save password when you set up the odbc, but if you
update the link the password is then lost

-i have also tried using a pass through query with the connection details
entered in the properties string(ODBC; DSN=test_db; server=test_ser;
UID=test_user; pwd=test_pass), but i get the following error:
ODBC--call failed.
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00936: missing expression
(#936)

any suggestions?

thanks a lot

 

[[MVP] S.Clark]

Maybe execute the queries with VBA code. Using ADO, you can setup the
connection strings, store the pwds, etc.

 

[Guest]

I've never used ADO and I am still novice with VBA.

do you/anyone can help with this?

Thank you

Maybe execute the queries with VBA code. Using ADO, you can setup the
connection strings, store the pwds, etc.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

hi

i use an autoexec macro to run several queries on a db.(db opens through a
scheduled batch file)

the queries use linked oracle tables (through ODBC's) to 12 different
databases in 12 different servers

the problem i have is that when i run the macro, the queries still opens a
popup window for the connections details:

'Microsoft ODBC Oracle Connect'
'User Name: ....
'Password:....
Server:....

obviously this problem require a user to input the passwords for each
connection, thus cannot use an automated option.

-i have tried using the save password when you set up the odbc, but if you
update the link the password is then lost

-i have also tried using a pass through query with the connection details
entered in the properties string(ODBC; DSN=test_db; server=test_ser;
UID=test_user; pwd=test_pass), but i get the following error:
ODBC--call failed.
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00936: missing expression
(#936)

any suggestions?

thanks a lot