Temporary Stored Procedure & adp's

[Martin Newman]

Using an vba you can create a temporary Stored Procedureusing VBA using the
# character as the first one in the name. Fine.

If I then wish to use DoCmd.OpenStoredProcedure I can't because the
temporary SP is held in tempdb not mu attached db. Is there any way round
this? (I currently do it by "rolling my own" temp SP's in my attached DB but
it would be noce not to have to).

Te reason I am doing this is becuase I have a table containg various lines
of (valid) SQL code the results of wish I wish to dispaly in a "naive" or
"raw" way to the user. I currently use the code to build my temp sp and
display that. Any better solutions?

Martin

 

[Daran Johnson]

Martin,

Are you refering to a temp table? I still don't understand what you are
doing. If you need to build a SQL string then just pass that to SQL Server
and retrieve a recordset.

-Daran

 

[Martin Newman]

I do not wish to open a recordset. Thsi has noithing to do with temporary
tables

if you run docmd.openstoredprocedure storedprocedurename the (first)
recordset from the stored orocedure is opened in a new window in the user
interface just like double clicking on a query in an mdb, say ie that single
line of code opens a table like window presentation of the data without your
having to do any detailed coding. If the SP is an ordinary SP
docmd.openstoredprocedure storedprocedurename works just fine and does
exactly what I want.

However if I create a temporary sp by making the first character of the
name a hash sign (in British parlance) ie "#", then, as BOL says, the temp
SP is stored not in my connected database but in the temdb database and
docmd.openstoredprocedure #tempSPname fails becuse the
docmd.openstoredprocedure looks in the connected db (but temp SP's aren't
stored ther) and not does not look in temdb.

Is there anyway round this?

 

[Sylvain Lafontaine]

Probably no for the same reason that you cannot use temporary tables
directly from Access: temporary tables and SPs are associated with a
connection and a connection is associated with only one database at a time.
The simple fact of switching connections to access another database (in this
case, tempdb) close the previous connection and delete any temporary tables
and SPs.

Instead of creating directly your stored procedures, you should use an
intermediary SP whose function will be to create the temporary SP (maybe via
an EXEC call), call it and then return the result. It should be easy to
pass your creating code as a string parameter to the intermediary SP.

S. L.

 

[Martin Newman]

That is what I thought was probably the case. Ah, well!

Thanks

 

[Martin Newman]

That works!

Probably no for the same reason that you cannot use temporary tables
directly from Access: temporary tables and SPs are associated with a
connection and a connection is associated with only one database at a time.
The simple fact of switching connections to access another database (in this
case, tempdb) close the previous connection and delete any temporary tables
and SPs.

Instead of creating directly your stored procedures, you should use an
intermediary SP whose function will be to create the temporary SP (maybe via
an EXEC call), call it and then return the result. It should be easy to
pass your creating code as a string parameter to the intermediary SP.

S. L.

attached