How to insert into a TEMP Table in VBA

2
votes

When I run the following code I receive an error, "Run-time error '1004': Application-defined or object-defined error"

When I select debug, the following line is highlighted: .Refresh BackgroundQuery:=False

querystr = "SET NOCOUNT ON" & Chr(13) & _
        "SELECT CSD.StoreNo AS 'StoreNo',SUM(CSD.Amount) as totalSales " & Chr(13) & _
        "INTO    #SalesOfTheStores " & Chr(13) & _
        "FROM Purchase.dbo.CashsheetDetail as CSD " & Chr(13) & _
        "INNER JOIN Purchase.dbo.CashsheetHeader as CSH on CSH.TransferID = CSD.TransferID and CSH.StoreNo = CSD.StoreNo " & Chr(13) & _
        "WHERE  CSD.Comments = 'Total Gross Sales' AND CSH.DayendDate between '" & StartDate & "' And '" & EndDate & "' " & Chr(13) & _
        "GROUP BY CSD.StoreNo; "



With ActiveSheet.QueryTables.Add(Connection:= _
    "ODBC;DRIVER=SQL Server;SERVER=" & Div & "DBL01\SR;UID=" & User & ";APP=Microsoft Office 2003;WSID=" & PC & ";DATABASE=Purchase;Trusted_Connection=Yes" _
    , Destination:=Range("A1"))
    .RefreshStyle = xlOverwriteCells
    .CommandText = querystr
    .Refresh BackgroundQuery:=False

End With

1
vbaexcel

1 Answers

0
votes

Your query string does not return data. Change your SQL to be a SELECT statement rather than a SELECT INTO (remove the INTO clause).