Calling Oracle stored procedure from MS Access
I am trying to execute a stored procedure which accepts an input parameter and returns a resultset using ref cursor, from an MS Access forrm using the below connection string:
Public conn1= "Driver={Microsoft ODBC for Oracle};CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.com)(PORT=1000))(CONNECT_DA TA=(SID=xe))); uid=mylogin;pwd=pwd;"
Below is the generic function in the VBA code which will accept a sql string (can be with one or more or even without any parameters).
' without parameter
strsql="CallMyProc"
'with parameter
'strsql="CallMyProc('name')"
public function call OracProc(strsql)
Dim db As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set db = New ADODB.Connection
db.ConnectionString = conn1
db.Open
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = db
'cmd.Properties("PLSQLRSet") = True
.CommandType = adCmdStoredProc
.CommandText = strsql
Set rs = cmd.Execute()
End With
If Not IsObject(rs) Then
rs.MoveFirst
OracProc= Trim(rs.Fields(0))
End If
end function
The above code does not work. Its giving an error.
Can someone please provide a correct solution?
i do not want to use create parameter as the function in the VBA code will be generic
Re: Calling Oracle function from MS Access
I don't see how Access plays into this. I don't see any place where you connected to Access... are you saying that you're running this from inside an Access database? Or is this from VB6 code?
Quote:
The above function does not work. Its giving an error.
Well, that's nice. I can only guess that the error has something to do with eels. Your best bet would be to remove the eels from your hovercraft. There is a guide in my signature that explains how to get rid of those pesky eels.
-tg
Re: Calling Oracle function from MS Access
Quote:
Originally Posted by
techgnome
I don't see how Access plays into this. I don't see any place where you connected to Access... are you saying that you're running this from inside an Access database? Or is this from VB6 code?
Well, that's nice. I can only guess that the error has something to do with eels. Your best bet would be to remove the eels from your hovercraft. There is a guide in my signature that explains how to get rid of those pesky eels.
-tg
made corrections to my post. Please read again
Re: Calling Oracle function from MS Access
Quote:
Originally Posted by
acpt
made corrections to my post. Please read again
I'm not going to make corrections to my post, but rather I'll just repeat myself.
Quote:
The above function does not work. Its giving an error.
Well, that's nice. I can only guess that the error has something to do with eels. Your best bet would be to remove the eels from your hovercraft. There is a guide in my signature that explains how to get rid of those pesky eels.
-tg
