CODEWITHSUNDEEP
sqlodbcwith-clause
Dominik Niewiadomski
Dominik Niewiadomski

Reputation: 1

ODBC doesn't return data when using WITH clause

I'm trying to connect to SQL Developer and get data into Excel using ODBC driver, let's make the query very simple for example purposes:

with temp_tab as (select * from table1)
select * from temp_tab where x = y

It executes in SQL Developer without problems and also works in Excel when WITH clause is removed, but in the above form it fails with error

"Operation is not allowed when the object is closed."

Do you have any idea what might be the problem with ODBC when using WITH clause?

I am using Microsoft ODBC for Oracle version 10.000.19041.01 and SQL Server version 10.00.19041.3086.

Here you can find VBA macro that I use to get the data from SQL and copy into Excel workbook. Line where error occurs is in bold:

'SQL query:
Dim strQuery As String
strQuery = ""
    
' Variables related to connection with SQL
Dim adoCM As ADODB.Command
Dim adoCN As ADODB.Connection
Dim adoRS As ADODB.Recordset

Set adoCN = New ADODB.Connection
    adoCN.CursorLocation = adUseClient
    adoCN.Properties("Prompt") = adPromptComplete

Application.ScreenUpdating = True
Application.ScreenUpdating = False
    adoCN.Open "Data Source=xxx", "yyy", Password

Set adoCM = New ADODB.Command
Set adoCM.ActiveConnection = adoCN

Set adoRS = New ADODB.Recordset
    
    adoCM.CommandType = adCmdText
    
    dblStartTime = Now
    strQuery = ThisWorkbook.Worksheets("Sheet1").Range("G3").Value
    adoCM.CommandText = strQuery
    Application.ScreenUpdating = True
    Application.ScreenUpdating = False
    adoCM.CommandTimeout = 3600
    Set adoRS = adoCM.Execute
    **ThisWorkbook.Worksheets("Sheet1").Range("G5").CopyFromRecordset adoRS**
    dblEndTime = Now - dblStartTime

    Application.ScreenUpdating = True
    Application.ScreenUpdating = False
    
    adoCN.Close

Upvotes: 0

Views: 79

Answers (0)

Related Questions