Using Excel to call Oracle Stored procedure.

[pdcjlw1]

I am trying to get Excel/MSQuery to call an Oracle stored procedure and
return a record set. Every time I try it I get an access violation error. Is
there a way to call an Oracle stored procedure (or function) and return a
record set to excel?
Thanks.

 

[Patrick Molloy]

in vba i use the following to read from SQL server
change the provider "MSDASQL" to whatever is appropriate for Oracle
also change servername, databasename ans stored_proc_name appropriately

Dim rst As ADODB.Recordset
Dim db As Connection
Dim SQL As String
Dim i As Long
Set db = New Connection

With db
.CursorLocation = adUseClient
.Open "PROVIDER=MSDASQL;driver={SQL
Server};server=SERVERNAME=;pwd=;database=DATABASENAME;"
End With

Set rst = New Recordset
SQL = "STORED_PROC_NAME"
rst.Open SQL, db, adOpenStatic, adLockOptimistic

range("B5").CopyFromRecordset rst