MS Excel & Oracle XE

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.

Code:

---

    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

---

Quote:

---

I have referenced the MS Active X Object Library 6.

---

Welcome to the forums :wave:

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

Code:

---

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

---