SQL Server temp tables via MS Access

Question

Well I've been using #temp tables in standard T-SQL coding for years and thought I understood them. However, I've been dragged into a project based in MS Access, utilizing pass-through queries, and found something that has really got me puzzled. Though maybe it's the inner workings of Access that has me fooled !?

Here we go : Under normal usage, I understand the if I create a temp table in a Sproc, it's scope ends with the end of the SProc, and is dropped by default.

In the Access example, I found it was possible to do this in one Query:

select top(10) * into #myTemp from dbo.myTable

And then this in second separate query:

select * from #myTemp

How is this possible ? If a temp table dies with the current session, does this mean that Access keeps a single session open, and uses that session for all Queries executed ?

Or has my fundamental understanding of scope been wrong all this time ?

Hope someone out there can help clarify what is occurring under the hood !?

Many Thanks

Answer 1

I found this answer of a kind of similar question:

Temp table is stored in tempdb until the connection is dropped (or in the case of a global temp tables when the last connection using it is dropped). You can also (and it is a good proctice to do so) manually drop the table when you are finished using it with a drop table statement.

I hope this helps out.