SQL Server in VBA problem

[LauraBethBrown]

I can't seem to be able to retrieve data from temporary tables. Is
something missing from my code? I can select the top ten form the
table and can select it into the temp table #lb, but I get nothing when
I try to retrieve the data from the temp table. Can anyone help?

Sub EmailSQLs()

Dim qt As QueryTable

sqlstr = "select top 10 * into #lb from dim_email" + _
" select * from #lb"

connstring = _
"ODBC;DSN=test;UID=;PWD=;Database=Kpi"

With ActiveSheet.QueryTables.Add(Connection:=connstring,
Destination:=Range("A1"), SQL:=sqlstr).Refresh
End With
End Sub

 

[Tim Williams]

Debug.Print your SQL and run it in a query tool: syntax looks odd...

Tim

 

[NickHK]

I would guess a querytable can only function on an SQL statement that
returns a single recordset. but assuming that is wrong, I doubt the correct
syntax for multiple SQL statements is like that.
You need the temp table ?

NickHK

 

[LauraBethBrown]

The syntax works in SQL server. The pasted version is just a sample of
a more detailed query that I need a temp table for.

 

[LauraBethBrown]

Thanks for the help everyone! I figured out the answer to my problem
and figured I'd post it for future reference

Only using 1 # in #topten creates a local temp table and I needed to
create a global temp table by using ##topten. The local worked in SQL
server because I was using it within the same query window, but it
seems that submitting it through VBA submits it as if it were two
different query windows.

Also, this modified version of the code below worked. I'm sure
there's a cleaner version of the code to use, but this did work.

Sub EmailSQL3()
Dim qt As QueryTable

sqlstr = "select top 10 * into ##topten from dim_emailtemplete"

connstring = _
"ODBC;DSN=test;UID=;PWD=;Database=Kpirepository"

With ActiveSheet.QueryTables.Add(Connection:=connstring,
Destination:=Range("A1"), SQL:=sqlstr).Refresh
End With

sqlstr = "select top 10 * from ##topten"

connstring = _
"ODBC;DSN=test;UID=;PWD=;Database=Kpirepository"

With ActiveSheet.QueryTables.Add(Connection:=connstring,
Destination:=Range("A1"), SQL:=sqlstr).Refresh
End With

End Sub