Thread: Multiple Temp Tables

Need some help executing sql code from VBA. Originally I was running this code in SQL, pasting it in my excel document and just running my macro, but my manager wants to be able to do it when I'm on vacation.

native SQL code http://pastebin.com/8wL71jSC

/*************************************************************/
/**************** SELECTS SEASONAL FACILITIES ****************/
/*************************************************************/
select fd.facilitymasterid, fd.effectivedate, IsOpenJanuary ,IsOpenFebruary ,isOpenMarch ,
isOpenApril ,IsOpenMay ,isOpenJune ,isOpenJuly ,IsOpenAugust ,isOpenSeptember ,isOpenOctober ,
IsOpenNovember ,isOpenDecember into ##tmp_RD from corfacilitydetail fd, 
(select facilitymasterid, max(effectivedate) as maxdate from corfacilitydetail 
where rowstatus = 'A' group by facilitymasterid) maxresults
where fd.facilitymasterid = maxresults.facilitymasterid and
fd.effectivedate = maxresults.maxdate
and rowstatus = 'A'

/*******************************************************
** selects FacilityMasterID, FacilityName and Number, ** 
** InspectFreq, FacilityCategor and ServiceProvider ****
** for only active facilities **************************
*******************************************************/

select b.Fname, b.FacilityMasterId, b.InspectionFrequency, b.FacilityCategory,
c.LastName + ',' + c.FirstName as ServiceProvider, b.siteCity into ##tmp_RD2 from
	(select z.FacilityMasterID, z.Fname, z.InspectionFrequency, z.siteCity,
	a.description1 as FacilityCategory, z.Responsible1ServiceProviderID from
		(select f.FacilityMasterId, FacilityName + ' [' + FacilityNumber + ']' as Fname,
		f.InspectionFrequency, f.FacilityCategoryID, f.Responsible1ServiceProviderID,
		f.FacilityStatusID, f.siteCity from 
			(select FacilityMasterId, max(effectivedate) as maxdate 
			from corFacilityDetail where rowStatus = 'A' group by FacilityMasterID
			) as x inner join corFacilityDetail as f on f.FacilityMasterId = x.FacilityMasterID 
		and f.effectivedate = x.maxdate and rowStatus = 'A') as z 
	inner join corFacilityCategory as a on a.FacilityCategoryID = z.FacilityCategoryID
	and z.FacilityStatusID = 'ffffffff-ffff-ffff-ffff-fffffffffffa') as b
inner join corServiceProvider as c on c.ServiceProviderID = b.Responsible1ServiceProviderID

select facilitymasterid, max(InspectionDate) as LastInspection into ##tmp_RD3 from insInspection where RowStatus = 'A'
group by facilitymasterid

select ##tmp_RD2.Fname, ##tmp_RD2.FacilityMasterID, ##tmp_RD2.InspectionFrequency, ##tmp_RD2.FacilityCategory,
##tmp_RD2.ServiceProvider, ##tmp_RD2.siteCity, ##tmp_RD.IsOpenJanuary, ##tmp_RD.IsOpenFebruary, ##tmp_RD.isOpenMarch, 
##tmp_RD.isOpenApril, ##tmp_RD.IsOpenMay, ##tmp_RD.isOpenJune, ##tmp_RD.isOpenJuly, ##tmp_RD.IsOpenAugust, 
##tmp_RD.isOpenSeptember, ##tmp_RD.isOpenOctober, ##tmp_RD.IsOpenNovember, ##tmp_RD.isOpenDecember,
##tmp_RD3.LastInspection, dateadd(Day,##tmp_RD2.InspectionFrequency, ##tmp_RD3.LastInspection) as NextInspection  
from ##tmp_RD, ##tmp_RD3, ##tmp_RD2 
where ##tmp_RD.facilityMasterID = ##tmp_RD3.facilityMasterID and ##tmp_RD.facilityMasterID = ##tmp_RD2.facilityMasterID
order by ##tmp_RD2.ServiceProvider, ##tmp_RD2.FacilityCategory, ##tmp_RD2.InspectionFrequency asc, NextInspection


drop table ##tmp_RD
drop table ##tmp_RD2
drop table ##tmp_RD3

VBA attempt http://pastebin.com/pBsQxUw6

Sub getData()
    Dim conn As Variant
    Dim rs As Variant
    Dim cs As String
    Dim query As String
    Dim row As Integer
    
    Set conn = CreateObject("adodb.connection")
    Set rs = CreateObject("adodb.recordset")
    
    'The database in this instance has been set as Northwind.
    'you will need to update the database for what yours is called.
    'The IP Address '127.0.0.1' represents localhost.  If you
    'are trying to connect to a remote sql server then you will
    'either need to enter the ip address or URL of that server.
    'In the connection string below, 1433 is the port number
    'the SQL server is listening on.  If your sql server is
    'listening on a different port you'll have to change it.
    '1433 is the default port for SQL Server.
    
    
    cs = "DRIVER=SQL Server;"
    cs = cs & "DATABASE=hedgehog inspector;"
    cs = cs & "SERVER=Pugsley"
    
    'parameters here are connectionSring, username, password
    'you will need to put the actual username and password in
    'quotes here for this code to work.
    conn.Open cs, "hedgehog user", "hedgehog"
        
    query = "select fd.facilitymasterid, fd.effectivedate, IsOpenJanuary ,IsOpenFebruary ,isOpenMarch ," & _
                "isOpenApril ,IsOpenMay ,isOpenJune ,isOpenJuly ,IsOpenAugust ,isOpenSeptember ,isOpenOctober ," & _
                "IsOpenNovember ,isOpenDecember into ##tmp_RD from corfacilitydetail fd," & _
                "(select facilitymasterid, max(effectivedate) as maxdate from corfacilitydetail " & _
                "where rowstatus = 'A' group by facilitymasterid) maxresults " & _
                "where fd.facilitymasterid = maxresults.facilitymasterid and " & _
                "fd.effectivedate = maxresults.maxdate " & _
                "and rowstatus = 'A'"
    rs.Open query, conn
    
    query = "select b.Fname, b.FacilityMasterId, b.InspectionFrequency, b.FacilityCategory," & _
                "c.LastName + ',' + c.FirstName as ServiceProvider, b.siteCity into ##tmp_RD2 from " & _
                "(select z.FacilityMasterID, z.Fname, z.InspectionFrequency, z.siteCity," & _
                "a.description1 as FacilityCategory, z.Responsible1ServiceProviderID from " & _
                "(select f.FacilityMasterId, FacilityName + ' [' + FacilityNumber + ']' as Fname," & _
                "f.InspectionFrequency, f.FacilityCategoryID, f.Responsible1ServiceProviderID," & _
                "f.FacilityStatusID, f.siteCity from " & _
                "(select FacilityMasterId, max(effectivedate) as maxdate " & _
                "from corFacilityDetail where rowStatus = 'A' group by FacilityMasterID " & _
                ") as x inner join corFacilityDetail as f on f.FacilityMasterId = x.FacilityMasterID " & _
                "and f.effectivedate = x.maxdate and rowStatus = 'A') as z " & _
                "inner join corFacilityCategory as a on a.FacilityCategoryID = z.FacilityCategoryID " & _
                "and z.FacilityStatusID = 'ffffffff-ffff-ffff-ffff-fffffffffffa') as b " & _
                "inner join corServiceProvider as c on c.ServiceProviderID = b.Responsible1ServiceProviderID"
    'rs.Open query, conn
    
    query = "select facilitymasterid, max(InspectionDate) as LastInspection into ##tmp_RD3 " & _
    "from insInspection where RowStatus = 'A' group by facilitymasterid"
    rs.Open query, conn
        
    query = "select ##tmp_RD2.Fname, ##tmp_RD2.FacilityMasterID, ##tmp_RD2.InspectionFrequency, " & _
            "##tmp_RD2.FacilityCategory, ##tmp_RD2.ServiceProvider, ##tmp_RD2.siteCity, ##tmp_RD.IsOpenJanuary," & _
            "##tmp_RD.IsOpenFebruary, ##tmp_RD.isOpenMarch, ##tmp_RD.isOpenApril, ##tmp_RD.IsOpenMay," & _
            "##tmp_RD.isOpenJune, ##tmp_RD.isOpenJuly, ##tmp_RD.IsOpenAugust, ##tmp_RD.isOpenSeptember, " & _
            "##tmp_RD.isOpenOctober, ##tmp_RD.IsOpenNovember, ##tmp_RD.isOpenDecember, ##tmp_RD3.LastInspection, " & _
            "dateadd(Day,##tmp_RD2.InspectionFrequency, ##tmp_RD3.LastInspection) as NextInspection from ##tmp_RD, " & _
            "##tmp_RD3, ##tmp_RD2 where ##tmp_RD.facilityMasterID = ##tmp_RD3.facilityMasterID and " & _
            "##tmp_RD.facilityMasterID = ##tmp_RD2.facilityMasterID order by ##tmp_RD2.ServiceProvider, " & _
            "##tmp_RD2.FacilityCategory, ##tmp_RD2.InspectionFrequency asc, NextInspection"
    'rs.Open query, conn
    
    query = "select * from ##tmp_RD"
    rs.Open query, conn
    
    row = 0
    Do Until rs.EOF
        row = row + 1
        'Cells(row, 1).Value = rs.fields("Fname").Value
        Cells(row, 1).Value = rs.fields("FacilityMasterId").Value
        'Cells(row, 2).Value = rs.fields("LastInspection").Value
        rs.movenext
    Loop
      
    
    'If rs.State = adStateOpen Then
        rs.Close
    '    Set rs = Nothing
    'End If
    
    conn.Close
    Set conn = Nothing

End Sub

Basically I can connect and run each query individually if I want, but when I try to run them all its not working. My guess is once I start a new rs connection the told temp table is dropped even though is should be global.

Am I fighting a loosing battle with Excel/VBA here? Should I attempt to merge my SQL query into one large query or perhaps put it into a stored procedure and call that instead?

Any help would be greatly appreciated.

Thanks for looking. Think I figured it out. I just put my 3 select into statements into one string so it was all done on the same connection. I had tried that before but I think I forgot to add a space to the end of each select statement when I concatenated the string.