Problem Connecting to Oracle ODBC

[UThomas]

I'm trying to link into an Oracle database through MS Access linked tables so I can run some reports. I have setup the Oracle database in the tnsnames.ora file, and can access the database using SQL Plus. So my permissions and everything are there. BUT, when I try to link in using a new DNS or a direct ODBC call I get a run time error:

COULD NOT RESOLVE SERVICE NAME

Any suggestions? I am entering the same user ID and password I use to logon to SQL Plus.

Thanks,

Thomas

************TEST VBA CODE**********************

Private Sub Command0_Click()
Dim cnoracle As Connection
Dim rsoracle As Recordset

Set cnoracle = New Connection
With cnoracle
.Provider = "MSORAOLE"
.ConnectionString = "Driver={Microsoft ODBC for ORACLE};
ODBC; UID= UID; PWD= PWD; Server=gbv03p"
.Open <---------- hangs on this command
End With
Set rsoracle = New Recordset
rsoracle.Open "SELECT full_name FROM gv_user", cnoracle
MsgBox rsoracle!full_name
End Sub

************TNSNAMES.ORA ENTRY***************
gbv03p = (DESCRIPTION =

(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = sjcds007)(PORT =1521)))

(CONNECT_DATA = (SID = gbv03p)(GLOBAL_NAME= gbv03p.world)))

 

[FoFa]

Sometimes you have to prefix your userid when acessing oracle, as an example here I have to put OPS$userid when I access Oracle externally. Also the gbv03p is the name you setup in TNSNAMES correct?

 

[UThomas]

"Also the gbv03p is the name you setup in TNSNAMES correct?"

Yep. Thats the thing that gets me. Its not saying "Your logon is incorrect," its not even seeing the server. And since permissions are ok (I can log on through SQL Net), it has to be my connection string. I've tried making a DNS and no luck.

So it must be how I am referencing the server, but everything I have seen on it says use the TNSNAMES.ora entry. Neither gbv03p.world or gbv03p works.

 

[FoFa]

The only other thing I could add is use the Oracle ODBC driver instead of MS's, that has changed things for me a time or two.

 

[UThomas]

Thanks. How would I do that? It it software I have to install or do I just reference differently in the connection string? Or a registry change?

 

[FoFa]

Typically when you install the Oracle network stuff (like SQLPLUS) you get the Oracle ODBC driver also. When you setup a new ODBC connection, you can select the Oracle driver instead of MS's Oracle driver.

 

[UThomas]

Thanks. I installed the easysoft open source ODBC driver. When I try to connect using the same information I feed the Net8 Assistant, it now says:

"Failed to connect to DSN [...] No License Slots Available"

So is this something I need to contact the DBA about?

Like I said, I can get on with Net8 Assistant and SQL*Plus but thats about it. Neither of those give me the development environment I need.

 

[Hypernoodle]

I've had the bast luck using the Oracle ODBC driver as installed by the Net8 Client. Try using this driver with the following connect string:

"DRIVER={Oracle ODBC Driver};DBQ=DBNAME;UID=UID;PWD=PWD;DBA=W;LOB=T;RST=T;FRL=T"

Make sure the DBNAME is properly set up in your tnsnames and you can connect to it using the same user/id through SQLPlus

 

[UThomas]

GLORIOUS! It works! Thanks for all the help guys. This is what I learned:

MS Oracle Drivers suck

Easy Soft Oracle Drivers rock

You have to get a test license from Easy Soft or it gives you an error

2 weeks of not finding an answer makes irritable