excel - Returned Recordset is closed (mysql DB accessed via ODBC in VBA) -

long time viewer first time poster. i'm working on database application front-end in excel. hence using vba access mysql database. driver i'm using odbc driver (windows) , have valid connection database lots of other queries function well.

what i'm trying return results series of sql statements rather trying combine 1 massive statement (which work difficult maintain/understand). have added flag flag_multi_statements = 67108864 connection string suppressed driver syntax errors.

but when run following:

querydb.open sqlquery, conn 

the recordset (querydb) remains closed no apparent error. sql statement can found here.

i may generating errors aren't being returned vba here appreciated.

note: sql statements work can paste statement phpmyadmin , returns correct (non-empty) results. don't know if statements problem perhaps use of create temporary table ... or use of multiple statements in general.

also guess driver may trying return result each sql statement , vba getting first or something...

edit: sql statement future reference.

create temporary table tmporders select o.customername, sum(o.sales) sales, sum(totalfobcost + totallandedcost + totallocalcost + totalcmtcost) totalcost, year(o.deliverydate) year, month(o.deliverydate) month devere_costing.orders_fixed_extras o o.orderapproved = true , o.ordercanceled = false , o.deliverydate between '2014-01-01' , '2014-03-31' group customername, year, month order year asc, month asc, customername asc;  create temporary table tmpproj select p.customername,    if(p.month > 9, p.year, p.year - 1) trueyear,    1 + ((p.month + 2) mod 12) truemonth,    sum(p.actualsalesinvoiced) salesinvoiced,    sum(p.budget) budget devere_costing.sales_projection_data p group p.customername, p.year, p.month having trueyear between year('2014-01-01') , year('2014-03-31') , truemonth between month('2014-01-01') , month('2014-03-31');  create temporary table tmpleft select if(o.customername null, p.customername, o.customername) customername, p.budget totalbudget, o.sales sales, p.salesinvoiced, 0 variancetobudget, o.totalcost, 0 directmargin, 0 directmarginpercent, if(o.year null, p.trueyear, o.year) year, if(o.month null, p.truemonth, o.month) month tmporders o left join tmpproj p on (o.customername = p.customername , o.year = p.trueyear , o.month = p.truemonth);  create temporary table tmpright select if(o.customername null, p.customername, o.customername) customername, p.budget totalbudget, o.sales sales, p.salesinvoiced, 0 variancetobudget, o.totalcost, 0 directmargin, 0 directmarginpercent, if(o.year null, p.trueyear, o.year) year, if(o.month null, p.truemonth, o.month) month tmporders o right join tmpproj p on (o.customername = p.customername , o.year = p.trueyear , o.month = p.truemonth);  (select * tmpleft) union distinct (select * tmpright); 

i have answered own question!

the secret lies here:

so right in there more 1 recordset returned. had iterate through them find data want. collection isn't indexed have search through each one. in case every sql statement not return recordset (that's why recordset remained closed when tried open it). exception last sql statement returns records. loop looks like:

dim rs adodb.recordset set rs = querydb(sql)  ' loop through returned recordsets find data     if not rs nothing         if rs.state = adstateopen             ' have open recordset.  means final select statement             ' has returned data.             exit         else             ' otherwise iterate through next recordset             set rs = rs.nextrecordset         end if     else         msgbox "no recordset returned sql statement"         goto exitcode     end if loop 

answer copied question body:

i have answered own question!

the secret lies here:

so right in there more 1 recordset returned. had iterate through them find data want. collection isn't indexed have search through each one. in case every sql statement not return recordset (that's why recordset remained closed when tried open it). exception last sql statement returns records. loop looks like:

dim rs adodb.recordset set rs = querydb(sql)  ' loop through returned recordsets find data     if not rs nothing         if rs.state = adstateopen             ' have open recordset.  means final select statement             ' has returned data.             exit         else             ' otherwise iterate through next recordset             set rs = rs.nextrecordset         end if     else         msgbox "no recordset returned sql statement"         goto exitcode     end if loop