Seeing SQL 7.0 ##temp tables in Access 97

The problem is we need local tables in Access for SubReports (can't use store procedures as record source for this), therefore trying to create temp table and link to Access for each report instance.


When we create the ## type table in tempdb this cannot be linked from Access (cannot be seen via DSN).
The only other option we can find is doing
CREATE TABLE tempdb.dbo.[tablename]
but this requires the user to have admin permissions and therefore be 'dbo' on tempdb and not 'guest'.

Any clues?

Palmy

Have you tried to create the ##temptable in another database other than tempdb, perhaps the same DB as where the original data is stored.

##temp tables are ALWAYS created in tempdb...that's what makes them temporary tables.

In SQL 7.0, you can setup an access database as a LINKed server (see BOL). If you have a table in your access DB with the required structure of the output you need, it may be possible to insert directly into the Access table via Linked server. Of course if you're dealing in a lot of rows, this won't be a practical solution ...


------------
Paul Connors at 5/2/01 8:33:02 AM

Have you tried to create the ##temptable in another database other than tempdb, perhaps the same DB as where the original data is stored.

Ananth is dead right. Sort it out Palmy.

------------
Ananth at 5/2/01 8:53:08 AM

##temp tables are ALWAYS created in tempdb...that's what makes them temporary tables.

In SQL 7.0, you can setup an access database as a LINKed server (see BOL). If you have a table in your access DB with the required structure of the output you need, it may be possible to insert directly into the Access table via Linked server. Of course if you're dealing in a lot of rows, this won't be a practical solution ...


------------
Paul Connors at 5/2/01 8:33:02 AM

Have you tried to create the ##temptable in another database other than tempdb, perhaps the same DB as where the original data is stored.

Yes ANANTH - aren't you clever, that knowledge was assumed - I was referring to temporary work tables created in another database (Not with the ## prefix)
because as you were so quick to point out, these are ALWAYS created in tempdb


------------
Ananth at 5/2/01 8:53:08 AM

##temp tables are ALWAYS created in tempdb...that's what makes them temporary tables.

In SQL 7.0, you can setup an access database as a LINKed server (see BOL). If you have a table in your access DB with the required structure of the output you need, it may be possible to insert directly into the Access table via Linked server. Of course if you're dealing in a lot of rows, this won't be a practical solution ...


------------
Paul Connors at 5/2/01 8:33:02 AM

Have you tried to create the ##temptable in another database other than tempdb, perhaps the same DB as where the original data is stored.