Pass Through Query from Access to Oracle

[Analyst2004]

I need to query Oracle database while I am in Access. I am able to programatically create a passthrough query and change its definition as required.

My problem is that my query gives ODBC error and can't connect first time. However, if I manually do a sample passthrough query and then come back and run my VB code (hidden behind a button),then it returns the data successfully. So, once connection is estb it works fine.
What am I doing wrong in the connection string ??

qdf.connect = "ODBC;DSN=pmd2;DRIVER={Microsoft ODBC for Oracle};USID=hans;PWD=hans;SERVER=pmd2"

Oralce uid/pwd/server as stated above
DSN = cmd2

ERROR : ODBC connection to ={Microsoft ODBC for Oracle}pmd2 failed.

Please advise !!

thanks

 

[psemianonymous]

Help by example? Here's how one of my passthrough queries looks, connecting to Oracle8i...

[tt]
ODBC;DSN=ORACLE;DBQ=orcl;DBA=W;APA=T;FEN=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;PFC=50;TLO=0;
[/tt]

 

[PHV]

Have you created a system DSN named pmd2 with ODBC manager ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Analyst2004]

Thanks friends for your suggestions....
I used ORACLE ODBC Driver instead.I created a System Data source and tried to run the code. It is ABLE to connect.

However each time it asks for the
Service name and User name (pwd already populated).

Even though I am supplying the connection service name,usid/pwd--- why does it still prompt me , Is there way I can avoid this as I want to make this transparent to the User ??

My connection code:

qdf.connect = "ODBC;DRIVER={Oracle ODBC Driver};System Data Source=pmd2;SERVICE NAME=pmd2;USID=hans;PWD=hans;




Oralce uid/pwd/server as stated above
DSN = pmd2

 

[Analyst2004]

ok foolio12 & PHV

I tried your advice and in the query design - properties saved the ODBC info (which appended few things to my string as below.) So, on copying this in my Code, I was able to avoid the prompting of server & user info and was able to run it Successfully.

THANKS A TON!!

qdf.connect =
"ODBC;DRIVER={Oracle ODBC Driver};SERVER=pmd2;UID=hans;PWD=hans;DBQ=pmd2;DBA=W;APA=T;PFC=1;TLO=O;"