Query issue - SQL Syntax error?

[alwayslrN]

I used the Macro recorder to help with the what shows below. First in SQL analyzer I created the SQL query, then in Microsoft Excel (2002 SP3), I turned on the recorder, went the External Data chose a database and a table and then bypassed everything to get to the View SQL and pasted the SQL code.

The correct results returned, but when I tried to run the macro to test that it really worked I received a SQL Syntax error and in the debugger the following is in yellow

.Refresh BackgroundQuery:=False

Sub mkQry()
'
' mkQry Macro
' Macro recorded 5/24/2007 by Administrator
'

'
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=SFP_Report_Data_Conversion;UID=nyl_sfp;APP=Microsoft Office XP;WSID=IMN-FMO-TXKV5;DATABASE=SFP_Report_Data_Conversion;Trust" _
        ), Array("ed_Connection=Yes")), Destination:=Range("A4"))
        .CommandText = Array( _
        "SELECT" & Chr(13) & "" & Chr(10) & "    nyl_sfp_poolmast.deal," & Chr(13) & "" & Chr(10) & "    nyl_sfp_poolmast.pool, " & Chr(13) & "" & Chr(10) & "    pool_prin_bal = isnull(b.pool_prin_bal,0) ," & Chr(13) & "" & Chr(10) & "    pool_original_amt = isnull(b.pool_original_amt,0)," & Chr(13) & "" & Chr(10) & "    pool_funding_amt = isnull(" _
        , _
        "b.pool_funding_amt,0)," & Chr(13) & "" & Chr(10) & "    loan_count = isnull(b.loan_count,0)," & Chr(13) & "" & Chr(10) & "    percentage = isnull(b.pool_prin_bal/p.all_pools_prin_bal,0)," & Chr(13) & "" & Chr(10) & "    cur_ltv = isnull(b.cur_ltv,0)," & Chr(13) & "" & Chr(10) & "    gross_rate = isnull(b.gross_" _
        , _
        "rate,0)," & Chr(13) & "" & Chr(10) & "    service_fee = isnull(b.service_fee,0)," & Chr(13) & "" & Chr(10) & "    net_rate = isnull(b.net_rate,0)," & Chr(13) & "" & Chr(10) & "    orig_term = isnull(b.orig_term,0)," & Chr(13) & "" & Chr(10) & "    cur_rterm = isnull(b.cur_rterm,0)," & Chr(13) & "" & Chr(10) & "    cur_age = isnull(b.cur_a")
        .Name = "Query from SFP_Report_Data_Conversion"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Thanks

 

[PHV]

Your SELECT clause is truncated.
Rewrite the .CommandText property without all this Array and Chr stuff.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

For example, once QT is added...

    Dim sSQL As String
        
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  OPS.PARTNO"
    sSQL = sSQL & ", OPS.OPERNUMB"
    sSQL = sSQL & ", OPS.DEPT"
    sSQL = sSQL & ", '' "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM"
    sSQL = sSQL & "  CAP.T_OPER_BASIC OPS "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE OPS.TPLN     ='1'"
    sSQL = sSQL & "  AND OPS.OPERNUMB >='010'"
    sSQL = sSQL & "  AND OPS.PARTNO IN (" & MakeList([Part_Number]) & ")"
'
    Debug.Print sSQL
    
    With wsQueries.QueryTables("qryCAP_OPS")
        .Connection = "ODBC;DSN=Shadow Direct DB2P 32-bit;"
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With

Skip,

_[red][/red]

 

[alwayslrN]

Skip this is my first time doing a Query using VBA, so I am not sure what after QT means.

 

[alwayslrN]

OK - Know QT means query table, what I meant was what do you mean by "once QT added"?

This is what I have now and I get the same error at the same place.

Sub mkQry()
'
' mkQry Macro
' Macro recorded 5/24/2007 by Administrator
'

'
Dim sSQL As String

sSQL = "SELECT"
sSQL = sSQL & "nyl_sfp_poolmast.pool,"
sSQL = sSQL & "nyl_sfp_poolmast.deal,"
sSQL = sSQL & "pool_prin_bal = isnull(b.pool_prin_bal,0),"
sSQL = sSQL & "pool_original_amt = isnull(b.pool_original_amt,0),"
sSQL = sSQL & "pool_funding_amt = isnull(b.pool_funding_amt,0),"
sSQL = sSQL & "loan_count = isnull(b.loan_count,0),"
sSQL = sSQL & "percentage = isnull(b.pool_prin_bal/p.all_pools_prin_bal,0),"
sSQL = sSQL & "cur_ltv = isnull(b.cur_ltv,0),"
sSQL = sSQL & "gross_rate = isnull(b.gross_rate,0),"
sSQL = sSQL & "service_fee = isnull(b.service_fee,0),"
sSQL = sSQL & "net_rate = isnull(b.net_rate,0),"
sSQL = sSQL & "orig_term = isnull(b.orig_term,0),"
sSQL = sSQL & "cur_rterm = isnull(b.cur_rterm,0),"
sSQL = sSQL & "cur_age = isnull(b.cur_age,0),"
sSQL = sSQL & "b.due_day"
sSQL = sSQL & "FROM"
sSQL = sSQL & "SFP_Report_Data_Conversion.dbo.nyl_sfp_poolmast nyl_sfp_poolmast"
sSQL = sSQL & "left outer join (select pool,sum(prin_bal) as pool_prin_bal,"
sSQL = sSQL & "sum(original_amt) as pool_original_amt,"
sSQL = sSQL & "sum(funding_amt) as pool_funding_amt,"
sSQL = sSQL & "round(max(case prin_bal when 0 then 0 else wcltv/prin_bal end),2) as cur_ltv,"
sSQL = sSQL & "round(max(case prin_bal when 0 then 0 else wac/prin_bal end),3) as gross_rate,"
sSQL = sSQL & "round(max(case prin_bal when 0 then 0 else wcsfee/prin_bal end),4) as service_fee,"
sSQL = sSQL & "round(max((case prin_bal when 0 then 0 else wac/prin_bal end)- (case prin_bal when 0 then 0 else wcsfee/prin_bal end)),3) as net_rate,"
sSQL = sSQL & "max(case prin_bal when 0 then 0 else wcoterm/prin_bal end) as orig_term,"
sSQL = sSQL & "max(case prin_bal when 0 then 0 else wcrterm/prin_bal end) as cur_rterm,"
sSQL = sSQL & "max(case prin_bal when 0 then 0 else wage/prin_bal end) as cur_age,"
sSQL = sSQL & "max(case prin_bal when 0 then 0 else wcremit/prin_bal end) as due_day,"
sSQL = sSQL & "count(case when prin_bal != 0 then 1 end) as loan_count,"
sSQL = sSQL & "hist_date"
sSQL = sSQL & "from nyl_sfp_balances"
sSQL = sSQL & "where hist_date = '2007-04-30'"
sSQL = sSQL & "group by pool,hist_date) b on"
sSQL = sSQL & "b.pool = nyl_sfp_poolmast.pool"
sSQL = sSQL & "left outer join (select hist_date,sum(prin_bal) as all_pools_prin_bal"
sSQL = sSQL & "from nyl_sfp_balances"
sSQL = sSQL & "where hist_date = '2007-04-30'"
sSQL = sSQL & "group by hist_date) p on"
sSQL = sSQL & "p.hist_date = '2007-04-30'"
sSQL = sSQL & "ORDER BY"
sSQL = sSQL & "nyl_sfp_poolmast.pool ASC"


    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=SFP_Report_Data_Conversion;UID=nyl_sfp;APP=Microsoft Office XP;WSID=IMN-FMO-TXKV5;DATABASE=SFP_Report_Data_Conversion;Trust" _
        ), Array("ed_Connection=Yes")), Destination:=Range("A4"))
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[alwayslrN]

I found my issue(s) - thanks

 

[SkipVought]

The QueryTables.Add method ADDS a QT to the sheet. It's like ADDING a button; if you add a button 5 time, you get 5 buttons on the sheet.

So what you do is only add your QT ONE TIME. I usually do not use VBA to ADD the QT. Check out your other post for the drill that I use.

If your SQL does not change, you need only use the Refresh method on the QT to get a new resultset.

YourSheetObject.QueryTables(1).Refresh BackgroundQuery:=False

Skip,

_[red][/red]