1 Reply Latest reply: Nov 10, 2009 4:14 PM by 733502 RSS

    Excel 2003 VBA and OO4O

    Kodiak_Seattle
      I have been able to successfully connect to the database and execute a few Select Statements and bring that data back into Excel, but I am having trouble executing a DDL Create Table Statement, here is my code, please help, not sure what is going on. I have the drivers for OO4O installed in XP and "Oracle InProc Server 5.0 Type Library" is checked marked for use.

      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