OLEDB Provider for Oracle (REMOTE)

From: Paul O. Schenker <poschenker_at_access.ch>
Date: Thu, 30 Sep 1999 22:18:55 +0200
Message-ID: <7t0ggu$cu2$1@pacifica.access.ch>

Hi everybody,  

On a LAN and WAN (by ISDN Router) I am trying to get connected to = different Oracle8i Databases by Visual Basic Code (6.0 SP 3, on Windows = NT/4 SP5, with Microsoft Data Access 2.1). And the following code works = fine, since it does not make use of the Remote Provider:  

    gsADOConnect = "Provider=MSDAORA.1;Password=" & gsPassword & = ";" & _

    "User ID=" & gsUserName & ";Data Source=" & gsHostString & = ";Persist Security Info=false"

    Set cnn = New ADODB.Connection
    With cnn

        .Provider = "MSDAORA.1"
        .ConnectionString = gsADOConnect
        .CursorLocation = adUseClient
        .Open

    End With
    If cnn.State = adStateOpen Then
    ...
    end if  

However, if I want to do the same with the following code, I keep = getting error messages like: "ORA12154: TNS: could not resolve service = name."  

  Set cnn = New ADODB.Connection
  With cnn

    .ConnectionTimeout = 60   '= 60 seconds
    .CursorLocation = adUseClient
    .Open "Provider=MS Remote;Remote Provider=MSDAORA;Data =

Source=gsDatabaseAlias;User ID=" & _

    txtUserName.Text & ";Password=" & txtPassword.Text & ";Remote = Server=http://" & txtServerName.Text
  End With
  If cnn Is Nothing Then
    MsgBox "Sorry, Login failed!", vbCritical, "XXX Login"     GoTo theEnd
  Else
    sQ1 = "select x,y,z from a"
    Set rs1 = New ADODB.Recordset
    With rs1
.ActiveConnection = cnn
.Source = sQ1
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open 'here the error occurs !!!!
    End With
  end if  

Previously I had tested this code with an Oracle DB Version of = 8.0.3.0.0. and it worked fine and I had to code "Data Source=" without = giving a TNS name. But now with Oracle8i (8.1.5.0) and its new Net = Service Names (differently set up), I do not know what to do in order to = get the connection working.  

Could somebody help, please!  

With my kindest regards
Paul Received on Thu Sep 30 1999 - 15:18:55 CDT