James said:
Could you please tell me the best way to execute an sql stored procedure
from
access and insert the returned records into a local access table. the
procedure takes one parameter, an as of date. Can I do it with a
passthrough
query, can a passtrough query be an append query? or should I use dao (or
ado) thanks
Without actually trying it, I would guess you could base an append query on
a pass-through query that executes your stored procedure. The parameter
presents a problem, though. I believe you'd have to modify the SQL of the
pass-through query in order to specify the parameter as a literal value.
Only after doing that would you execute the append query. That means making
a design change to the stored pass-through query every time you execute it,
which is not a wonderful thing to be doing.
As an alternative approach, you could use an ADO Command object to execute
the stored procedure and return a recordset, and then loop through that
recordset and add each record to your local table. You'd probably have use
a second recordset, opened on the local table, to do that. My guess is that
this would execute less efficiently than runnng an append query on a
pass-through query, as described above, but it wouldn't require making
design changes at run-time.
