sql - 运行时错误 '3704' 关闭对象时不允许进行操作。 VBA 中的 SQL 语句

Question

因此，我为其他报告运行了同样的宏，但由于某种原因，当我运行这个报告时，我收到了一个错误:

Run-Time Error 3704 Operation is not allowed when the object is closed

当我调试时它会突出显示这一点:

ThisWorkbook.Sheets("Sheet2").Range("A2").CopyFromRecordset rs

来 self 的宏:

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

我假设这可能是因为记录集？也许我的查询太复杂，所以在发生任何事情之前就超时了？

我在某处读到，当查询如此大时，我必须执行 DoEvents 或 For 循环来保持连接打开直到完全执行，但我不知道。

非常感谢任何建议。

Answer 1

因为您有多个操作查询，包括使用临时表。考虑在 SQL Server 数据库内构建一个存储过程，然后使用 ADO Command 从 Excel 调用它。 .

SQL (strSort 不再连接，而是绑定(bind)到 @param 占位符)

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

<小时/>