Ошибка времени выполнения «3704» операция не разрешена, когда объект закрыт. оператор sql в vba

Поэтому я запускаю тот же самый МАКРОС для других отчетов, но по какой-то причине, когда я запускаю этот, я получаю сообщение об ошибке:

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

И когда я отлаживаю, он выделяет это:

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 или цикл For, чтобы соединение оставалось открытым до полного выполнения, но я понятия не имею.

Любые предложения приветствуются.

Каковы значения rs.EOF и rs.BOF, когда он выходит из строя?

Tim Williams 13.09.2018 20:41

Я даже не получаю для этого значений, я получаю то же сообщение об ошибке

Pr0x1mo 13.09.2018 22:15

@TimWilliams, когда я выполняю обработку ошибок, я получаю: ADODB.Recordset Операция не разрешена, когда объект закрыт.

Pr0x1mo 13.09.2018 22:17
2
3
2 224
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Потому что у вас есть несколько запросов к действию, включая использование временных таблиц. Рассмотрите возможность создания хранимой процедуры в базе данных SQL Server, а затем вызовите ее из Excel с помощью Команда ADO.

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

Итак, это решение было бы лучше, чем предыдущее, которое вы опубликовали, я полагаю?

Pr0x1mo 13.09.2018 22:40

Кроме того, с этим мне не пришлось бы отбрасывать все временные таблицы в конце?

Pr0x1mo 13.09.2018 22:47

Все, что вам подходит! У вас или будущих читателей может не быть права изменять базу данных. И производительность может быть разной. Временные таблицы удаляются после сеанса. Так что это были лишние линии.

Parfait 13.09.2018 22:50

И последнее ... как в этой процедуре объявить @ -param? Я попытался поместить это в хранимую процедуру как Declare @ -param VarChar (50); но vba вернул мне ошибку, сказав, что myStoredProcedure не имеет параметров

Pr0x1mo 13.09.2018 23:02

Не объявляйте параметр. Передайте @param снаружи по линии CREATE PROCEDURE. См. Редактировать.

Parfait 14.09.2018 00:04

Превосходно! Рад помочь. Как насчет версии CTE?

Parfait 14.09.2018 16:57

они оба отлично работают. Я выбираю его, чтобы он всегда был в базе данных, а не в sql-файле, который можно случайно удалить. Кроме того, похоже, что хранимая процедура выполняется быстрее, чем файл .sql. Я где-то читал, что это одна из причин использовать sp, потому что он быстрее (я думаю, из кеша из предыдущего запуска?)

Pr0x1mo 14.09.2018 18:11

Отличный ответ! Часто рекомендуется запускать длинные процессы в сохраненных процессах, как вы упоминаете, для безопасности и эффективности вашей бизнес-логики. И база данных сохраняет статистику своих объектов.

Parfait 14.09.2018 18:52

Рассмотрите возможность замены использования временных таблиц для операторов С КТР для набора результатов вывода один.

SQL(сохраните как длинный форматированный текстовый файл .sql для чтения в VBA)

WITH [check] AS (
    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' 
    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')) 
),

WITH [check2] AS (    
    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' 
    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')) 
),

WITH finaldata AS (    
    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'  
    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 = ?) 
      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 

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
Dim i As Integer

' READ SQL QUERY FROM FILE
With CreateObject("Scripting.FileSystemObject")
      StrQuery = .OpenTextFile("C:\path\to\mySQLQuery.sql", 1).readall
End With

' OPEN DB CONNECTION
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

' DEFINE COMMAND OBJECT
With cmd
    .ActiveConnection = cnn
    .CommandType = adCmdText
    .CommandText = StrQuery
    .Parameters.Append .CreateParameter("@param", adVarChar, adParamInput, 255, StrSort)
End With

' BIND OUTPUT TO RECORDSET
Set rs = cmd.Execute

' COPY TO WORKSHEET
With ThisWorkbook.Sheets("Sheet2")
    ' COLUMNS
    For i = 1 To rs.Fields.Count
       .Cells(1, i) = rs.Fields(i - 1).Name
    Next i 
    ' DATA
   .Range("A2").CopyFromRecordset rs
End With

rs.Close: cnn.Close
Set rs = Nothing: Set cmd = Nothing: Set cnn = Nothing

Я также попробовал это с этим, и я получаю сообщение об ошибке, когда запускаю макрос: Должен объявить скалярную переменную "@ param"

Pr0x1mo 13.09.2018 23:17

Замените "@param" в запросе SQL на ? (qmark без кавычек). См. Редактировать.

Parfait 14.09.2018 00:02

Другие вопросы по теме