Returning results from Stored Procedures

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

I know I have seen posts similar to this, but I cannot seem to find them.
Please accept my apology in advance

I have an access application linked to a SQlLbackend. Some of my queries
take forever to run, therefore I would like to use the power of SQL server
to run the query as a stored procedure and just return the recordset as the
datasource for a report.

I have code to pass parameters to a stored procedure and run it, but how do
I get the data back as a recordset that I can work with? (For instance, you
can set up an SQL view as a table in access, but I am not sure how to return
the recordset and assign it as the datasource for a report.

THanks

- joe
 
You can create a pass-through query that executes the stored procedure.
However, you can't use parameters with pass-through queries. What you need
to do is write a string to call the stored procedure with the actual values
you're trying to pass, update the SQL of the pass-through query to use that
new string, and then use the updated query.
 
Doug,

can you provide a simple example of passing a parameter to a SQL stored
procedure?

THanks
 
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set dbCurr = CurrentDb()

Set qdfCurr = dbCurr.QueryDefs("MyPassThrough")
strSQL = "Exec Add_Organization_Status_Type "
strSQL = strSQL & "@Name='" & NewData & "'"
qdfCurr.SQL = strSQL
 
Thanks Doug.

The only question I have left is how do I make this SQL statement the data
source for a report?
 
You dont't make the SQL statement the data source. You make the query
("MyPassThrough" in the example) the data source.

Of course, this means that you need some code to run before you open the
report to set the SQL: you can't just click on the report, as it'll have the
values from the last time you ran the query.
 
Back
Top