I've been using ADODB in (Excel) VBA to pull SQL data as required. All of it has been running without an issue, including a case where I needed to pull the data into a temporary table to make some calculations in the final output simpler (all in a single SQL query).
However, I recently had to make a change to that particular query, and now it doesn't seem to return anything and so Excel throws an 3704 error stating "Operation is not allowed when the object is closed". From my research I see this often occurs when using temporary tables with ADODB in VBA, but as I've successfully done just that previously I'm extremely confused as to what's changed to cause the issue. (For reference I've used debug.print to get the query produced in Excel and it successfully executes when pasted into Server Management Studio).
I've not been using SQL long so I'm sure there's plenty I'm not doing 'right', but this is the first issue I've been unable to resolve myself (with the help of Dr. Google). I also appretiate that without the databases it's not easy for others to error-check the code, so I'm willing to try any/all thoughts/ideas/suggestions to try and get this to work! (Also, just let me know if I've missed any useful information, etc.)
SQL code as output by using debug.print on the query produced in VBA (for easier reading):
SET NOCOUNT ON
Declare @End_Date Datetime = '11-Jun-2023'
Declare @Start_Date Datetime = '01-May-2023'
Declare @Mon_Count Int = datediff( day, -7, @End_Date ) / 7 - datediff( day, -6, @Start_Date) / 7
Declare @Tue_Count Int = datediff( day, -6, @End_Date ) / 7 - datediff( day, -5, @Start_Date ) / 7
Declare @Wed_Count Int = datediff( day, -5, @End_Date ) / 7 - datediff( day, -4, @Start_Date ) / 7
Declare @Thu_Count Int = datediff( day, -4, @End_Date ) / 7 - datediff( day, -3, @Start_Date ) / 7
Declare @Fri_Count Int = datediff( day, -3, @End_Date ) / 7 - datediff( day, -2, @Start_Date ) / 7
Declare @Sat_Count Int = datediff( day, -2, @End_Date ) / 7 - datediff( day, -1, @Start_Date ) / 7
Declare @Sun_Count Int = datediff( day, -1, @End_Date ) / 7 - datediff( day, -0, @Start_Date ) / 7
Select
CAST( tk.ref As Int ) As 'Ref Num'
,MIN( ls.subdt ) As 'First Log Sub of Period'
,SUM( Case When tl_part.wtype in ('L', 'LH') Then tl_part.lhrs Else 0 End ) as 'Standard Hours'
,Sum( tl_part.lhrs ) as 'Total Worked Hours'
,ISNULL( ( ( cth.cmonhrs * @Mon_Count ) +
( cth.ctueshrs * @Tue_Count ) +
( cth.cwedshrs * @Wed_Count ) +
( cth.cthurshrs * @Thu_Count ) +
( cth.cfrihrs * @Fri_Count) +
( cth.csathrs * @Sat_Count ) +
( cth.csunhrs * @Sun_Count )
), 0 ) as 'Contract Hours'
,( Select
Count (ph.phdate)
From
publichols As ph
Where
ph.area = tk.tkarea
and ph.phdate between @Start_Date and @End_Date
) *
( ISNULL( ( cth.cmonhrs +
cth.ctueshrs +
cth.cwedshrs +
cth.cthurshrs +
cth.cfrihrs +
cth.csathrs +
cth.csunhrs
), 0 ) / 5) As 'Public Holidays'
Into
#Temp_Table_1
From
trucks As tk
left join timelogs As tl_full On tl_full.ref = tk.ref
and tl_full.wtype in ( 'L', 'LH', 'O' )
left join timelogs as tl_part on tl_part.tindex = tl_full.tindex
and tl_part.tworkdt > @Start_Date
left join logsubs As ls On ls.logref = tl_full.logref
left join contract As ct On ct.ref = tk.ref
and @Start_Date between ct.stadt and ct.enddt
left join contracthours As cth On cth.ctrref = ct.ctrref
Where
tk.tkincl = 'Y'
and ls.subdt between @Start_Date and @End_Date
Group By
tk.ref
,tk.tkarea
,cth.cmonhrs
,cth.ctueshrs
,cth.cwedshrs
,cth.cthurshrs
,cth.cfrihrs
,cth.csathrs
,cth.csunhrs
Order By
Min (ls.subdt)
,CAST( tk.ref As Int )
Select
[Ref Num]
,[Standard Hours] / Case
When ([Total Worked Hours] > ([Contract Hours] - [Public Holidays]))
Then [Total Worked Hours]
Else ([Contract Hours]-[Public Holidays])
End as 'Monitor Ratio'
From
#Temp_Table_1
Order By
[First Log Sub of Period]
,[Ref Num]
Drop Table #Temp_Table_1
What I believe should be the relevant VBA parts (the debug.print DB_RS.EOF is a stand-in for where the RS would actually be used, but it's enough to throw the error).:
Sub Demo()
Dim Query As String
Query = Issue_Query
Dim RS As ADODB.Recordset
Set RS = Run_SQL_Query(DB_Query:=Query)
Debug.Print RS.EOF
End Sub
Function Run_SQL_Query(ByVal DB_Query As String) As ADODB.Recordset
Dim DB_Conn As ADODB.Connection
Set DB_Conn = New ADODB.Connection
DB_Conn.Open "Provider=SQLNCLI11;" & _
"Data Source=OurDB;" & _
"Initial Catalog=son_db_unicode;" & _
"Integrated Security=SSPI;"
Dim DB_RS As New ADODB.Recordset
DB_RS.CursorLocation = adUseClient
DB_RS.Open DB_Query, DB_Conn, adOpenStatic
Set Run_SQL_Query = DB_RS
End Function
Function Issue_Query() As String
Dim Query As String
Query = "SET NOCOUNT ON" & vbNewLine _
& vbNewLine _
& "Declare @End_Date Datetime = '11-Jun-2023'" & vbNewLine _
& "Declare @Start_Date Datetime = '01-May-2023'" & vbNewLine _
& vbNewLine _
& "Declare @Mon_Count Int = datediff( day, -7, @End_Date ) / 7 - datediff( day, -6, @Start_Date) / 7" & vbNewLine _
& "Declare @Tue_Count Int = datediff( day, -6, @End_Date ) / 7 - datediff( day, -5, @Start_Date ) / 7" & vbNewLine _
& "Declare @Wed_Count Int = datediff( day, -5, @End_Date ) / 7 - datediff( day, -4, @Start_Date ) / 7" & vbNewLine _
& "Declare @Thu_Count Int = datediff( day, -4, @End_Date ) / 7 - datediff( day, -3, @Start_Date ) / 7" & vbNewLine _
& "Declare @Fri_Count Int = datediff( day, -3, @End_Date ) / 7 - datediff( day, -2, @Start_Date ) / 7" & vbNewLine _
& "Declare @Sat_Count Int = datediff( day, -2, @End_Date ) / 7 - datediff( day, -1, @Start_Date ) / 7" & vbNewLine _
& "Declare @Sun_Count Int = datediff( day, -1, @End_Date ) / 7 - datediff( day, -0, @Start_Date ) / 7" & vbNewLine _
& vbNewLine _
& "Select" & vbNewLine _
& vbNewLine _
& " CAST( tk.ref As Int ) As 'Ref Num'" & vbNewLine _
& " ,MIN( ls.subdt ) As 'First Log Sub of Period'" & vbNewLine _
& " ,SUM( Case When tl_part.wtype in ('L', 'LH') Then tl_part.lhrs Else 0 End ) as 'Standard Hours'" & vbNewLine
Query = Query _
& " ,Sum( tl_part.lhrs ) as 'Total Worked Hours'" & vbNewLine _
& vbNewLine _
& " ,ISNULL( ( ( cth.cmonhrs * @Mon_Count ) +" & vbNewLine _
& " ( cth.ctueshrs * @Tue_Count ) +" & vbNewLine _
& " ( cth.cwedshrs * @Wed_Count ) +" & vbNewLine _
& " ( cth.cthurshrs * @Thu_Count ) +" & vbNewLine _
& " ( cth.cfrihrs * @Fri_Count) +" & vbNewLine _
& " ( cth.csathrs * @Sat_Count ) +" & vbNewLine _
& " ( cth.csunhrs * @Sun_Count )" & vbNewLine _
& " ), 0 ) as 'Contract Hours'" & vbNewLine _
& vbNewLine
Query = Query _
& " ,( Select" & vbNewLine _
& " Count (ph.phdate)" & vbNewLine _
& " From" & vbNewLine _
& " publichols As ph" & vbNewLine _
& " Where" & vbNewLine _
& " ph.area = tk.tkarea" & vbNewLine _
& " and ph.phdate between @Start_Date and @End_Date" & vbNewLine _
& " ) *" & vbNewLine _
& " ( ISNULL( ( cth.cmonhrs +" & vbNewLine _
& " cth.ctueshrs +" & vbNewLine _
& " cth.cwedshrs +" & vbNewLine _
& " cth.cthurshrs +" & vbNewLine _
& " cth.cfrihrs +" & vbNewLine _
& " cth.csathrs +" & vbNewLine _
& " cth.csunhrs" & vbNewLine _
& " ), 0 ) / 5) As 'Public Holidays'" & vbNewLine
Query = Query _
& vbNewLine _
& "Into" & vbNewLine _
& " #Temp_Table_1" & vbNewLine _
& vbNewLine _
& "From" & vbNewLine _
& " trucks As tk" & vbNewLine _
& " left join timelogs As tl_full On tl_full.ref = tk.ref" & vbNewLine _
& " and tl_full.wtype in ( 'L', 'LH', 'O' )" & vbNewLine _
& " left join timelogs as tl_part on tl_part.tindex = tl_full.tindex" & vbNewLine _
& " and tl_part.tworkdt > @Start_Date" & vbNewLine _
& " left join logsubs As ls On ls.logref = tl_full.logref" & vbNewLine _
& " left join contract As ct On ct.ref = tk.ref" & vbNewLine _
& " and @Start_Date between ct.stadt and ct.enddt" & vbNewLine _
& " left join contracthours As cth On cth.ctrref = ct.ctrref" & vbNewLine _
& vbNewLine
Query = Query _
& "Where" & vbNewLine _
& " tk.tkincl = 'Y'" & vbNewLine _
& " and ls.subdt between @Start_Date and @End_Date" & vbNewLine _
& vbNewLine _
& "Group By" & vbNewLine _
& " tk.ref" & vbNewLine _
& " ,tk.tkarea" & vbNewLine _
& " ,cth.cmonhrs" & vbNewLine _
& " ,cth.ctueshrs" & vbNewLine _
& " ,cth.cwedshrs" & vbNewLine _
& " ,cth.cthurshrs" & vbNewLine _
& " ,cth.cfrihrs" & vbNewLine _
& " ,cth.csathrs" & vbNewLine _
& " ,cth.csunhrs" & vbNewLine _
& vbNewLine _
& "Order By" & vbNewLine _
& " Min (ls.subdt)" & vbNewLine _
& " ,CAST( tk.ref As Int )" & vbNewLine _
& vbNewLine
Query = Query _
& "Select" & vbNewLine _
& " [Ref Num]" & vbNewLine _
& " ,[Standard Hours] / Case" & vbNewLine _
& " When ([Total Worked Hours] > ([Contract Hours] - [Public Holidays]))" & vbNewLine _
& " Then [Total Worked Hours]" & vbNewLine _
& " Else ([Contract Hours]-[Public Holidays])" & vbNewLine _
& " End as 'Monitor Ratio'" & vbNewLine _
& vbNewLine _
& "From" & vbNewLine _
& " #Temp_Table_1" & vbNewLine _
& vbNewLine _
& "Order By" & vbNewLine _
& " [First Log Sub of Period]" & vbNewLine _
& " ,[Ref Num]" & vbNewLine _
& vbNewLine _
& "Drop Table #Temp_Table_1"
Issue_Query = Query
End Function
Edited: I've added the old queries (in both VBA and SQL formats) below.
Old Query - SQL Format:
SET NOCOUNT ON
Declare @End_Date Datetime = '14-Jun-2023'
Declare @Start_Date Datetime = '01-May-2023'
Declare @Mon_Count Int = datediff( day, -7, @End_Date ) / 7 - datediff( day, -6, @Start_Date) / 7
Declare @Tue_Count Int = datediff( day, -6, @End_Date ) / 7 - datediff( day, -5, @Start_Date ) / 7
Declare @Wed_Count Int = datediff( day, -5, @End_Date ) / 7 - datediff( day, -4, @Start_Date ) / 7
Declare @Thu_Count Int = datediff( day, -4, @End_Date ) / 7 - datediff( day, -3, @Start_Date ) / 7
Declare @Fri_Count Int = datediff( day, -3, @End_Date ) / 7 - datediff( day, -2, @Start_Date ) / 7
Declare @Sat_Count Int = datediff( day, -2, @End_Date ) / 7 - datediff( day, -1, @Start_Date ) / 7
Declare @Sun_Count Int = datediff( day, -1, @End_Date ) / 7 - datediff( day, -0, @Start_Date ) / 7
Select
CAST( tk.ref As Int ) As 'Ref Num'
,MIN( ls.subdt ) As 'First Log Sub of Period'
,SUM( Case When tl.wtype in ('L', 'LH') Then tl.lhrs Else 0 End ) as 'Standard Hours'
,Sum(tl.lhrs) as 'Total Worked Hours'
,ISNULL( ( ( cth.cmonhrs * @Mon_Count ) +
( cth.ctueshrs * @Tue_Count ) +
( cth.cwedshrs * @Wed_Count ) +
( cth.cthurshrs * @Thu_Count ) +
( cth.cfrihrs * @Fri_Count) +
( cth.csathrs * @Sat_Count ) +
( cth.csunhrs * @Sun_Count )
), 0 ) as 'Contract Hours'
,( Select
Count (ph.phdate)
From
publichols As ph
Where
ph.area = tk.tkarea
and ph.phdate between @Start_Date and @End_Date
) *
( ISNULL( ( cth.cmonhrs +
cth.ctueshrs +
cth.cwedshrs +
cth.cthurshrs +
cth.cfrihrs +
cth.csathrs +
cth.csunhrs
), 0 ) / 5) As 'Public Holidays'
Into
#Temp_Table_1
From
trucks As tk
left join timelogs As tl On tk.ref = tl.ref
left join logsubs As ls On ls.logref = tl.logref
left join contract As ct On ct.ref = tk.ref
and @Start_Date between ct.stadt and ct.enddt
left join contracthours As cth On cth.ctrref = ct.ctrref
Where
tk.tkincl = 'Y'
and tl.wtype in ('L','LH','O')
and ls.subdt between @Start_Date and @End_Date
Group By
tk.ref
,tk.tkarea
,cth.cmonhrs
,cth.ctueshrs
,cth.cwedshrs
,cth.cthurshrs
,cth.cfrihrs
,cth.csathrs
,cth.csunhrs
Order By
Min (ls.subdt)
,CAST( tk.ref As Int )
Select
[Ref Num]
,[Standard Hours] / Case
When ([Total Worked Hours] > ([Contract Hours] - [Public Holidays]))
Then [Total Worked Hours]
Else ([Contract Hours]-[Public Holidays])
End as 'Monitor Ratio'
From
#Temp_Table_1
Order By
[First Log Sub of Period]
,[Ref Num]
Drop Table #Temp_Table_1
Old Query - VBA Format:
Function Issue_Old_Query() As String
Dim Query As String
Query = "SET NOCOUNT ON" & vbNewLine _
& vbNewLine _
& "Declare @End_Date Datetime = '11-Jun-2023'" & vbNewLine _
& "Declare @Start_Date Datetime = '01-May-2023'" & vbNewLine _
& vbNewLine _
& "Declare @Mon_Count Int = datediff( day, -7, @End_Date ) / 7 - datediff( day, -6, @Start_Date) / 7" & vbNewLine _
& "Declare @Tue_Count Int = datediff( day, -6, @End_Date ) / 7 - datediff( day, -5, @Start_Date ) / 7" & vbNewLine _
& "Declare @Wed_Count Int = datediff( day, -5, @End_Date ) / 7 - datediff( day, -4, @Start_Date ) / 7" & vbNewLine _
& "Declare @Thu_Count Int = datediff( day, -4, @End_Date ) / 7 - datediff( day, -3, @Start_Date ) / 7" & vbNewLine _
& "Declare @Fri_Count Int = datediff( day, -3, @End_Date ) / 7 - datediff( day, -2, @Start_Date ) / 7" & vbNewLine _
& "Declare @Sat_Count Int = datediff( day, -2, @End_Date ) / 7 - datediff( day, -1, @Start_Date ) / 7" & vbNewLine _
& "Declare @Sun_Count Int = datediff( day, -1, @End_Date ) / 7 - datediff( day, -0, @Start_Date ) / 7" & vbNewLine _
& vbNewLine _
Query = Query _
& "Select" & vbNewLine _
& vbNewLine _
& " CAST( tk.ref As Int ) As 'TKID'" & vbNewLine _
& " ,MIN( ls.subdt ) As 'First Log Sub of Period'" & vbNewLine _
& " ,SUM( Case When tl.wtype in ('L', 'LH') Then tl.lhrs Else 0 End ) as 'Standard Hours'" & vbNewLine _
& " ,Sum(tl.lhrs) as 'Total Worked Hours'" & vbNewLine _
& vbNewLine
Query = Query _
& " ,ISNULL( ( ( cth.cmonhrs * @Mon_Count ) +" & vbNewLine _
& " ( cth.ctueshrs * @Tue_Count ) +" & vbNewLine _
& " ( cth.cwedshrs * @Wed_Count ) +" & vbNewLine _
& " ( cth.cthurshrs * @Thu_Count ) +" & vbNewLine _
& " ( cth.cfrihrs * @Fri_Count) +" & vbNewLine _
& " ( cth.csathrs * @Sat_Count ) +" & vbNewLine _
& " ( cth.csunhrs * @Sun_Count )" & vbNewLine _
& " ), 0 ) as 'Contract Hours'" & vbNewLine _
& vbNewLine
Query = Query _
& " ,( Select" & vbNewLine _
& " Count (ph.phdate)" & vbNewLine _
& " From" & vbNewLine _
& " publichols As ph" & vbNewLine _
& " Where" & vbNewLine _
& " ph.area = tk.tkarea" & vbNewLine _
& " and ph.phdate between @Start_Date and @End_Date" & vbNewLine _
& " ) *" & vbNewLine _
& " ( ISNULL( ( cth.cmonhrs +" & vbNewLine _
& " cth.ctueshrs +" & vbNewLine _
& " cth.cwedshrs +" & vbNewLine _
& " cth.cthurshrs +" & vbNewLine _
& " cth.cfrihrs +" & vbNewLine _
& " cth.csathrs +" & vbNewLine _
& " cth.csunhrs" & vbNewLine _
& " ), 0 ) / 5) As 'Public Holidays'" & vbNewLine _
& vbNewLine
Query = Query _
& "Into" & vbNewLine _
& " #Temp_Table_1" & vbNewLine _
& vbNewLine _
& "From" & vbNewLine _
& " trucks As tk" & vbNewLine _
& " left join timelogs As tl On tk.ref = tl.ref" & vbNewLine _
& " left join logsubs As ls On ls.logref = tl.logref" & vbNewLine _
& " left join contract As ct On ct.ref = tk.ref" & vbNewLine _
& " and @Start_Date between ct.stadt and ct.enddt" & vbNewLine _
& " left join contracthours As cth On cth.ctrref = ct.ctrref" & vbNewLine
Query = Query _
& "Where" & vbNewLine _
& " tk.tkincl = 'Y'" & vbNewLine _
& " and tl.wtype in ('L','LH','O')" & vbNewLine _
& " and ls.subdt between @Start_Date and @End_Date" & vbNewLine _
& vbNewLine
Query = Query _
& "Group By" & vbNewLine _
& " tk.ref" & vbNewLine _
& " ,tk.tkarea" & vbNewLine _
& " ,cth.cmonhrs" & vbNewLine _
& " ,cth.ctueshrs" & vbNewLine _
& " ,cth.cwedshrs" & vbNewLine _
& " ,cth.cthurshrs" & vbNewLine _
& " ,cth.cfrihrs" & vbNewLine _
& " ,cth.csathrs" & vbNewLine _
& " ,cth.csunhrs" & vbNewLine _
& vbNewLine
Query = Query _
& "Order By" & vbNewLine _
& " Min (ls.subdt)" & vbNewLine _
& " ,CAST( tk.ref As Int )" & vbNewLine _
& vbNewLine _
& "Select" & vbNewLine _
& " TKID" & vbNewLine _
& " ,[Standard Hours] / Case" & vbNewLine _
& " When ([Total Worked Hours] > ([Contract Hours] - [Public Holidays]))" & vbNewLine _
& " Then [Total Worked Hours]" & vbNewLine _
& " Else ([Contract Hours]-[Public Holidays])" & vbNewLine _
& " End as 'Monitor Ratio'" & vbNewLine _
& vbNewLine
Query = Query _
& "From" & vbNewLine _
& " #Temp_Table_1" & vbNewLine _
& vbNewLine _
& "Order By" & vbNewLine _
& " [First Log Sub of Period]" & vbNewLine _
& " ,[Ref Num]" & vbNewLine _
& vbNewLine _
& "Drop Table #Temp_Table_1"
Issue_Old_Query = Query
End Function
So the issue was the line Sum(tl_part.lhrs) as 'Total Worked Hours' (found by tweaking the original SQL script one line at a time until it triggered the error in VBA, and then doing further checks to confirm it was just this line and not a combination of issues).
In Microsoft SQL Server Management Studio I then ran the script for just the first half of the script (all the bits prior to the second Select) without the results being displayed I realised there was a warning being generated; Null value is eliminated by an aggregate or other SET operation. Removing groupings, etc. confirmed there were some unexpected (or rather, overlooked) NULL values in the tl_part.lhrs data.
It seems SSMS just treats these NULL values as zeros when summing, but for some reason that I'm not entirely sure on, when running the script via VBA it causes it to error out (my guess would be that the warning half-way through the script 'breaks' the VBA call, ultimately causing it to close the dataset; but that is just a guess).
Simply updating the issue line to Sum( IsNull( tl_part.lhrs, 0 ) ) as 'Total Worked Hours' resolves the issue.
TL;DR: The left join of tl_part causes there to be NULL values, these trigger a warning in SMSS when being summed but cause VBA calls to error. I was an idiot and didn't notice the warning message in SMSS as it ran successfully, and I didn't even consider the NULLs caused by the left join.