ODBC Connection / Excel 2007 pulling Oracle data vba

kwieckii
kwieckii used Ask the Experts™
on
I'm running a query through Excel where I've passed the connection using the driver: Microsoft ODBC for Oracle.

Most queries run fine.  I have a problem when I write a query that is available with Oracle 10g:

strsql01 = "    with x as "
strsql02 = " (select 123 ID from dual) "
strsql03 = "select * from x "

getsql = strsql01 & strsql02 & strsql03

When I try to run this - I receive the following error:

Run-time error 3704 Operation is not allowed when the object is closed.



Thanks in advance!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Your connection isn't open hence the error.

NG,
If you have other queries that use the same connection and they are running correctly then your connection is being closed before getting to your query above.

If the query isn't running at all then I would check the username/password, etc. and make sure the connection works before proceeding.

NG,

Author

Commented:
I think it has something to do with the query using 'with' and that the driver isn't compliant with 10g... another query in the same exact place - changing nothing will work:

select 'x' from dual --- will work

I know the connection is closing - but I think the message isn't telling me what's really going on.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Have you tried using the ORACLE driver instead?

NG,

Author

Commented:
It needs the Microsoft driver because Excel needs it to talk to Oracle.

Author

Commented:
What I'm thinking is the connection may close because of the 'with x as' part.

Here's the code I'm using --

Sub test(cnnstringname As String, reportname As String)

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cnn.ConnectionString = cnnstring(cnnstringname)

cnn.Open
Set rs = cnn.Execute("select 'x' from dual")

    Set wks = Nothing
 
    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets("reportname")
    Application.DisplayAlerts = False
    If Not wks Is Nothing Then
        wks.Delete
    End If
Application.DisplayAlerts = True
    On Error GoTo 0

Sheets.Add
ActiveSheet.Name = "Data"
Sheets("Data").Select

Range("A2").CopyFromRecordset rs

cnn.Close
End Sub



And this is what I would like to work - but doesn't:


Sub test(cnnstringname As String, reportname As String)

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cnn.ConnectionString = cnnstring(cnnstringname)

cnn.Open
Set rs = cnn.Execute("with x as (select 'x' from dual) select * from x")

    Set wks = Nothing
 
    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets("reportname")
    Application.DisplayAlerts = False
    If Not wks Is Nothing Then
        wks.Delete
    End If
Application.DisplayAlerts = True
    On Error GoTo 0

Sheets.Add
ActiveSheet.Name = "Data"
Sheets("Data").Select

Range("A2").CopyFromRecordset rs

cnn.Close
End Sub
I've never used a SQL statement that doesn't begin with select...

Not sure what your trying to achieve with your query? Some sort of subquery?

Does this run in oracle?

Set rs = cnn.Execute("with x as (select 'x' from dual) select * from x")
Set rs = cnn.Execute("with x as (select 'x' from dual) select * from x")
Sorry, meant this SQL statement..

with x as (select 'x' from dual) select * from x

A subquery should look like...
Select x.* from (select * from table) x

Author

Commented:
If you run this sql in oracle - it runs... this is how I need it to be.  I'm trying to get this SQL to work with ADO
The ORACLE ODBC driver will also work and may allow you to use the SQL that you need, try it.

NG,
Commented:
Sub test(cnnstringname As String, reportname As String)

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cnn.ConnectionString = cnnstring(cnnstringname)

cnn.Open
Set rs = cnn.Execute("select * from (with x as (select 'x' from dual) select * from x)")
' added an outer select

    Set wks = Nothing
 
    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets("reportname")
    Application.DisplayAlerts = False
    If Not wks Is Nothing Then
        wks.Delete
    End If
Application.DisplayAlerts = True
    On Error GoTo 0

Sheets.Add
ActiveSheet.Name = "Data"
Sheets("Data").Select

Range("A2").CopyFromRecordset rs

cnn.Close
End Sub
Take a look at this link:

http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1142498392

It appears 'WITH' is ORACLE specific and probably not ODBC compliant I would try using a 'SELECT' statement.

NG,
As I said earlier -  

"I've never used a SQL statement that doesn't begin with select..."

It appears that using a SELECT has solved your problem..

NG,

Author

Commented:
The SQL works fine - I use it all the time

Give it a try (using Oracle) - since Microsoft is passing the query through to Oracle - it should work without having to add an outside query.

with x as (select 'x' from dual) select * from x
I think your solution has a modified query based on the exchange of information in the question that you asked as it has been changed to a SELECT statement.

Set rs = cnn.Execute("select * from (with x as (select 'x' from dual) select * from x)")

I'll let the moderator determine the outcome of this question. I don't think closing the Q as self solved is a valid solution.

NG,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial