ADO Connection to ORACLE 9.2 through EXCEL fails
 

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
.................................................. ....

ADO Connection to ORACLE 9.2 through EXCEL fails
 

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.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Dave Bash" wrote in message
om...
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
.................................................. ...

ADO Connection to ORACLE 9.2 through EXCEL fails
 

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

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
.................................................. ....

ADO Connection to ORACLE 9.2 through EXCEL fails
 

"TK" wrote in message ...
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


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.

ADO Connection to ORACLE 9.2 through EXCEL fails
 

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" wrote in message ...
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.


--
Rob van Gelder - http://www.vangelder.co.nz/excel

ADO Connection to ORACLE 9.2 through EXCEL fails
 

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Dave Bash" wrote in message
om...
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" wrote in message
...
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.


--
Rob van Gelder - http://www.vangelder.co.nz/excel