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. 3 4815
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
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: ian justice |
last post by:
Before i post actual code, as i need a speedyish reply.
Can i first ask if anyone knows off the top of their head, if there is
a likely obvious cause to the following problem.
For the moment i've...
|
by: lkrubner |
last post by:
Are there any benchmarks on how much an extra, unneeded VARCHAR, CHAR,
INT, BIGINT, TEXT or MEDIUMTEXT slows down a database call with MySql?
PostGre info would also be useful.
I'm trying to...
|
by: caprice |
last post by:
I'm a MySQL newbie.
As I have to access MySQL database in Pocket PC, I'm developing a
evc++ program to read and retrieve data from MySQL table.
Where can I get some detail information about how...
|
by: Michael |
last post by:
I have a problem forcing files to download. If I select Save the document is
saved with no problems. If I select "Open" the document is empty or I get a
"File not found" error from the application...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a...
|
by: Neil H |
last post by:
Hi All
I am doing a multiple table and field database search, and my problem
lies in the options that a user has. In each field, the user can specify
a specific value or any value. I take each...
|
by: google |
last post by:
Hello everyone,
I am having an issue using the "Multi Select" option in a list box
in MS Access 2003. I am making a form that users can fill out to add
an issue to the database. Each issue can...
|
by: bill salkin |
last post by:
The code below creates a dataset containing a table
called "Customers" from the Northwind database
Later on in my code, after the database connection is
closed, I need to access this dataset to...
|
by: Henning M |
last post by:
Hi all,
I having some problems with Access and selecting records between dates..
When I try this in access, it works fine!!
"Select * from Bilag Where Mdates Between #1/1/2006# And...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |