EXCEL 2013 VBA Querying two files on separate databases on different servers at same time -

EXCEL 2013 VBA Querying two files on separate databases on different servers at same time -

i have moderate experience when comes excel vba , ado. have 2 files task file , calendar file each of on different database , different server. need determine number of actual work days associated task. need take timestamp date task file, check against calendar file determine how many days in task actual working days. figured out how open 2 separate database connections in vba script, cannot figure out how combine 2 files can working days count.

something this

select taskid, count(*) taskfile, calendar taskdate >= calendardate , taskdate <= calendardate , calendarworkday = 1 grouping taskid

i thought preloading worksheet calendar info don't see how query work.

any suggestions or code snippets appreciated.

this quick , dirty subroutine suggested in sec comment above. won't work need right out of box, overall thought sound. realize every record returned in first query generate new query sec file/database, pretty hairy if there lot of records.

sub tworecordsets() dim objconn adodb.connection, objconn2 adodb.connection dim rs adodb.recordset dim strsql string dim strconn string, strconn2 string 'open first connection set objconn = new adodb.connection strconn = "<your 1st connection string>" objconn.open strconn 'open sec connection set objconn2 = new adodb.connection strconn2 = "<your 2nd connection string>" objconn2.open strconn2 'first query: strsql = "select taskid, taskdate taskfile grouping taskid" 'open first recordset using first query set rs = new adodb.recordset rs.open strsql, objconn 'die if there no records returned if rs.eof , rs.bof exit sub end if 'loop through recordset rs.movefirst until rs.eof 'build sql statement sec bit. might have monkey quote marks , date formats depending on db strsql = "select count(*) recordcount calendar '" & rs.fields("taskdate").value & "' >= calendardate , '" & rs.fields("taskdate").value & "' <= calendardate , calendarworkday = 1" 'open recordset set rs2 = new adodb.recordset rs2.open strsql, objconn2 'get reply homecoming heresyouranswer = rs2.fields("recordcount").value 'iterate next record in rs rs.movenext loop end sub

excel vba ado