VB Forum / Database Access / March 2008

Is there some trick to creating a temp table on a SQL server through the ADO
connection object? This code works fine if I create a permanent table, but
doesn't do anything if I add the # to create a temp table

Option Explicit
Public oCon As ADODB.Connection

Sub OpenConnection()

Dim sConnectString As String

Set oCon = New ADODB.Connection

sConnectString = "Provider=sqloledb;" & _
          "Data Source=Server\Instance1;" & _
          "Initial Catalog=DBToUse;" & _
          "Integrated Security=SSPI"

oCon.Open sConnectString

End Sub

Sub CloseConnection()
   If Not oCon Is Nothing Then
       If (oCon.State And adStateOpen) = adStateOpen Then
           oCon.Close
       End If
       Set oCon = Nothing
   End If
End Sub

Sub CreateDatesTable(sDate1 As String, sDate2 As String)

Dim sSQL As String

OpenConnection
sSQL = "Create table DateRange(id int primary key, Dates datetime NOT NULL)
" -- works
'sSQL = "Create table #DateRange(id int primary key, Dates datetime NOT
NULL) " --doesn't work
oCon.Execute sSQL

'sSQL = "insert into #DateRange (id, Dates) values (1, '" & sDate1 & "')"
'oCon.Execute sSQL
'sSQL = "insert into #DateRange (id, Dates) values (2, '" & sDate2 & "')"
'oCon.Execute sSQL

CloseConnection

End Sub

TIA

Matt