Accessing SQL Server temp table for Access Project

Hi,
I am trying to use a temp table as the record source for a listbox on a access project form.
I have no problem creating the temp table and inserting data to it, but I can't access it from MS Access (every thing works on Query Analyzer).
I know that local temp tables are deleted when the connection is lost, but I'm on the same form that create the temp table, why can't I access the data??
(It works when I use global temp tables like ##Test, but I can't use global temp tabels for my application)

Here is my code, any idea what the problem might be?

Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset

Dim SQL As String

SQL = "exec sp_dropMListSource "
SQL = SQL & "SELECT distinct dbo.tblContact.ContactID,ISNULL(dbo.tblContact.Fir stName, '') "
SQL = SQL & "+ ' ' + ISNULL(dbo.tblContact.LastName, '') AS [Contact Name]"
SQL = SQL & "INTO #MListSource "
SQL = SQL & " FROM dbo.tblContact INNER JOIN dbo.tblProperty ON "
SQL = SQL & "dbo.tblContact.ContactID = dbo.tblProperty.ContactID "
SQL = SQL & "WHERE dbo.tblProperty.Zip in (" & Me!zips & ")Order By [Contact Name]"

Rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Me.MListSource.RowSource = "Select ContactID, [Contact Name] From #MListSource"

Set Rs = Nothing>> I know that local temp tables are deleted when the connection is lost
Nope - it is dropped when the batch completes.
In your case the batch is the create statement.|||nigelrivett Not exactly try the following, works just fine.

create table #Tmp(f1 int)
go
insert into #Tmp values(1)
go
select * from #Tmp
go

Sia Okay, this is just a guess but what happens if you run profiler as you step through your code? I am thinking that the temp table is alive until you execute the "Set Rs = Nothing". I think this implicitly (sp?) closes the connection and as a result your temp table goes away.

Here is a blerb from BOL:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Can you run your code in debug and check this out?|||I think that Nigel is right. Once the batch completes the local temp table is dropped. If you want to return the entries from your temp it would be better to put all the SQL statements in a SP that returns your recordset (use ADODB command object)

Originally posted by Paul Young
nigelrivett Not exactly try the following, works just fine.

create table #Tmp(f1 int)
go
insert into #Tmp values(1)
go
select * from #Tmp
go

Sia Okay, this is just a guess but what happens if you run profiler as you step through your code? I am thinking that the temp table is alive until you execute the "Set Rs = Nothing". I think this implicitly (sp?) closes the connection and as a result your temp table goes away.

Here is a blerb from BOL:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Can you run your code in debug and check this out?|||The problem is that the local temp table is accessible only through the SP and not from outside, however the global temp table is accissble from outside.|||Unless a temporary table is created within a stored procedure it persists until it is explicitly dropped or it's connection ends.

The problem is that your code is using a second connection to populate the list box than the one that created the table. If you have an adp project, you shouldn't have to make a new connection or even create a temporary table. Just set your listbox's source directly, and remember to requery it to show the results:

SQL = SQL & "SELECT distinct dbo.tblContact.ContactID,ISNULL(dbo.tblContact.Fir stName, '') "
SQL = SQL & "+ ' ' + ISNULL(dbo.tblContact.LastName, '') AS [Contact Name]"
SQL = SQL & " FROM dbo.tblContact INNER JOIN dbo.tblProperty ON "
SQL = SQL & "dbo.tblContact.ContactID = dbo.tblProperty.ContactID "
SQL = SQL & "WHERE dbo.tblProperty.Zip in (" & Me!zips & ")Order By [Contact Name]"

Me.MListSource.RowSource = SQL
Me.MListSource.Requery

blindman