Insert query in Oracle database using microsoft ODBC for oracle

[Guest]

Does anybody know how to run a insert query through excel vb using microsoft ODBC for oracle

I made de code below but it dosen't works

Dim GDW As Object ' Database Objec
Dim LOCAL_DW As Object ' Database Objec
Dim gdbEngine As Object ' DB Engin
Dim gwsp As Object ' Workspac
Dim sql_data As Objec
Dim Const idbOPENDYNASET =
Dim Const idbOPENSNAPSHOT =
Dim Const idbSQLPASSTHROUGH = 6
Dim Const idbSQLINCONSISTENT = 1
Dim Const ixlMANUAL = -413

'create work spac

If (Left(application.Version, 1) = 7) The
Set gdbEngine = CreateObject("DAO.DBEngine") ' Excel 9
Els
If (Left(application.Version, 1) = 8) The
Set gdbEngine = CreateObject("DAO.DBEngine.35") ' Excel 9
Els
Set gdbEngine = CreateObject("DAO.DBEngine.36") ' Excel 200
End I
End I

'define worksapc
Set gwsp = gdbEngine.Workspaces(0

'connect databas
' The ODBC used here is the microsoft ODBC for oracl

Set LOCAL_DW = gwsp.OpenDatabase("", False, False, "ODBC;DSN=" & Local_DW_DSN & ";UID=" & Local_DW_user & ";PWD=" & Local_DW_user_psw & ";SERVER=" & Local_DW_Server & ""

'set insert sq

sql = "insert into bi_fin_stg_valor_calc_gdw Values ('200310', '00593', 'K1000',-914099.59,0,0,0, SYSDATE , 'boasrm')

run sql statmen

Set sql_data = LOCAL_DW.OpenRecordset(SQL, idbOPENDYNASET, idbSQLPASSTHROUGH)

 

[TC]

Action queries (like INSERT) don't return a recordset. So try
LOCAL_DW.Execute(...), instead of LOCAL_DW.OpenRecordset(...).

Also, please name the INSERT fields explicitly! What if your Oracle DBA does
a re-org, & the order of the fieldnames changes in that table?

HTH,
TC

Does anybody know how to run a insert query through excel vb using microsoft ODBC for oracle

I made de code below but it dosen't works

Dim GDW As Object ' Database Object
Dim LOCAL_DW As Object ' Database Object
Dim gdbEngine As Object ' DB Engine
Dim gwsp As Object ' Workspace
Dim sql_data As Object
Dim Const idbOPENDYNASET = 2
Dim Const idbOPENSNAPSHOT = 4
Dim Const idbSQLPASSTHROUGH = 64
Dim Const idbSQLINCONSISTENT = 16
Dim Const ixlMANUAL = -4135


'create work space

If (Left(application.Version, 1) = 7) Then
Set gdbEngine = CreateObject("DAO.DBEngine") ' Excel 95
Else
If (Left(application.Version, 1) = 8) Then
Set gdbEngine = CreateObject("DAO.DBEngine.35") ' Excel 97
Else
Set gdbEngine = CreateObject("DAO.DBEngine.36") ' Excel 2000
End If
End If

'define worksapce
Set gwsp = gdbEngine.Workspaces(0)

'connect database
' The ODBC used here is the microsoft ODBC for oracle

Set LOCAL_DW = gwsp.OpenDatabase("", False, False, "ODBC;DSN=" &

Local_DW_DSN & ";UID=" & Local_DW_user & ";PWD=" & Local_DW_user_psw &
";SERVER=" & Local_DW_Server & "")

'set insert sql

sql = "insert into bi_fin_stg_valor_calc_gdw Values ('200310', '00593',

'K1000',-914099.59,0,0,0, SYSDATE , 'boasrm')"

run sql statment

Set sql_data = LOCAL_DW.OpenRecordset(SQL, idbOPENDYNASET,

idbSQLPASSTHROUGH)