>Argh! This is driving me nuts. I am trying to create a temporary table in SQL Server 7.0 from VB 6.0 using SQLEOLEDB and ADO for dynamic reporting purposes. It gives me no errors when I do the Execute command but as soon as I try and reference a recordset to the temp table name I get "Invalid object name '#tblNewTableName'". There is a KB article on Microsoft's site but it only has to do with paramterized queries (and I have already tried their "solution" with no success). Has anyone else tried to do this? Below is my code am using so if anyone can help me out I would appreciate it.
>
>Dim cnCreateReportTable as ADODB.Connection
>Dim rstTest as ADODB.Recordset
>
> Set cnCreateReportTable = new ADODB.Connection
> cnCreateReportTable.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "Persist Security Info=False;User ID=" & UID & ";" & _
> "Initial Catalog=" & DBName & ";Data Source=" & SQLName & ";" & _
> "Password=" & PWD
> cnCreateReportTable.Open
>
> cnCreateReportTable.Execute ("SELECT output_file.* INTO #tblNewTables FROM output_file " & _
> "WHERE (((output_file.Phys_Num)='000303'));")
>
> Set rstTest = New ADODB.Recordset
> rstTest.Open ("SELECT * FROM #tblNewNames;"), cnCreateReportTable, adOpenForwardOnly, adLockReadOnly
> With rstTest
> Do Until .EOF
> Debug.Print rstTest.Fields("jas")
> Loop
> End With
> rstTest.close
> Set rstTest = Nothing
> cnCreateReportTable.Close
> Set cnCreateReportTable = Nothing

I think that you need to have your temp table Global (using ##). If you use only one #, the table will exists only for the current transaction.