Savvas,

Here's an example that should point you in the right direction.

You need to add a project reference to MS ActiveX Data Objects

'back in the database DO THE FOLLOWING
CREATE PROC spTemp(@Table1 nvarchar(50), @Table2 nvarchar(50))
as
-- example of a dynamic SQL sp returning multiple recordsets
SET NOCOUNT ON
EXEC('SELECT * FROM ' + @Table1)
EXEC('SELECT * FROM ' + @Table2)
SET NOCOUNT OFF
GO

Sub Test()
Dim vParams As Variant
Dim vValues As Variant
Dim rsReturn As ADODB.Recordset
vParams = Array("Table1", "Table2")
vValues = Array("TableName1", "TableName2")
'change DBNAME to whatever DB you created the above proc in
ReturnRSFromSP "spTemp", vParams, vValues, "DBNAME"
End Sub

Public Sub ReturnRSFromSP(strSP As String, _
   vParams As Variant, _
   vValues As Variant, _
   strCatalog As String)

Dim cnSP As ADODB.Connection
Dim cmdSP As ADODB.Command
Dim lCounter As Long
Dim strItem As String
Dim lIndex As Long
Dim rsReturn As ADODB.Recordset

Set cnSP = New ADODB.Connection

'you will have to amend this for Oracle
cnSP.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
   "Persist Security Info=False;Initial Catalog=" & strCatalog & _
   ";Data Source=" & FILLTHISIN 'add your  data source here

cnSP.Open

'create the command object
Set cmdSP = New ADODB.Command
cmdSP.ActiveConnection = cnSP
cmdSP.CommandText = strSP
cmdSP.CommandType = adCmdStoredProc
cmdSP.Parameters.Refresh

lCounter = 0

For lCounter = 1 To cmdSP.Parameters.Count - 1

   strItem = cmdSP.Parameters(lCounter).Name

   For lIndex = 0 To UBound(vParams)

       If "@" & vParams(lIndex) = strItem Then

           cmdSP.Parameters(lCounter).Value = vValues(lIndex)
           Exit For

       End If

   Next

Next

'*****************************************
'use this bit if trying to return results as a recordset
'delete it otherwise
'*****************************************

'create the recordset object
Set rsReturn = New ADODB.Recordset

With rsReturn

   .CursorLocation = adUseClient
   .CursorType = adOpenStatic
   .LockType = adLockBatchOptimistic

   'execute the SP returning the result into a recordset
   .Open cmdSP

End With

Do Until rsReturn Is Nothing

   If rsReturn.State = adStateOpen Then

       DumpRecordset rsReturn

   End If

   Set rsReturn = rsReturn.NextRecordset

Loop
'*********************end of section

'*****************************************
'use this bit if just trying to run a stored proc
'delete it otherwise
'*****************************************
'execute the SP
oCmd.Execute

'*********************end of section

Set cmdSP = Nothing

If cnSP.State = adStateOpen Then
   cnSP.Close
End If
Set cnSP = Nothing
Set rsReturn = Nothing
End Sub

Sub DumpRecordset(rsName As ADODB.Recordset, Optional lstartpos As Long)
Dim W As Workbook
Dim nField As Integer
Dim lRowPos As Long

Set W = ActiveWorkbook

Workbooks.Add

With rsName

   For nField = 1 To .Fields.Count

       Cells(1, nField).Value = .Fields(nField - 1).Name

   Next nField

   If .RecordCount = 0 Then Exit Sub
   .MoveFirst

   If Not IsEmpty(lstartpos) Then .Move lstartpos

End With

Cells(2, 1).CopyFromRecordset rsName
End Sub

Robin Hammond
www.enhanceddatasystems.com