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