Can't create persistent ## tables with ADP call to a stored procedure(!?)

[Marchand]

Environment is SQL Server 2008: Access 2007.
I have a stored procedure which creates a few global temporary tables.
When executed within SQL Server Management Studio it works just fine.
The tables are created and accessible within the tempdp. However, when
I execute the same stored procedure within an ADP file (yeah, I know
it should be an ACCDB but it will be used by both Access 2003 and
Access 2007 users), the call executes just fine, but the tables never
appear within tempdb. The table names are passed as parameters and are
unique (incorporating recursive calls to a GUID function). What's even
more interesting is that I can return the @@ROWCOUNT from within the
stored procedure to the ADP file and it reports that rows do exist.
But the tables don't.

 

[Marchand]

Never mind! It was a security issue, now fixed.

= M =

 

[Sylvain Lafontaine]

Environment is SQL Server 2008: Access 2007.
I have a stored procedure which creates a few global temporary tables.
When executed within SQL Server Management Studio it works just fine.
The tables are created and accessible within the tempdp. However, when
I execute the same stored procedure within an ADP file (yeah, I know
it should be an ACCDB but it will be used by both Access 2003 and
Access 2007 users)

ADP and ACCDB files are two very different types of file for different types
of work and the version of Access has nothing to do with ADP. Even if you
were to use Access 2007 exclusively, you couldn't replace the ADP project
with an ACCDB database file to do the same thing.

, the call executes just fine, but the
tables never
appear within tempdb. The table names are passed as parameters and are
unique (incorporating recursive calls to a GUID function). What's even
more interesting is that I can return the @@ROWCOUNT from within the
stored procedure to the ADP file and it reports that rows do exist.
But the tables don't.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)