I have a vb6 application that connects to SQL Server 2005 database (with
compatibility set to 80) using ADO 2.8 (sp1). At some point of time you can
not create temp tables using ADO Connection object. Here is the sample code
that proves it.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
'Author: Madhusudana Gorthi
'Instructions:
' Create a DSN with a name 'SQLNative' using SQL Native Client Driver to
connect to a database (with compatibility = 80) on SQL Server 2005
' Create a test EXE project in visual basic 6(sp6).
' Add reference to ADO 2.8 (basically any version of ADO).
' One Form1 add a Command button with name Command1.
' Add following code to the form editor and run by pressing F8.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
Option Explicit

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

cn.Open "DSN=SQLNative;UID=bob;Trusted_Connection=Yes; "

' Following statement will create the temp table.
cn.Execute "CREATE TABLE ##Test123 (TempRowNum int NOT NULL, ID int NOT
NULL, MainData varchar(255) NULL, ScrollTipData varchar(255) NULL)"

' Some thing happens to connection cn if you run the following line.
' It can not create temp table any more.
' If you want to create temp table use cmd object (or) set
cmd.ActiveConnection = Nothing and use cn object
Set cmd.ActiveConnection = cn

' Following statement will create the temp table but deletes it
immedietely. All of it happens immidietely with in a millisecond.
' You can see that by running this code by pressing F5. But does not
through error.
cn.Execute "CREATE TABLE ##Test21 (TempRowNum int NOT NULL, ID int NOT
NULL, MainData varchar(255) NULL, ScrollTipData varchar(255) NULL)"

' Following statement will create the temp table if you are running code
by pressing F8.
cmd.CommandText = "CREATE TABLE ##Test321 (TempRowNum int NOT NULL, ID
int NOT NULL, MainData varchar(255) NULL, ScrollTipData varchar(255) NULL)"
cmd.Execute
Set cmd = Nothing

' Following statement will create the temp table.
cn.Execute "CREATE TABLE ##Test321 (TempRowNum int NOT NULL, ID int NOT
NULL, MainData varchar(255) NULL, ScrollTipData varchar(255) NULL)"


cn.Execute "drop table ##Test321"
cn.Execute "drop table ##Test123"

cn.Close

Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing

Exit Sub

ErrorHandler:
Debug.Print Err.Description

End Sub


Can any body guess what is the problem? Please let me know if you have an
answer. Thank you.