Re: Howto set Access Recordsource onto a "select" sproc
This is a discussion on Re: Howto set Access Recordsource onto a "select" sproc within the SQL Server forums, part of the Microsoft SQL Server category; --> If you are using a data project (ADP), you simply name the recordsource the name of the stored procedure, ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 02:30 PM
| ||||
| ||||
 Re: Howto set Access Recordsource onto a "select" sproc 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.      |
Linear Mode
