sql - 运行时错误“3704”关闭对象时不允许操作。VBA 中的 SQL 语句
问题描述
因此，我为其他报告运行相同的 MACRO，但由于某种原因，当我运行此报告时，我收到以下错误：
运行时错误 3704 对象关闭时不允许操作
当我调试时，它会突出显示：
ThisWorkbook.Sheets("Sheet2").Range("A2").CopyFromRecordset rs
从我的宏是：
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, 或一个ForLoop 以保持连接打开直到完全执行，但我不知道。
任何建议都非常感谢。
解决方案
因为您有多个操作查询，包括使用临时表。考虑在 SQL Server 数据库中构建一个存储过程，然后使用ADO 命令从 Excel 调用它。
SQL （strSort 不再连接而是绑定到@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
推荐阅读
- php - Freshdesk API：工单类型
- python - 将值附加和分配给列表有什么区别？
- vba - Excel中命名范围的总和到数组
- https - Google Cloud Global Forwarding：字段资源的值无效。IPAddress
- r - 在 Spotfire 中重置 R 的文本输出
- javascript - 为数组中的相同 td 应用不同的颜色
- php - WordPress插件写出www文件夹
- node.js - 在节点 js for 循环中使用 :nth-child 会引发语法错误
- javascript - 反应应用。无法在尚未安装的组件上调用 setState
- docker - 使用 Docker Centos 7.5 映像运行 GitLab CI 管道时 ChromeHeadless 超时