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.Execute strSQL,,adCmdText

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 myStoredProcedureName 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

[Pavel Romashkin]

I may be off course here, but is the temp table linked to your Access
project? Is your report trying to use a local table which exists on the
server, not locally?
If you are avoiding using linked tables, you should probably establish
the record source for the report in code when it is being opened, using
your server connection.
Pavel

Lauren Quantrell wrote:

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.Execute strSQL,,adCmdText

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 myStoredProcedureName 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