Setting stored procedure as a recordsource

[Hellgofi]

I have an adp Access file using Sql 2005 as a backend. I have some forms in that project which has stored procedures as a record source. However I dont want to return all of the data when forms opened. My stored procedures used as a record sources are mainly selecting some records from multiple tables with WHERE clause.

I remember there was a NODATA property in foxpro which tells the FP to run sp but return no data when opening the form. is there anything similar I can do the same thing in Access ADP+ SQL 2005 environment? ie, form recordset will be a stored procedure but form will be opened with no data,after user is selected some filter criteria ( which will be transferred to sp as a paramater) and then form will be filled with the data matching the criteria?

regards

 

[Banana]

A MVP around here, datAdrenaline, mentioned the idea that you could bind the form's recordsource to something like that:

SELECT a, b, c FROM foo WHERE 1=0

This will of course return no data at all. You can then dynamically set the recordsource to whatever criteria you need to use. A benefit of doing this is that you still get to use a bound form and design it just like any bound form but you aren't taxing server with extraneous requests you didn't want.

BTW, I should note that if you do use SP, which I assume will use a passthrough query, you can't really use bound form for editing and/or inserting new data. Just so you know.

HTH.

 

[Hellgofi]

Thanks Banana,
Simple yet effective trick.