Excel VBA Runtime 1004 error Refresh BackgroundQuery:=False

Question

I have an excel macro which get the data from teradata. it used to work well, but after i adjusted some SQL code (the code is longer and more complexity). it error. The error showed

Run-time error '1004 : Application-defined or object-defined error.

It was error at

.Refresh BackgroundQuery:=False

I have tried many solutions here, but still did not work. Here is my code.

Sub ConDB()
Dim conn As ADODB.Connection
Dim rec1 As ADODB.Recordset
Dim thisSql As String
Dim DATE_PARAM As String
DATE_PARAM = Format(Sheets("Summary").Range("B1").Value, "yyyy-mm-dd")
Set conn = New ADODB.Connection
conn.CommandTimeout = 0


conn.Open "Driver=Teradata; DBCName=" & "111.111.11.111" & ";UID=" & "myusername" & ";PWD=" & "Password"
Sheets("Data").Select
 Sheets("Data").Cells.Select
Selection.Delete Shift:=xlUp



thisSql = "select * from (select * from (select CAST('" & DATE_PARAM & "'  AS DATE )as asofdate) A "
thisSql = thisSql & "union select * from (select CAST('" & DATE_PARAM & "' AS DATE) -1  as asofdate) B "
thisSql = thisSql & "union select * from (select CAST('" & DATE_PARAM & "' AS DATE)-2  as asofdate) C "
thisSql = thisSql & "union select * from (select CAST('" & DATE_PARAM & "' AS DATE)-3  as asofdate) D "
thisSql = thisSql & "union select * from (select CAST('" & DATE_PARAM & "' AS DATE)-4  as asofdate) E "
thisSql = thisSql & "union select * from (select CAST('" & DATE_PARAM & "' AS DATE)-5  as asofdate) F "
thisSql = thisSql & "union select * from (SELECT ADD_MONTHS(CAST ('" & DATE_PARAM & "' AS DATE) - EXTRACT(DAY FROM CAST('" & DATE_PARAM & "' AS DATE) ), 0 ) as asofdate ) G "
thisSql = thisSql & "union select * from (SELECT ADD_MONTHS(CAST ('" & DATE_PARAM & "' AS DATE) - EXTRACT(DAY FROM CAST('" & DATE_PARAM & "' AS DATE) ), -1 ) as asofdate ) H "
thisSql = thisSql & "union select * from (SELECT ADD_MONTHS(CAST ('" & DATE_PARAM & "' AS DATE) - EXTRACT(DAY FROM CAST('" & DATE_PARAM & "' AS DATE) ), -2 ) as asofdate ) I "
thisSql = thisSql & "Union select * from ( SELECT  (CASE      WHEN   EXTRACT(MONTH FROM (CAST('" & DATE_PARAM & "' AS DATE))) BETWEEN 1 AND 3 THEN (CAST('" & DATE_PARAM & "'AS DATE)/10000*10000 )+101"
thisSql = thisSql & "WHEN   EXTRACT (MONTH FROM (CAST ('" & DATE_PARAM & "' AS DATE))) BETWEEN 4 AND 6 THEN (CAST('" & DATE_PARAM & "'AS DATE)/10000*10000 )+401 "
thisSql = thisSql & " WHEN   EXTRACT (MONTH FROM (CAST ('" & DATE_PARAM & "'AS DATE))) BETWEEN 7 AND 9 THEN (CAST('" & DATE_PARAM & "' AS DATE)/10000*10000 )+701"
thisSql = thisSql & " WHEN  EXTRACT (MONTH FROM (CAST ('" & DATE_PARAM & "'AS DATE))) BETWEEN 10 AND 12 THEN (CAST('" & DATE_PARAM & "' AS DATE)/10000*10000 )+1001 END )-1 AS asofdate) J "
thisSql = thisSql & "union select * from ( select cast( trim( extract( year from CAST('" & DATE_PARAM & "' AS DATE))-1 ) || '-12-31' as DATE ) as asofdate ) J) cal_date "
thisSql = thisSql & "inner join (select     BRANCH, CIF_NO ,   sum (PRINCIPAL)  as PRINCIPAL, sum (UNEARN_PRIN)  as  UNEARN_PRIN,   sum (PRINTNET)  as PRINTNET "
thisSql = thisSql & ",ACCRU,   LOAN_PRIN_DESC,   dept_tran,   gl_code,   loan_type,   CURR_DATE,   START_DATE ,   END_DATE from  "
thisSql = thisSql & "(select     BRANCH,    CIF_NO  AS CIF_NO,   PRINCIPAL AS PRINCIPAL,    UNEARN_PRIN AS UNEARN_PRIN"
thisSql = thisSql & ",CASE WHEN  (PRINCIPAL- UNEARN_PRIN) < 0 THEN 0 "
thisSql = thisSql & "ELSE  (PRINCIPAL- UNEARN_PRIN) "
thisSql = thisSql & "END AS  PRINTNET "
thisSql = thisSql & ",ACCRU AS ACCRU "
thisSql = thisSql & ",CASE   when LOAN_PRIN_TYPE = 'C' THEN 'CASH' "
thisSql = thisSql & "   when LOAN_PRIN_TYPE = 'N' THEN 'NON_CASH' "
thisSql = thisSql & "   when LOAN_PRIN_TYPE is null and loan_type in ('LGCM','PN')  THEN 'CASH' "
thisSql = thisSql & "   when LOAN_PRIN_TYPE is null and appl = 'PNS'  THEN 'CASH' "
thisSql = thisSql & "   ELSE 'NON_CASH'  "
thisSql = thisSql & "   END AS LOAN_PRIN_DESC "
thisSql = thisSql & ",   dept_tran "
thisSql = thisSql & ",   gl_code "
thisSql = thisSql & ",   loan_type "
thisSql = thisSql & ",  '2017-05-31'  as CURR_DATE "
thisSql = thisSql & ",   START_DATE  "
thisSql = thisSql & ",   END_DATE "
thisSql = thisSql & " from EDWPRD_SEMVRS_IMGCIM.CIM_CIMD131 A "
thisSql = thisSql & " where dept_tran not in ('20100','02400','44000','08300','03600','20300','43000','02200','00100','20200','07000','07001','07002','05400','24700','00200','59500','06300','43400' /*CB*/ "
thisSql = thisSql & ",'29400' /* Auto*/  ,'55400'  /*ADD_Metropolitan*/ ,'55500' /*ADD_Provincial */ ,'23500' /*NPO*/,'23400' /*IB*/ "
thisSql = thisSql & ",'12200','26400','70100','70200','70300','26500','70400','70500','70600','26600','70700','70800','70900','26700','71000','71100','71200','26800','71300','71400' /*JMC*/ "
thisSql = thisSql & ",'00600' /*HR*/,'08700','00988','03607','03608','03609','03610' /*NPL*/ ) "
thisSql = thisSql & "/*comment here*/ "
thisSql = thisSql & "and   GL_CODE NOT IN ('0001310541','0001310519','0001310527','0001310528', "
thisSql = thisSql & "'0001310535','0001310537','0001310539','0001310515','0001310546','0001390110', "
thisSql = thisSql & "'0001310341','0001310353','0001310354','0001310343','0001320111','0001320123', "
thisSql = thisSql & "'0005010011','0001370010','0001370040','0003810353','0003870010','0003870040') "
thisSql = thisSql & "/*äÁèàÍÒ Loan_type µÒÁ´éÒ¹ÅèÒ§*/ "
thisSql = thisSql & "AND LOAN_TYPE NOT IN ('OD01','OD07','OD08','CA1','CA07','CA08') "
thisSql = thisSql & ")D131 "
thisSql = thisSql & "group by    BRANCH,    CIF_NO, ACCRU,   LOAN_PRIN_DESC,   dept_tran,   gl_code,   loan_type,   CURR_DATE,   START_DATE ,   END_DATE)AA "
thisSql = thisSql & "on cal_date.asofdate between AA.start_date and AA.end_date AND CURR_DATE  BETWEEN START_DATE AND END_DATE "
thisSql = thisSql & "--and start_date  >='2016-12-25' ;"

Sheets("Sheet1").Range("A1").Value = thisSql

Set rec1 = New ADODB.Recordset
rec1.Open thisSql, conn
Sheets("Data").Select
With Sheet1.QueryTables.Add(Connection:=rec1, Destination:=Sheet1.Range("A1"))
    .Name = "data"
    .FieldNames = True
    **.Refresh BackgroundQuery:=False**
End With

'Refresh Pivot
   Sheets("Summary").Select
   ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

Thanks for your kind help and support in advance.

Take a look at this solution: https://stackoverflow.com/questions/39027286/how-to-reference-and-refresh-a-querytable-in-excel-2016-in-vba — Victor K, Sep 19 '17 at 14:21
yes it correct . I have tested the query and with parameter value before. Stange code is my comment in Thai. — Siroros Roongdonsai, Sep 19 '17 at 17:46

Excel VBA Runtime 1004 error Refresh BackgroundQuery:=False

0 Answers0