Connect via Oracle Provider for OLE DB from Visual Basic CRASHES

[J.L. PAUL]

Hello,

I'm trying to use the Oracle Provider for OLE DB from VB, but
unsucessfully !
Everything is OK with Microsoft OLE DB Provider for Oracle, but with
the Oracle Provider it's impossible to connect.

Error received :
-2147467259-ORA-12154: TNS:Could not resolve service name

- My <alias-user-password> works well with the SQL Plus utility.
- The tnsping <alias> is OK.

Versions : oracle 7.3.4/OLE DB Provider 8.1.7.2.0/VB 6.0 sp4/NT 4.0
sp5

Any ideas ?
Thanks in advance, any suggestion will be appreciated.

Jean-Louis.

Here's the code :

.
.
.

Set mconConnection = New ADODB.Connection

With mconConnection
' this one is working well :
' .ConnectionString = "Provider=MSDAORA.1;" & _
"Data Source=" & sAlias & ";User ID=" & sUser & ";Password="
& sPwd & ";"
.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=" & sAlias & ";User ID=" & sUser & ";Password="
& sPwd & ";"

.ConnectionString = .ConnectionString & "OSAuthent=0;PLSQLRSet=1;"
.Open ' <---- it crashes here : ORA-12154

End With

[TurkBear]

If you have multiple Oracle homes, check to be sure the tnsnames.ora file is the
same in each..or set a TNS_ADMIN key in the registry ( details if needed on
request) to specify the location to use...
The Microsoft provider and the Oracle provider may be 'looking' in separate
homes ( depends on the version)..
If you have only one home,. then I guess its an Oracle provider failure ( Not
all that surprising, ODBC and OLE drivers have not been their long suit)

jlp...@transiciel.com (J.L. PAUL) wrote:

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

[Justin Cave]

jlp...@transiciel.com (J.L. PAUL) wrote in message news:<74d4afd7.01061...@posting.google.com>...

> Hello,
>
> I'm trying to use the Oracle Provider for OLE DB from VB, but
> unsucessfully !
> Everything is OK with Microsoft OLE DB Provider for Oracle, but with
> the Oracle Provider it's impossible to connect.
>
> Error received :
> -2147467259-ORA-12154: TNS:Could not resolve service name
>
> - My <alias-user-password> works well with the SQL Plus utility.
> - The tnsping <alias> is OK.
>
> Versions : oracle 7.3.4/OLE DB Provider 8.1.7.2.0/VB 6.0 sp4/NT 4.0
> sp5

I don't believe you can install the OLE DB provider on top of an
Oracle7 client. I believe you have to have at least an 8i client.

Justin Cave

[Mark Fry]

Hi,
I think I have a very similar problem. A VB 6 program that
connects to an Oracle 8.1.6 database fine from one NT4 workstation
(where there is only one Oracle Home, for 8.1.6 Client programs),
fails to connect to the same database from my NT4 (SP5?) workstation
when using the MSDAORA OLE-DB provider. The same VB program works fine
when using the Oracle-supplied OLE-DB provider, but that causes other
problems.

I have discovered that I have two Oracle Homes on my machine - one is
for the 8.1.6 client, and the other is an old OraNT 7.3.4 installation
from a previous user. So far, two attempts to uninstall the OraNT Home
have failed, so I think I'm stuck with it for the time being.

Could you tell me more about using a TNS_ADMIN Registry key to
persuade MSDAORA to use my 8i Home, please?

Cheers
Mark Fry

TurkBear <no...@nowhere.com> wrote in message news:<s1a1jtcko2cdr29vj...@4ax.com>...

[Mark Fry]

Not sure if it's the done thing to answer one's own posting, but
anyway...

I've found that TNS_ADMIN can be an NT environment variable, which is
simpler to set than a Registry key (I think): Settings; Control Panel;
System; Environment tab.

So I've set TNS_ADMIN = c:\<my oracle 8.1.6 home dir>\Network\Admin
and bingo! my VB 6 program will connect to the correct database
through MSDAORA.

mark...@acnielsen.co.uk (Mark Fry) wrote in message news:<af8f834e.01071...@posting.google.com>...