OO4O execute a query against Oracle, VBA Excel 2003

[RompStar]

Any OO4O Oracle users out there ?

Ok, I am making this Excel VBA thing that will execute a series of
queries and bring in Analysis Data, the first step that I need to do
is execute a DDL query to create a Table:

Any variables that hold data are in the code, I just simplified it and
removed them, that all is good, I put watch on them to see what values
they got.

Sub Process_AdHoc_Analysis_Report()

Dim OraDynaset As Object
Dim objSession As Object
Dim objDataBase As Object

Dim sql1 As String

Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDataBase = objSession.OpenDatabase("database", schema & "/" &
password, 0&)

sql1 = "create table " & tablename & "_1" & " as select * from table
where cmpgn_id='" & campaignid & "';"

objDataBase.ExecuteSQL (sql1) <-- here is the error "Run-time error
'440': Automation error

End Sub

Any idea how to execute a DDL query ? All I need is to just execute
this query, I don't need nothing returned to deal with it like with a
Select Statement. One would figure this was easy, not sure, I read the
OO4O Wiki and not very well documented.

 

[Tim Williams]

Have you tried using ADO instead of OO4O ?
There will be many more examples when you get stuck...

anyway...

If you debug.print the SQL, can you run it successfully in your favorite
query tool ?

Tim

 

[RompStar]

Thanks, but I won't want to run the query in Toad/Or SQL*Plus, I
wanted to run it from Excel, just need to figure out how.

Oracle writes crappy instructions, the worse ever!

 

[RompStar]

I found that Oracle maintains Forums and there is one for OO4O and
Wizards:

Forum Home » Windows and .NET » OO4O and Wizards


Just to go Oracle.com

That's what I will try next.

 

[Tim Williams]

I wasn't suggesting you not use Excel, just that you verify the SQL is
being created correctly and that it runs under the account you're using.

Tim



Thanks, but I won't want to run the query in Toad/Or SQL*Plus, I
wanted to run it from Excel, just need to figure out how.

Oracle writes crappy instructions, the worse ever!

 

[RompStar]

Ohhh yes, I can execute the SQL with no problem using TOAD on Oracle
10G, but for some reason it won't using OO4O, every example that I
have read in their awesome documentation (not), is showing in create
an empty Table and then doing inserts, somehow I can't find a single
example of:

Create Table TableName As (Select * from Table where blablabla)

I mean, this is a DDL statement or not ?

It reads that it supports DDL, but like, not sure....