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

[Tom Ogilvy]

http://www.oracle.com/technology/doc...help/start.htm

--
Regards,
Tom Ogilvy


"ashishprem" wrote:

>
> 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
>
>
> --
> ashishprem
> ------------------------------------------------------------------------
> ashishprem's Profile: http://www.excelforum.com/member.php...o&userid=31485
> View this thread: http://www.excelforum.com/showthread...hreadid=545793
>
>

[ashishprem]

Hi,
I have found a code to connect to Oracle using macro. Dint understand 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,
Ashish