Calling Oracle stored procedure from MS Access

[acpt]

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

[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?

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

[acpt]

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

[techgnome]

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.

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