467,922 Members | 1,361 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,922 developers. It's quick & easy.

Temp Table in Stored Procedure with Access Report

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
Nov 12 '05 #1
  • viewed: 3085
Share:
1 Reply
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by Jon Ole Hedne | last post: by
1 post views Thread by hotice3100 | last post: by
4 posts views Thread by joshd | last post: by
2 posts views Thread by kizmar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.