Temp Table in Stored Procedure with Access Report

[Lauren Quantrell]

I have read the newsgroups and see this is a common issue but I saw no
resolution for it:
I have an Access2K frotn end and SQL Server 2K backend.
In access, I create a temp table using code in a module:
strSQL = "CREATE TABLE #tempTableName ("TempID int NOT NULL IDENTITIY
(1,1) PRIMARY KEY, Field1 int...)
CurrentProject. Connection.Exec ute strSQL,,adCmdTe xt

so far so good...

I create a stored procedure that uses the temp table table above in
the FROM statement to populate a form, where myStoredProcedu reName is
the recordsource for the form.

The recordset is editable.
so far so good...

I double-click on the stored procedure by itself, just to check the
table exists.
so far so good...

I create a report based on the same stored procedure.
I get "Invalid object name '#tempTableName '.

So, I double-click on the stored procedure by itself, to see if the
temp table has evaporated, it opens fine.

For whatever reason, Access dowsn't like to use the temp table in a
report's recordsource, even if it's called in a stored procedure???

I would like to use a temp table rather than a persistent global table
used to hold temp data for these reports. Can this be done?
lq