471,594 Members | 1,286 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,594 software developers and data experts.

Howto set Access Recordsource onto a "select" sproc

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.

Sep 29 '05 #1
3 4705
You'll probably get a better response in one of the
microsoft.public.access.* groups - how to present data in an Access
report isn't really an MSSQL question.

Simon

Sep 29 '05 #2
Stu
Look at pass-through queries in Access; they'll run the stored
procedure on SQL Server. However, you'll have to use VBA to modify the
paramaters (by actually modifiying the text of the pass-through query).

Stu

Sep 30 '05 #3
If you are using a data project (ADP), you simply name the recordsource
the name of the stored procedure, either hard coded in the report
designer or in code at runtime.

If you are using an MDB, Stu's suggestion of pass-through query objects
is the usual method.

Radu wrote:
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.


Sep 30 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

23 posts views Thread by ian justice | last post: by
2 posts views Thread by caprice | last post: by
1 post views Thread by Neil H | last post: by
4 posts views Thread by bill salkin | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.