Multiple External Data Returns

[Guest]

I have an SQL statement that return 3 sets of data to an SQL editor. Each
Statement is separated by a semi-colon. Not all statements have equal titles
or columns. Is there a way to return the three sets in one get external Data
action or do i have to loop through the SQl three time?

My code currently builds the SQL Statement from start to finish and runs
fine for standard returns, but only lists the data return for the first data
set. Is this a limitation with Excel or can I return all 3 data sets in one
shot? In other words do i need to code some sort of looping?

Thanks!

 

[Tim]

How are you running the SQL ?
ADO ?
http://www.freevbcode.com/ShowCode.asp?ID=3058

Tim

 

[Guest]

ADO?
I have a Db2 datasourse defined, in my ODBC connections, Then I gather the
sql statement, and normally run the following code populate a report.
However now I want to run three bits of SQL stored in a MultiSQL array and
just drop it to a data sheet to be distributed from there. I have a form to
gather my UserID and Password.

I want the target cell to adjust to the last row of the last query results.
Currently the first data set returns fine. I don't know if I have to create
a record set definition in Excel (never done that before) then drop the
results into Excel multiple times. This code ony brings back the first set.
It could be that the data set for the
#2-4 has not been established and so I am really not refreshing the data for
the first time and the code if failing. How do i make this loop through each
SQL script and returnt he record set?

Thanks sooo much for yor advice!

VB is:
For iCnt = 0 To sqlCounter - 1
mySQL = MultiSQL(iCnt)

'****** Set Connection String *******************
ConnectString = "ODBC;DSN=DB2source" & ";UID=" & myUID & ";PWD=" & myPWD
& ";DBALIAS=DB2source;TXNISOLATION=1"
DataTarget = Cells(CntRow, 1).Address

Worksheets(TargetSheet).Select

'******** Refresh the data **********************************
On Error GoTo ResetTitles
With ActiveSheet.QueryTables.Add(Connection:=ConnectString,
Destination:=Range(DataTarget), Sql:=mySQL)
.Refresh BackgroundQuery:=False
End With
CntRow = (Cells(65536, 1).End(xlUp).Row + 2)
Next iCnt

 

[Guest]

OH I see.... what I am fooking for is :
SQL1) count cows, horses and pigs in Ariz
SQL2) count houses, cars in OHIO
SQL#) count people in USA

Return these counts on the same data page, one SQL dataset below the other.
I can get a single (multi record) data set fine. I need to get 3 separate
data sets.

 

[Tim]

Can't you just run the 3 queries separately ? I'm not sure that Querytables
support more than one resultset.

Or if your number of columns is the same for each query use "union all" to
join the counts as a single recordset.

Tim