SQL Stored Procedure as Forms Recordsource

[terbs]

I know there are existing topics on this, but im not sure if my scenario is the same.

My entire Access App uses an SQL ODBC link for all its recordsources etc. But one Select Statement, (which runs fine as a SQL stored procedure) throws an error when I try run it from Access.

Does referencing a SP from Access just retrieve the SQL Statement, or does it return a recordset?

Does anyone have some sample code to to perform this task? I need to pass in a parameter to get the SP to work.

cheers

 

[terbs]

no one can help?

 

[Banana]

Well, the most simplest and frills-free way to do this would be to create a new pass-through query that calls the stored procedure, and use a parameter to pass along the variable required for the stored procedure. No coding necessary.

 

[terbs]

OK the SQL does work if I hard code the parameter in. Is there anyway to have dynamic parameters for pass through queries?

 

[Banana]

Add a parameter to the pass through query. This is off the top of head, but if you right click on SQL view, you should be able to select "Parameters" from the context menu, then add a variable type.

Alternatively, add this line prior to the SQL:

PARAMETER variablename datatype
(SQL starts here...)

 

[terbs]

Thanks for you help Banana, but after scouring google for hours I finally found a solution, albeit a little messy. Heres the steps for anyone needing something similar in future.

1. Create you stored procedure in SQL e.g "sproc_MySproc"

2. Create a Pass-Through Query with this as the SQL. Save as "sproc_MySproc"

EXEC sproc_MySproc

3. Use this function to set Recordsource of you Report/Form.

Dim qdf As QueryDef
Dim frm As Form
' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Form_'YOUR FORM NAME' 
Open QueryDef object.
Set qdf = dbsReport.QueryDefs("sproc_MySproc")
Dim rstReport As DAO.Recordset
qdf.SQL = "EXEC sproc_mySproc " & 'YOUR PARAM HERE'
qdf.ReturnsRecords = True
 '  Open Recordset object.
Set rstReport = qdf.OpenRecordset()
rstReport.MoveFirst
Form_'YOUR FORM NAME' .RecordSource = "sproc_mySproc"

This actually saves the Pass-Through Queries SQL each time with the last parameter used.