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

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

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

--
ashishpre
-----------------------------------------------------------------------
ashishprem's Profile: http://www.excelforum.com/member.php...fo&userid=3148
View this thread: http://www.excelforum.com/showthread.php?threadid=54579