Attempting to call Oracle stored procedure with vba code??

[notageek7]

I'm totally stumped....when trying to call my oracle stored procedure within my vba code, example below. I've gotten this code example below and not having any luck with it. Does anybode see any problems???? In general I'm not finding much info out there regarding oracle stored procedures and vba....guess it's not a suprise...any ideas?



Function CallSProcwithoutparam() As Boolean

Dim db As Database
Dim LSProc As QueryDef

On Error GoTo Err_Execute

Set db = CurrentDb()

Set LSProc = db.CreateQueryDef("")

'Use {Microsoft ODBC for Oracle} ODBC connection
LSProc.Connect = "Provider=OraOLEDB.Oracle;Data Source=xxx;User Id=xxx;Password=xxx"
LSProc.SQL = "BEGIN DeleteHoldCodes; END;"
LSProc.ReturnsRecords = False
LSProc.ODBCTimeout = 0

LSProc.Execute

Set LSProc = Nothing

CallSProcwithoutparam = True

Exit Function

Err_Execute:
MsgBox "The call to the Oracle stored procedure failed."
CallSProcwithoutparam = False

End Function

 

[cmmrfrds]

Another option is to use ADO OLEDB instead of the ODBC connection. The Command object in ADO is ideal for handling stored procedures. I don't have an example of Oracle, but here is an example with SQL Server. You would need to replace with the Oracle OLEDB provider.


Public Function testAccessZ()

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.Recordset, connString As String, bdate As String
Dim cmd As New ADODB.Command, parm1 As ADODB.Parameter
Set rs = New ADODB.Recordset
'--- connect to sql server
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

Set parm1 = cmd.CreateParameter(, adInteger, adParamInput)
cmd.Parameters.Append parm1
parm1.Value = "10255"
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic

Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.CustOrdersDetail"
cmd.ActiveConnection = cn
''Set rs = cmd.Execute
rs.Open cmd, , adOpenStatic, adLockOptimistic
Debug.Print "value = "; rs(0)
Debug.Print "record count = "; rs.RecordCount

cn.Close
Set cn = Nothing
Set cmd = Nothing

End Function

Connection Strings can be found at.

http://www.connectionstrings.com/

http://www.carlprothman.net/Default.aspx?tabid=81