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