My requirement is to run a SQL query on Oracle DB and return the results obtained from it into an XLS. I am new to VB programming and have searched for content on the internet and have put together the code as below but it is not working and failing with error message
Run time error 3709. The connection cannot be used to perform this operation. It is closed or invalid in context.
But when i execute the SQL query on Oracle DB it runs fine and gives me results. When i hit debug it highlights the line Set RS = Cmd.Execute. Please help
Sub TEST_Connection()
Dim con As ADODB.Connection
Dim RS As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set RS = New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
Dim UID As String
Dim PWD As String
Dim Server As String
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=xxxxx)(PORT=xxxxx))" & _
"(CONNECT_DATA=(SID=xxxxx))); uid=xxxxx; pwd=xxxxx;"
con.Open (strCon)
Set Data = Sheets("Sheet1")
Data.Select
Range("A:F").ClearContents
Cmd.CommandType = adCmdText
sqlText = "select request_id from pom__products where rownum <3"
Cmd.CommandText = sqlText
Set RS = Cmd.Execute
For X = 0 To 10
Data.Cells(1, X + 1) = RS.Fields(X).Name
Next
Do While Not RS.EOF
Row = Row + 1
For Findex = 0 To RS.Fields.Count - 1
Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
Next Findex
RS.MoveNext
Loop
Application.Calculation = xlCalculationAutomatic
Application.Calculate
End Sub
Bookmarks