Posted by Radu on 09/29/05 03:23

Hi. I have lots of processing to do on the server - from the client
(Access) I call a sproc which returns a recordset (the sproc is
essentially a big "select"). With the obtained data , I need to
generate a report. How do I set the Recordsource of the report to the
result of the select sproc ?

I have tried the following, but it does not work.

Private Sub cmdReport_Click()

On Error GoTo cmdReport_ClickError

Dim objCmd As ADODB.Command
Dim intOpenObjects As Integer
Dim rsTemp As ADODB.Recordset

Set objCmd = New ADODB.Command
intOpenObjects = 1

objCmd.ActiveConnection = m_objConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "_TestReport"

Set rsTemp = objCmd.Execute
intOpenObjects = 2

Dim rpt As Report
DoCmd.OpenReport "TestReport", acViewDesign
Set rpt = Reports("TestReport")
Set rpt.RecordSource = rsTemp
DoCmd.Close acReport, "TestReport", acSaveYes
Set rpt = Nothing
DoCmd.OpenReport "TestReport", acViewPreview

DoCmd.OpenReport "TestReport", acViewPreview
DoCmd.SelectObject acReport, "TestReport"
DoCmd.Maximize

cmdReport_ClickExit:
If intOpenObjects = 2 Then
rsTemp.Close
Set rsTemp = Nothing
intOpenObjects = 1
End If
If intOpenObjects = 1 Then
Set objCmd = Nothing
intOpenObjects = 0
End If

Exit Sub

cmdReport_ClickError:
MsgBox Err.Description, vbCritical, Me.Name
Resume cmdReport_ClickExit
End Sub

How can I do that, please ? Would it maybe be better to change the
"Select" sproc into an Insert sproc, as in "SELECT... INTO TEMP", in
order to create a temp table on the server, then in Access link to that
table and set the recordsource onto the linked table ?

Please help. Thank you very much, Alex.

• Next in forum: NAMED SET / PIVOT TABLE !! HELP ME
• Prev in forum: HOW TO PLACE NAMED SET INTO A PIVOT TABLE !!!!
• Thread view: Howto set Access Recordsource onto a "select" sproc

[Reply to this message]