Problem in executing query

[ashishprem]

Hi,
I want to connect to a Oracle database located on remote machine and
execute the insert query from the macro but i am getting error related
to session object. I am sending the code, please help me out..

CODE
Dim oconn As ADODB.Connection
Set oconn = New Connection
Dim cmd As ADODB.Command
Set cmd = New Command

oconn.ConnectionString = "Driver={Oracle ODBC Driver};" & _
"Dbq=alf;" & _
"Uid=system;" & _
"Pwd=manager1"

cmd.ActiveConnection = oconn
cmd.CommandText = "insert into zzz_ashish values('YYY','22')"
cmd.Execute

 

[Guest]

http://www.oracle.com/technology/docs/tech/windows/odbc/htdocs/817help/start.htm

 

[ashishprem]

Hi,
I have found a code to connect to Oracle using macro. Dint understan
much in the code but its working fine.
Code :-

Public objSession As Object
Public objDataBase As Object
Dim strSQL As String

Sub final()
ConnectToOracle Worksheets("sheet2")
End Sub

Sub ConnectToOracle(ws1 As Worksheet)
'Create a reference to the OO4O dll
Set objSession = CreateObject("OracleInProcServer.XOraSession")
'Create a reference to my database

Set objDataBase = objSession.OpenDatabase("AshishDB"
"username/password", 0&)

For i = 2 To ws1.UsedRange.Rows.Count
strSQL = "insert into zzz_ashish values('" + ws1.Cells(i, 1) + "','"
CStr(ws1.Cells(i, 2)) + "')"
objDataBase.ExecuteSQL (strSQL)
Next i
End Sub

Regards,
Ashis