Is "Table/View/StoredProc" only available in ADPs? (Combo box recordsets)

[annie]

Hi there,

I am using an Access2K database on its own (i.e not in a project).

I want to make the recordset of a combobox be a stored procedure on
our SQL Server.

The problem is, I cannot set the "rowsource" to "EXEC <my_sp>" as I
there is not the option to select "Table/View/StoredProc" from the
rowsourcetype list. All I have is the usual "Table/Query, Value List,
and Field List". I went and opened a blank ADP and saw that one of the
options was "Table/View/StoredProc".

Other than iterating through a recordset and adding the values
delimited with a ";" I can't think of how to do this. I do not want to
use the Value List option as it can be slow.

If anyone has come across this problem before, I'd love to hear your
solution. Please keep in mind that unless you have an Access Project
file, you cannot use "EXEC sp_name" as the rowsource.

Thanks!

Annie

 

[Douglas J. Steele]

Create a pass-through query, and put the EXEC <my_sp> as the SQL for that
query.

 

[Dirk Goldgar]

Hi there,

I am using an Access2K database on its own (i.e not in a project).

I want to make the recordset of a combobox be a stored procedure on
our SQL Server.

The problem is, I cannot set the "rowsource" to "EXEC <my_sp>" as I
there is not the option to select "Table/View/StoredProc" from the
rowsourcetype list. All I have is the usual "Table/Query, Value List,
and Field List". I went and opened a blank ADP and saw that one of the
options was "Table/View/StoredProc".

Other than iterating through a recordset and adding the values
delimited with a ";" I can't think of how to do this. I do not want to
use the Value List option as it can be slow.

If anyone has come across this problem before, I'd love to hear your
solution. Please keep in mind that unless you have an Access Project
file, you cannot use "EXEC sp_name" as the rowsource.

Thanks!

Annie

How about creating a stored pass-through query that executes your stored
procedure. Then you could set the combo box's RowSourceType to
"Table/Query" and its RowSource to the name of the pass-through query.

 

[annie]

Thanks Dirk and Douglas - this is an excellent solution to my problem.

However, now I don't know how to pass a form's control value (e.g text
box, whatever) to the SP if I call it from within a pass-through
query.

Can I still pass values this way?

 

[Douglas J. Steele]

You change the SQL property of the Query to reflect what you want to pass.

This means you must do it using VBA: you can't just refer to a control on a
form, for instance, in the SQL, like you can do for non-pass through
queries.