Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Server in VBA problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Server in VBA problem
Debug.Print your SQL and run it in a query tool: syntax looks odd...
Tim wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Server in VBA problem
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 wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Server in VBA problem
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Server in VBA problem
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=Kpirepositor y" 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=Kpirepositor y" With ActiveSheet.QueryTables.Add(Connection:=connstring , Destination:=Range("A1"), SQL:=sqlstr).Refresh End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem updating link from Novell server to windows server #REF er | Excel Discussion (Misc queries) | |||
ADO connection problem when using server | Excel Programming | |||
query problem after changing to SQL server | Excel Discussion (Misc queries) | |||
Problem linking with SQL Server | Excel Programming | |||
Problem linking with SQL Server | Excel Programming |