Re: Multiple External Data Returns

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 *** 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 Active***.QueryTables.Add(Connection:=ConnectString,
Destination:=Range(DataTarget), Sql:=mySQL)
.Refresh BackgroundQuery:=False
End With
CntRow = (Cells(65536, 1).End(xlUp).Row + 2)
Next iCnt

"Tim" wrote:

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

Tim


"Candyman" <Candyman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2DB3EE83-BF18-437F-854C-113E3E1C6E66@xxxxxxxxxxxxxxxx
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!





.

Quantcast