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