[SOLVED] Assign stored procedure to a report.recordset in Microsoft Access

Assign stored procedure to a report.recordset in Microsoft Access

I've heard you can only do this is adp, I'm connecting directly to SQL Server, and would like to assign a stored procedure to a record and I get the error that can be done only in adp file?

I'm not linked to SQL Server, I connect directly to it with ODBC driver.

Is there any way I can assign this stored procedure to the report?

Thanks..

Solution

All you have to do is create a query in Access, but mark it (change it) to a pass-thougthh query.

So, the pt query can be

Exec MyStoredProcedure

Save the query. Now just use this query as the source for the report. You don't really need to write any code here. Now of course if you need to pass a value or some kind of parameter?

Well, then you can use this code:

Let's assume that the stored procedure returns data for a given invoice number.

So, your code would look like this:

dim strInvoiceNumber   as string
strInvoiceNumber = InputBox("Enter invoice number")

currentdb.QueryDefs("MyPass").sql = "EXEC StorProc " & strInvoiceNumber

' code here follows to open report
 docmd.OpenReport "MyInoice",acViewPreview

So, you can create a pass-thorough query, and type into the query any exec or command that would run/call the stored procedure. And as noted, if there are no parameters, then you don't need to write any code at all - just create the pass-though query, and use that as the source for the report.