MS Excel & Oracle XE

[rbharij]

Hi,
I am trying to connect to an Oracle XE database from excel. I have referenced the MS Active X Object Library 6. The code below executes up to the point of executing the sql statement. I get the error message:

'The connection cannot be used to perform this operation. It is either closed or invalid in this context.'

I would appreciate any help in solving this.

    Dim conn, rs
    Dim strSQL As String
     
    Set conn = CreateObject("ADODB.Connection")
    With conn
        .ConnectionString = "Provider = MSDAORA;Data Source = 192.168.0.195:1521/XE;User ID = travel;Password = travel"
        .Open
    End With
     
    Set rs = CreateObject("ADODB.Recordset")
    strSQL = "select * From airlines"
    rs.Open strSQL
          
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing

[Siddharth Rout]

I have referenced the MS Active X Object Library 6.

Welcome to the forums

Try this... A slight change in your code. if it doesn't work, then we will take it from there...

Sub Sample()
    Dim conn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim strConn As String, strSQL As String
    Dim myUser As String, myPassword As String
    
    myUser = "travel"
    myPassword = "travel"
    
    strConn = "Provider=MSDAORA;Data Source=192.168.0.195:1521/XE;" & _
    "Password=" & myPassword & ";User ID=" & myUser & ";Persist Security Info=True"
    
    conn.Open strConn
    
    strSQL = "select * From airlines"
    rs.Open strSQL
          
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub