Connect a Pivot Table to the result of a stored Procedure

  • Thread starter Thread starter Rafael Gomes
  • Start date Start date
R

Rafael Gomes

Hi!

how can i use the result of a stored procedure as the data of a Pivot Table
in Access?

thanks!
Rafael Gomes

I have this SP:

CREATE PROCEDURE STD_Temp

@from as datetime='',
@to as datetime=''
AS
Select * from TD_temp Where begindate >= @from and enddate <= @to
GO

Then in Access i have this code in a Form that is in PivotTable view

Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String
Dim strDate As String

strSQL = "STD_Temp"
Set cmd = New ADODB.Command
' Parameters.Refresh only functions for
' DataShape-Provider with ADO Version 2.6 or later
If CurrentProject.Connection.Version < "2.6" Then
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = CurrentProject.BaseConnectionString
cnn.Open
cmd.ActiveConnection = cnn
Else
cmd.ActiveConnection = CurrentProject.Connection
End If
cmd.CommandText = strSQL
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
For Each par In cmd.Parameters
' Only for input parameters
If par.Direction = adParamInput Then
par.Value = InputBox(par.Name)
End If
Next
Set rst = cmd.Execute

'Heres the PROBLEM
'I have the recordser but how can i connect it to de Pivot Table?

'Before i have this and it work, but i want to place all the query's in SQL
Server, and just call it when needs!
dim st as Stirng
st =" Select * from TD_temp Where begindate >= "& inputbox(begindate) & "
and enddate <= "& inputbox(enddate)
me.recordsource=st

Set rst = Nothing
If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If
 
Back
Top