I am getting the error for the ws.Cells(2, 2).CopyFromRecordset adoDbRs line:
operation is not allowed when the object is closed
If I remove the JOIN to the temp table #reporttable, it works fine, I feel like the recordset is empty because I'm sending it to a temp table and then trying to JOIN and pull out of that, but I'm not sure how to take the output from the final select query properly, it works fine when reformatted and typed directly into the SQL Server.
Secondary question:
Since I have been struggling with this, but can always get it working on the server, is there a way to setup a custom call to the SQL Server which is like command(var1,var2,var3) which runs a stored brick of working code on the SQL Server and returns what it returns, instead of my current process which is get it to work on the SQL Server and then struggle to adapt it into VBA?
Private Sub Run_Summary_Click()
Dim adoDbConn As New ADODB.Connection
Dim adoDbRs As New ADODB.Recordset
Dim selectCmd As New ADODB.Command
Dim Machvar As Integer
Machvar = Worksheets("SumImport").Range("A1").Value
Dim DateYMD As String
Dim DateStart As Date
Dim DateEnd As Date
Dim SQL As String
DateYMD = Format(Worksheets("SumImport").Range("A2").Value, "YYYY-MM-DD")
' Open connection to the SQL Server database
adoDbConn.Open "Provider=SQLOLEDB; Data Source=********; Initial Catalog=SMP; User Id=Query;"
' Execute the select query
selectCmd.ActiveConnection = adoDbConn
selectCmd.CommandText = "IF OBJECT_ID('tempdb.#reporttable2') IS NOT NULL DROP TABLE #reporttable2 " & _
" SELECT " & _
" DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DateTime) / 5)*5, '20000101') as Date_Time " & _
" ,max(Part_Count)-min(Part_Count) as PartsMade " & _
" ,max(convert(char(5), DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DateTime) / 5)*5, '20000101'), 108)) as times " & _
" ,max(Alarm_Light) as AlarmLight " & _
" ,max(PV_Alarm) as AlarmCode " & _
" INTO #reporttable2 " & _
" FROM [33_TestImport]" & _
" Where [DateTime]>= DateAdd(Hour, DateDiff(Hour, 0, '" & DateYMD & "')-0, 0) AND [DateTime]<= DateAdd(Hour, DateDiff(Hour, 0, '" & DateYMD & "')+24, 0) " & _
" AND Machine_Number = " & Machvar & " " & _
" Group BY DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DateTime) / 5)*5, '20000101') " & _
" select * " & _
" from #reporttable2 p " & _
" right join SMP.dbo.Timerange c " & _
" ON c.mins = p.times " & _
" order by mins "
Set adoDbRs = selectCmd.Execute(, , adCmdText)
' Clear the contents in cells where we're going to display the result
Dim cellRange As Range
Dim ws As Worksheet
Set ws = Worksheets("SumImport")
ws.Activate
Set cellRange = Worksheets("SumImport").Range("B1:M1800")
cellRange.ClearContents
' Activate the Worksheet
Set ws = Worksheets("SumImport")
ws.Activate
' Put the query results starting from cell B2
ws.Cells(2, 2).CopyFromRecordset adoDbRs
' Set the column header
ws.Cells(1, 2) = "DateTime"
ws.Cells(1, 3) = "Part Total"
ws.Cells(1, 4) = "TimeSync"
ws.Cells(1, 5) = "Alarm Light"
ws.Cells(1, 6) = "Alarm Code"
' Close the connection and free the memory
Set adoDbRs = Nothing
Set selectCmd = Nothing
adoDbConn.Close
Set adoDbConn = Nothing
Set ws = Worksheets("Summary")
ws.Activate
End Sub
And the code that works in SQL directly
drop table #reporttable2
declare @dateget as date
set @Dateget = '2020-03-19'
SELECT
DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DateTime) / 5)*5, '20000101') as Date_Time
,max(Part_Count)-min(Part_Count) as PartsMade
,max(convert(char(5), DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DateTime) / 5)*5, '20000101'), 108)) as times
into #reporttable2
FROM
[SMP].[dbo].[33_TestImport]
where [DateTime]>= DateAdd(Hour, DateDiff(Hour, 0, @Dateget)-1, 0)
and [DateTime]<= DateAdd(Hour, DateDiff(Hour, 0, @Dateget)+24, 0)
GROUP BY
DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DateTime) / 5)*5, '20000101')
select *
from #reporttable2 p
right join SMP.dbo.Timerange c
ON c.mins = p.times
order by mins
