Oracle Stored procedures

[Guest]

Trying to call a Oracle stored procedure that has (3)
parameters[Date],[Date],[String]
Set cn = New ADODB.Connection
cn.Open strConn
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "pkg_allocation_report.runallocationreport_005"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append
cmd.Parameters.Refresh

cmd(1) = strStart
cmd(2) = strEnd
cmd(3) = strDiv

This is based on the MS Help on stored procs in Access 2003
When it gets to the cmd(1) it says thata they are undefined. Is there any MS
documentation on how to work with Oracle through Access?

I have also tried to create a single string like
'strSQL = "BEGIN pkg_allocation_report.runallocationreport_005 (to_date('" &
Format(strStart, "mm/dd/yyyy") & "','mm/dd/yyyy'), "
'strSQL = strSQL & "to_date('" & Format(strEnd, "mm/dd/yyyy") &
"','mm/dd/yyyy'),"
'strSQL = strSQL & strDiv & " )"
'strSQL = strSQL & "; END;"
which doesn't execute but the string will run the procedure when executed
through sqlplus.

 

[Guest]

I found my answer so here goes the whole thing including the need to name the
driver

strConn = "ODBC;DRIVER={Oracle in OraHome92};DSN=XXX;UID=YYY;PWD=ZZZ"

Set cn = New ADODB.Connection
cn.Open strConn
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "pkg_allocation_report.report"
cmd.CommandType = adCmdStoredProc

Set param1 = cmd.CreateParameter("P_PPed_BD", adDate, adParamInput)
Set param2 = cmd.CreateParameter("P_PPed_ED", adDate, adParamInput)
Set param3 = cmd.CreateParameter("P_Div", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = strStart
cmd.Parameters.Append param2
param2.Value = strEnd
cmd.Parameters.Append param3
param3.Value = strDiv

cmd.Execute

 

[Guest]

strConn = "ODBC;DRIVER={Oracle in OraHome92};DSN=xxx;UID=xxx;PWD=xxx"

Set cn = New ADODB.Connection
cn.Open strConn
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "pkg_allocation_report.report"
cmd.CommandType = adCmdStoredProc

Set param1 = cmd.CreateParameter("P_PPed_BD", adDate, adParamInput)
Set param2 = cmd.CreateParameter("P_PPed_ED", adDate, adParamInput)
Set param3 = cmd.CreateParameter("P_Div", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = strStart
cmd.Parameters.Append param2
param2.Value = strEnd
cmd.Parameters.Append param3
param3.Value = strDiv

cmd.Execute