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
Thanks
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
Code:
.Refresh BackgroundQuery:=False
Code:
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