So I run this same exact MACRO for other reports I do but for some reason when I run this one I am getting back an error of:
Run-Time Error 3704 Operation is not allowed when the object is closed
And when I debug it highlights this:
ThisWorkbook.Sheets("Sheet2").Range("A2").CopyFromRecordset rs
From my macro which is:
Sub Bucket()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim ConnectionString As String
Dim StrQuery As String
strSort = ThisWorkbook.Sheets("Bucket").Cells(2, "A").Value
'ConnectionString = "Provider=SQLOLEDB;" & _
'"Data Source=RFCHaux01\sqlexpress;" & _
'"Initial Catalog=Monthly;" & _
'"Trusted_connection=yes;"
ConnectionString = "Driver={SQL Server Native Client 11.0};Server=RFCHaux01\sqlexpress;Database=Monthly;Trusted_Connection=yes"
cnn.ConnectionTimeout = 0
cnn.CommandTimeout = 0
cnn.Open ConnectionString
'rs.ActiveConnection = cnn
StrQuery = "if exists (select * from tempdb.sys.tables where name like '#check%') drop table #check if exists (select * from tempdb.sys.tables where name like '#check2%') drop table #check2 if exists (select * from tempdb.sys.tables where name like '#finaldata%') drop table #finaldata " & _
"select i.fileno, i.code, substring(i.field,0,5) + substring(i.field,6,2) as 'FILED_MONTH', dense_rank() over(partition by i.fileno order by i.date desc, i.field desc) as 'GARN_GOAL_CHECK', " & _
"row_number() over(partition by i.fileno order by i.date desc, i.code desc) as 'row_check', " & _
"c.court_id, convert(char(2), month(m.date_recd),100) + '/' + convert(char(4),year(m.date_recd),100) as 'MONTH_RECD', m.closed_yy, " & _
"case when m.jmt_amt is null then m.suit_amt else m.jmt_amt end as 'PLACED_AMT' " & _
"into #check from master m join masforw mf on mf.forw_no = m.forw_no join clerkf c on c.venue_no = m.venue1_no join infinity i on i.fileno = m.fileno " & _
"where (i.code in ('*WRT1DT', '*WRT2DT', '*WGRMLD', '*WgGarDT')) " & _
"select i.fileno, i.code, substring(i.field,0,5) + substring(i.field,6,2) as 'FILED_MONTH', " & _
"dense_rank() over(partition by i.fileno order by i.date desc, i.field desc) as 'GARN_GOAL_CHECK', " & _
"row_number() over(partition by i.fileno order by i.date desc, i.code desc) as 'row_check', c.court_id, " & _
"convert(char(2),month(m.date_recd),100) + '/' + convert(char(4),year(m.date_recd),100) as 'MONTH_RECD', " & _
"m.closed_yy, case when m.jmt_amt is null then m.suit_amt else m.jmt_amt end as 'PLACED_AMT' " & _
"into #check2 from master m join masforw mf on mf.forw_no = m.forw_no join clerkf c on c.venue_no = m.venue1_no " & _
"join infinity i on i.fileno = m.fileno where (i.code in ('*WRT3DT','*WRT4DT','*BGRMLD')) " & _
"select distinct m.fileno as 'File Number', mf.sort_field as 'sort field', m.forw_no as 'Port', inside.amt_recvd, inside.trans_date, case when m.Source <> 'HISTORY\HISTORY' then 'Open' else 'Close' end as 'Status', ii4.field as 'Pooltype', " & _
"m.forw_refno as 'Unifund File Number', m.orig_claim as 'Original Amount', d.st as 'Debtor State', c.cnty_name as 'County Filed In', m.date_recd as 'Recieved Date', m.suit_date as 'Suit Date', m.jmt_date as 'Judgement Date', " & _
"m.statute_date as 'Statute of Limitation Date', Convert(VARCHAR(50),Convert(Datetime, i.field),101) as 'Suit to Court', Convert(VARCHAR(50),Convert(Datetime, i1.field),101) as 'Resent for Service', Convert(VARCHAR(50),Convert(Datetime, ii1.field),101) as 'Served', " & _
"i2.field as 'Suit Worthiness', i3.field as 'SW Type', d.fico_score as 'Credit Score', i4.field as 'Triple Match', i5.field as 'Place Status', i6.field as 'Prior Status', case when g.code = '*WGRMLD' then 'GARN' when g.code = '*WgGarDT' then 'GARN' " & _
"when g.code = '*WRT1DT' then 'GARN' when g.code = '*WRT2DT' then 'GARN' else '' end as 'Garn', case when g2.code = '*BGRMLD' then 'LEVY' when g2.code = '*WRT3DT' then 'LEVY' when g2.code = '*WRT4DT' then 'LEVY' else '' end as 'LEVY', i7.field as 'Verification Date 1', i9.field as 'Verification Of', i8.field as 'Verification Date 2', ii.field as 'Verification', m.charge_off_date, ii2.field as 'Grade', ii3.field as 'Grade D Overages', (m.int_coll + m.cost_recovered + m.prin_coll) as 'Collected' into #finaldata from master m left join(SELECT c1.fileno, c1.amt_recvd, c1.trans_date FROM colbills C1 WHERE trans_date = (SELECT MIN(trans_date) FROM colbills c2 where c1.FILENO = c2.FILENO AND c2.amt_recvd > 0 AND c2.amt_recvd IS NOT NULL)and AMT_RECVD = (select top 1 amt_recvd from colbills " & _
"where c1.fileno = fileno) and c1.AMT_RECVD > 0 group by c1.fileno, c1.AMT_RECVD, c1.TRANS_DATE) inside on inside.fileno = m.FILENO join masforw mf on mf.forw_no = m.forw_no join debtor d on d.fileno = m.fileno and d.number = 1 join clerkf c on c.venue_no = m.venue1_no left join infinity i on i.fileno = m.fileno and i.code = '*SCTOCT' left join infinity ii4 on ii4.fileno = m.fileno and ii4.code = '*AssetTp' left join infinity i1 on i1.fileno = m.fileno and i1.code = '*SCRSDT' left join infinity i2 on i2.fileno = m.fileno and i2.code = '*SUITREC' left join infinity i3 on i3.fileno = m.fileno and i3.code = '*SRVWFTY' left join infinity i4 on i4.fileno = m.fileno and i4.code = '*ADDMTCH' left join infinity i5 on i5.fileno = m.fileno and i5.code = '*FWCTYPE' left join infinity i6 on i6.fileno = m.fileno and i6.code = '*PRSTCOD' left join infinity i7 on i7.fileno = m.fileno and i7.code = '*PLVpD1' left join infinity i9 on i9.fileno = m.fileno and i9.code = '*PLVPN1' " & _
"left join infinity ii on ii.fileno = m.fileno and ii.code = '*PLVPN2' left join infinity i8 on i8.fileno = m.fileno and i8.code = '*PLVpD2' left join infinity ii1 on ii1.fileno = m.fileno and ii1.code = '*AMDTSV' left join infinity ii2 on ii2.fileno = m.fileno and ii2.code = '*SuitGrd' left join infinity ii3 on ii3.fileno = m.fileno and ii3.code = '*RiskGrd' left join #check g on g.fileno = m.fileno left join #check2 g2 on g2.fileno = m.fileno where (mf.sort_field = '" & strSort & "') and (m.fileno not like 'Z%' and m.fileno not like 'FOW%' ) " & _
"select f.* from #finaldata f where f.[Prior Status] <> 'RENEWED' or f.[Prior Status] is null " & _
"drop table #check drop table #check2 drop table #finaldata "
rs.Open StrQuery, cnn
ThisWorkbook.Sheets("Sheet2").Range("A2").CopyFromRecordset rs
End Sub
I'm assuming that this could be because of the recordset? And maybe my query is too complex so it times out before anything happens?
I was reading somewhere that when a query is this large that I have to do a DoEvents, or a For Loop to keep the connection open till full execution but I have no idea.
Any suggestions greatly appreciated.
Because you have multiple action queries including use of temp tables. Consider build a stored procedure inside the SQL Server database and then call it from Excel with ADO Command.
SQL (strSort no longer concatenated but binded to @param placeholder)
CREATE PROCEDURE myStoredProcedure @param VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
if exists (select * from tempdb.sys.tables where name like '#check%')
drop table #check
if exists (select * from tempdb.sys.tables where name like '#check2%')
drop table #check2
if exists (select * from tempdb.sys.tables where name like '#finaldata%')
drop table #finaldata
select i.fileno, i.code, substring(i.field,0,5) + substring(i.field,6,2) as 'FILED_MONTH',
dense_rank() over(partition by i.fileno order by i.date desc, i.field desc) as 'GARN_GOAL_CHECK',
row_number() over(partition by i.fileno order by i.date desc, i.code desc) as 'row_check',
c.court_id, convert(char(2), month(m.date_recd),100) + '/' + convert(char(4),year(m.date_recd),100) as 'MONTH_RECD', m.closed_yy,
case when m.jmt_amt is null then m.suit_amt else m.jmt_amt end as 'PLACED_AMT'
into #check
from master m
join masforw mf on mf.forw_no = m.forw_no
join clerkf c on c.venue_no = m.venue1_no
join infinity i on i.fileno = m.fileno
where (i.code in ('*WRT1DT', '*WRT2DT', '*WGRMLD', '*WgGarDT'))
select i.fileno, i.code, substring(i.field,0,5) + substring(i.field,6,2) as 'FILED_MONTH',
dense_rank() over(partition by i.fileno order by i.date desc, i.field desc) as 'GARN_GOAL_CHECK',
row_number() over(partition by i.fileno order by i.date desc, i.code desc) as 'row_check', c.court_id,
convert(char(2),month(m.date_recd),100) + '/' + convert(char(4),year(m.date_recd),100) as 'MONTH_RECD',
m.closed_yy, case when m.jmt_amt is null then m.suit_amt else m.jmt_amt end as 'PLACED_AMT'
into #check2
from master m
join masforw mf on mf.forw_no = m.forw_no
join clerkf c on c.venue_no = m.venue1_no
join infinity i on i.fileno = m.fileno
where (i.code in ('*WRT3DT','*WRT4DT','*BGRMLD'))
select distinct m.fileno as 'File Number', mf.sort_field as 'sort field', m.forw_no as 'Port',
inside.amt_recvd, inside.trans_date,
case when m.Source <> 'HISTORY\HISTORY' then 'Open' else 'Close' end as 'Status',
ii4.field as 'Pooltype', m.forw_refno as 'Unifund File Number', m.orig_claim as 'Original Amount',
d.st as 'Debtor State', c.cnty_name as 'County Filed In', m.date_recd as 'Recieved Date',
m.suit_date as 'Suit Date', m.jmt_date as 'Judgement Date',
m.statute_date as 'Statute of Limitation Date',
Convert(VARCHAR(50),Convert(Datetime, i.field),101) as 'Suit to Court',
Convert(VARCHAR(50),Convert(Datetime, i1.field),101) as 'Resent for Service',
Convert(VARCHAR(50),Convert(Datetime, ii1.field),101) as 'Served',
i2.field as 'Suit Worthiness', i3.field as 'SW Type', d.fico_score as 'Credit Score',
i4.field as 'Triple Match', i5.field as 'Place Status', i6.field as 'Prior Status',
case when g.code = '*WGRMLD' then 'GARN'
when g.code = '*WgGarDT' then 'GARN'
when g.code = '*WRT1DT' then 'GARN'
when g.code = '*WRT2DT' then 'GARN'
else '' end as 'Garn',
case when g2.code = '*BGRMLD'
then 'LEVY' when g2.code = '*WRT3DT'
then 'LEVY' when g2.code = '*WRT4DT'
then 'LEVY' else '' end as 'LEVY', i7.field as 'Verification Date 1',
i9.field as 'Verification Of', i8.field as 'Verification Date 2',
ii.field as 'Verification', m.charge_off_date, ii2.field as 'Grade',
ii3.field as 'Grade D Overages', (m.int_coll + m.cost_recovered + m.prin_coll) as 'Collected'
into #finaldata
from master m
left join
(SELECT c1.fileno, c1.amt_recvd, c1.trans_date
FROM colbills C1
WHERE trans_date = (SELECT MIN(trans_date)
FROM colbills c2
where c1.FILENO = c2.FILENO
AND c2.amt_recvd > 0 AND c2.amt_recvd IS NOT NULL)
and AMT_RECVD = (select top 1 amt_recvd
from colbills
where c1.fileno = fileno)
and c1.AMT_RECVD > 0 group by c1.fileno, c1.AMT_RECVD, c1.TRANS_DATE) inside
on inside.fileno = m.FILENO
join masforw mf on mf.forw_no = m.forw_no
join debtor d on d.fileno = m.fileno and d.number = 1
join clerkf c on c.venue_no = m.venue1_no
left join infinity i on i.fileno = m.fileno and i.code = '*SCTOCT'
left join infinity ii4 on ii4.fileno = m.fileno and ii4.code = '*AssetTp'
left join infinity i1 on i1.fileno = m.fileno and i1.code = '*SCRSDT'
left join infinity i2 on i2.fileno = m.fileno and i2.code = '*SUITREC'
left join infinity i3 on i3.fileno = m.fileno and i3.code = '*SRVWFTY'
left join infinity i4 on i4.fileno = m.fileno and i4.code = '*ADDMTCH'
left join infinity i5 on i5.fileno = m.fileno and i5.code = '*FWCTYPE'
left join infinity i6 on i6.fileno = m.fileno and i6.code = '*PRSTCOD'
left join infinity i7 on i7.fileno = m.fileno and i7.code = '*PLVpD1' left join infinity i9 on i9.fileno = m.fileno and i9.code = '*PLVPN1'
left join infinity ii on ii.fileno = m.fileno and ii.code = '*PLVPN2'
left join infinity i8 on i8.fileno = m.fileno and i8.code = '*PLVpD2'
left join infinity ii1 on ii1.fileno = m.fileno and ii1.code = '*AMDTSV'
left join infinity ii2 on ii2.fileno = m.fileno and ii2.code = '*SuitGrd'
left join infinity ii3 on ii3.fileno = m.fileno and ii3.code = '*RiskGrd'
left join #check g on g.fileno = m.fileno
left join #check2 g2 on g2.fileno = m.fileno
where (mf.sort_field = @param)
and (m.fileno not like 'Z%' and m.fileno not like 'FOW%' )
select f.* from #finaldata f where f.[Prior Status] <> 'RENEWED' or f.[Prior Status] is null
GO
END
VBA
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As New ADODB.Command
Dim ConnectionString As String, StrSort As String, StrQuery As String
StrSort = ThisWorkbook.Sheets("Bucket").Cells(2, "A").Value
ConnectionString = "Driver={SQL Server Native Client 11.0};Server=RFCHaux01\sqlexpress;Database=Monthly;Trusted_Connection=yes"
cnn.ConnectionTimeout = 0
cnn.CommandTimeout = 0
cnn.Open ConnectionString
With cmd
.ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "[myStoredProcedure]"
.Parameters.Append .CreateParameter("@param", adVarChar, adParamInput, 255, StrSort)
End With
Set rs = cmd.Execute
ThisWorkbook.Sheets("Sheet2").Range("A2").CopyFromRecordset rs
rs.Close: cnn.Close
Set rs = Nothing: Set cmd = Nothing: Set cnn = Nothing