ADO Connection to ORACLE 9.2 through EXCEL fails

[Dave Bash]

I am trying to hit the scott tiger schema in ORACLE through EXCEL VBA
(XP/XP)without success. I get an error when the connection is
attempted:

ORA-12154: TNS:could not resolve service name

If I go into Windows / Control Panel / Administrative Tools / Data
Sources I can test the connection to myDB Scott tiger and it passes.
Likewise, I can go to MS ACCESS and link the table via ODBC and see
them through Access as well.

Why am I not able to connect via ADODB?

Please Help,

Thanks,



Here is sample code, I have tried several other snipets that have
worked in other implementations in the past as well ...

Option Explicit
Public Sub Macro1()
Dim Conn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim Rs As New ADODB.Recordset
Dim strSql As String
Dim strConn As String
Dim i As Integer


strConn = "PROVIDER=MSDAORA;DATA SOURCE=myDB;USER
ID=scott;PASSWORD=tiger;" & _
"PERSIST SECURITY INFO=TRUE;"
strSql = "SELECT * FROM EMP"

Conn.ConnectionString = strConn
Conn.CursorLocation = adUseServer
Cmd.CommandText = strSql
Cmd.CommandType = adCmdText
Set Rs.Source = Cmd
Conn.Open ''''''''''''''''''''''''''''''''''''''ERROR APPEARS AT
THIS LINE
Set Cmd.ActiveConnection = Conn

Rs.CursorType = adOpenForwardOnly
Rs.LockType = adLockPessimistic
......................................................

 

[Rob van Gelder]

Assuming myDB is in your tnsnames.ora file of the default Oracle home, it
should work.

I've had DATA SOURCE=tns:myDB in the past - not sure if that makes a
difference though.

 

[Guest]

Hi Dave

Why am I not able to connect via ADODB?
"Dave Bash" wrote:

Have you set a referance to the librarry
tools/reference/microsoft active data objects 2.x (I think that's right out
late)
post back if you are still having problems and I'll post you some code


good luck
TK

 

[Dave Bash]

Hi Dave


Have you set a referance to the librarry
tools/reference/microsoft active data objects 2.x (I think that's right out
late)
post back if you are still having problems and I'll post you some code


good luck
TK

I have this referenced to ADO (Active Data Object) - that isn't the
issue - it must be the way it is attempting to get to the Oracle
database, the provider, or the tnsnames.ora file - (I am not really
familiar with that or know how to check)

Thanks.

 

[Dave Bash]

Rob - here is the file: and no myDATA .... btw - I thought I was using
the "default" Oracle set-up and that scott tiger was on myData ...



# TNSNAMES.ORA Network Configuration File:
C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = amd2100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = name.domain)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = amd2100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

 

[Rob van Gelder]

If you can connect via SQLplus, then I'm at a loss...
I haven't spent much time troubleshooting ADO connections. It's always just
worked for me without hassle.

The only other thing: I don't use "PERSIST SECURITY INFO=TRUE;"
But that's probably not it.

Suggest you approach a different newsgroup for the solution:
microsoft.public.data.oledb

 

[oraclevsmicrosoft]

You may have a problem like a "WORLD." prefix missing in your db
name.
ou may also try another connection string for instance:
strConn = "PROVIDER=MSDAORA;Data Source=" &strMyDbName & ";User Id="
& strUser &";Password=" & strPwd & ";"
Check out my blog http://oraclevsmicrosoft.blogspot.com (last weeks
entries) for other types of connection from Oracle to Excel.


oraclevsmicrosoft

 

[Dave Bash]

You are 100% on the money! I actually got the solution this morning
from an Oracle guru and was looking to post it when I saw your post.

I needed to alter the TNSNAMES.ORA file to have it include the WORLD
suffix ( I used JOE.WORLD where it previously said "NAME". I then
changed the "myDB" in the data source to JOWE.WORLD. I resaved the
TNSNAMES.ORA File and then modified my ADODB code as follows:

Sub OpenDatabase(DB As ADODB.Connection, Provider As String,
DataSource As String, UserID As String, Password As String)
Set DB = New ADODB.Connection
DB.ConnectionString = "Provider=" + Provider + ";Data Source=" +
DataSource + ";User ID=" + UserID + ";Password=" + Password + ";"
DB.ConnectionTimeout = 30
DB.CursorLocation = adUseClient
DB.Mode = adModeReadWrite
DB.Open
End Sub

Sub b()

Dim DB As ADODB.Connection
Call OpenDatabase(DB, "oraoledb.oracle.1", "Joe.world", "scott",
"tiger")
Set records = DB.Execute(strSQL, Count)
CountofRecords = records.RecordCount
'''Error Check Point
If CountofRecords = 0 Then
MsgBox ("The query returns no data from Scott Tiger")
Exit Sub
Else
arrRec = records.GetRows
..
..
..

Thanks for the help.

dB

 

[oraclevsmicrosoft]

Thank you for the feed back. I just realize I made a mistake: of
course "World" is a suffix here, not a prefix...

http://oraclevsmicrosoft.blogspot.com