Use SQL Stored Procedure as source for non-ADP Report? 1

[HowieG]

Can you use a SQL server stored procedure (uses one parameter) as the record source for a MDB (non-ADP) Access Report?

Thanks,
Rob

 

[lameid]

Sort of... What you could do is make an SQL Pass Thru (SPT)query that runs the stored procedure with a parameter specified and make it the recordsource of your report. Finally when you open the report (presumably a button click event) you can modify the SQL property of the SPT Query to change the call (I would just set the entire statemnet).

Dim qry as dao.querydef

Set Qry = currentdb.queries("SPT Query Name")

qry.SQL = "procname (" & Variable & ")"
qry.close
set qry = nothing

docmd.openreport "ReportName"

 

[HowieG]

Great! That works.

(Except it's querydef, not queries)

Thanks!