Can Excel run this big SQL?

Can Excel run this?

Code:

---

SELECT Sum(Data.PBO) AS SumOfPBO, Sum(Data.AccruedInterest) AS SumOfAccruedInterest, Sum(IIf([Status]="Claims" Or [Status]="Forbearance",[AccruedInterest],IIf([Status]="Repayment",0,IIf([SubsidyIndicator]="N",[AccruedInterest],0)))) AS ITBC, Sum(IIf([ABI]=0,0,[pbo]/[ABI])) AS Borr, Count(Data.BorrowerUniqueID) AS Loans, Sum([PBO]*[RemainingRepaymentTerm])/Sum([PBO]) AS RepayTerm, Sum([pbo]*[InterestRate])/Sum([PBO]) AS IntRate
FROM Data LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID
HAVING (((Data.PBO)<>0));

---

I just need to know if it's possible because it either returns an error in Select statment syntax or returns zero.

I'm using this:

Code:

---

Set cn = New ADODB.Connection
        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Open MyConn
            Set rs = .Execute(sSQL)
        End With

---

Cross-thread: http://www.excelfox.com/forum/f2/can...1417/#post6658

Its not much clear to me, Pelase provide details description of error you get and at what point you get an error connecting or executing SQL.
Still try using this one

Code:

---

Set RS = new ADODB.Recordset
RS.Open SQL, cn

---

Break your sSQL into parts. e.g.
sSQL = "Select * from Data"

When you build your SQL string, use:

Code:

---

Debug.Print sSQL

---

to see if you built it properly. Most SQL string comparisons use single quotes rather than double quotes. e.g.

Code:

---

sSQL = "SELECT * FROM Products WHERE ProductName = 'Watch' "

---

I have definitely been changing them to single quotes when I run it, but the debug idea is great! Thanks for the advice!

Quote:

---

sSQL = "SELECT Sum(Data.PBO) AS SumOfPBO, Sum(Data.AccruedInterest) AS SumOfAccruedInterest, Sum"
sSQL = sSQL & "(IIf([Status]='Claims' Or [Status]='Forbearance',[AccruedInterest],IIf([Status]='Repayment"
sSQL = sSQL & "',0,IIf([SubsidyIndicator]='N',[AccruedInterest],0)))) AS ITBC, Sum(IIf([ABI]=0,0,[pbo]/[ABI]"
sSQL = sSQL & ")) AS Borr, Count(Data.BorrowerUniqueID) AS Loans, Sum([PBO]*[RemainingRepaymentTerm])/Sum([PBO"
sSQL = sSQL & "]) AS RepayTerm, Sum([pbo]*[InterestRate])/Sum([PBO]) AS IntRate"
sSQL = sSQL & "FROM Data LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID"
sSQL = sSQL & "HAVING (((Data.PBO)<>0));"




Debug.Print sSQL

---

Got this error:

The Select statement includes a reserved word or an arguement name that is misspelled or missing, or the punctuation is incorrect

The string is fine as far as strings go. You might want to add some vbCRLF to make it more readable like a standard SQL string.

Whether it meets SQL syntax standards is where you have problems I suspect. I don't know what database you are trying to connect to. If you can make the SQL string there, and it runs ok, post that string here and I will show you how to build it.

The code is copy and pasted from Access, it runs the query on Access fine.

I'm looking up what vbCRLFs are, I'm not sure what that is.

[]'s can indicate an Excel range, which also means Evaluate in Excel, or a parameter query. I suspect you mean the latter. I will show an example for doing that later. First, I want to show you how to build a string efficiently. Concatenation as you did is fine for small string. This method also makes it easy to maintain. The vbCRLF is the carriage return and line feed characters. Press F1 while in or next to a constant or command word to get specific help in the Visual Basic Editor (VBE).

In the 1st example code, I join the array elements back into a string and delimit by the comma character. I prefer delimiting by the vbCRLF character(s) or vbLF usually.

The 3rd example shows how to run a stored query.

Code:

---

Sub sql()
 Dim sSQL As String, s() As String

  'SELECT Sum(Data.PBO) As SumOfPBO,
  'Sum(Data.AccruedInterest) As SumOfAccruedInterest,
  'Sum(IIf([Status]="Claims" Or [Status]="Forbearance",
  '[AccruedInterest],IIf([Status]="Repayment",0,
  'IIf([SubsidyIndicator]="N",[AccruedInterest],0)))) As ITBC,
  'Sum(IIf([ABI]=0,0,[pbo]/[ABI])) As Borr, Count(Data.BorrowerUniqueID) As Loans,
  'Sum([PBO]*[RemainingRepaymentTerm])/Sum([PBO]) As RepayTerm,
  'Sum([pbo]*[InterestRate])/Sum([PBO]) As IntRate FROM Data LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID HAVING (((Data.PBO)<>0));
 
  ReDim s(1 To 8)
  s(1) = "SELECT Sum(Data.PBO) As SumOfPBO"
  s(2) = "Sum(Data.AccruedInterest) As SumOfAccruedInterest"
  s(3) = "Sum(IIf([Status]='Claims' Or [Status]='Forbearance'"
  s(4) = "[AccruedInterest],IIf([Status]='Repayment',0"
  s(5) = "IIf([SubsidyIndicator]='N',[AccruedInterest],0)))) As ITBC"
  s(6) = "Sum(IIf([ABI]=0,0,[pbo]/[ABI])) As Borr, Count(Data.BorrowerUniqueID) As Loans"
  s(7) = "Sum([PBO]*[RemainingRepaymentTerm])/Sum([PBO]) As RepayTerm"
  s(8) = "Sum([pbo]*[InterestRate])/Sum([PBO]) As IntRate FROM Data LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID HAVING (((Data.PBO)<>0));"
 
  sSQL = Join(s(), ",")
  Debug.Print sSQL
  MsgBox sSQL
End Sub

---

Code:

---

'http://www.excelkb.com/article.aspx?id=10091&cNode=1I7C3V
Sub Execute_Parameterized_SQL_Queries()
  Dim cnt As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim prm As ADODB.Parameter
  Dim rst As ADODB.RecordSet
  Dim stDB As String, stCon As String, stParamSQL As String
  Dim vaNumber As Variant
  Dim xlCalc As XlCalculation

  'Change settings in order to increase the performance.
  With Application
    xlCalc = .Calculation
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
  End With

  'Path to the database.
  stDB = "E:\Arbetsmaterial\XLData1.mdb"

  'Create the connection string.
  stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & stDB & ";" & _
            "Jet OLEDB:Database Password=dennis;" & _
            "Persist Security Info=False"

  'Create the parameter string.
  stParamSQL = "SELECT * FROM tblData WHERE [Number]=?"

  'Instantiate the ADO COM's objects.
  Set cnt = New ADODB.Connection
  Set cmd = New ADODB.Command

  'Open the connection.
  cnt.Open stCon

  'Set main properties of the command object.
  With cmd
      .ActiveConnection = cnt
      .CommandText = stParamSQL
      .CommandType = adCmdText
  End With

  'Retrieve the value to be used.
  vaNumber = Trim(Application.InputBox(Prompt:="Please enter an article number:", _
  Title:="Parameterized Queries", Type:=1))

  'In the user cancel the operation.
  If vaNumber = "False" Then GoTo Exithere

  'Create and define the parameter.
  Set prm = cmd.CreateParameter("Number", adInteger, adParamInput, Len(vaNumber), vaNumber)

  cmd.Parameters.Append prm

  'Instantiate the Recordset object and execute the command.
  Set rst = cmd.Execute
 
  'Check if the Recordset have any records.
  If rst.EOF = True Or rst.BOF = True Then
      MsgBox "No records match the number!", vbInformation
      GoTo Exithere
      Else
      'Dump the records into the active worksheet.
      With ActiveSheet.Cells(2, 1)
        .CurrentRegion.ClearContents
        .CopyFromRecordset rst
      End With
  End If

  'Close the recordset and the connection.
  rst.Close
  cnt.Close
Exithere:
 
  'Release objects from memory.
  Set rst = Nothing
  Set prm = Nothing
  Set cmd = Nothing
  Set cnt = Nothing
 
  'Restore the settings.
  With Application
    .Calculation = xlCalc
    .EnableEvents = True
    .ScreenUpdating = True
  End With
End Sub

---

Code:

---

'http://www.vbaexpress.com/forum/showthread.php?t=24118
'http://www.vbaexpress.com/forum/showthread.php?t=24575
'http://www.vbaexpress.com/forum/showthread.php?t=23783
'http://www.vbaexpress.com/forum/showthread.php?t=26145
Sub Test()
  Dim mdbPath As String, dbName As String, cmdText As String
  Dim rngDestination As String
  'mdbPath = "E:\ADO\NWind2003.mdb"    'change the path here to suit your needs
  'mdbPath = "c:\myfiles\edrive\excel\ado\NWind2003.mdb"
  mdbPath = "//matpc10/ExcelVBAExamples/ado/NWind2003.mdb"
  dbName = "NWind2003_1"              'change the database name here to suit your needs
  cmdText = "Aug94"                  'change the stored SQL here to suit your needs
  rngDestination = "A1"              'change the destination range here to suit your needs
 
  'Clear previous data
  Cells.Delete

  InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
 
  'Insert other data to the right of A1 with a blank column separating the two
  rngDestination = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 2).Address
  cmdText = "Sales by Category"
  InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
End Sub

' http://www.vbaexpress.com/forum/showthread.php?t=43307
Sub Test_InsertTableWithStoredSQL()
  Dim databaseName() As Variant, cmdText() As Variant, i As Integer
  '
  databaseName = Array("qOne", "qTwo", "qThree")
  cmdText() = Array("Aug94", "Order Subtotals", "Sales by Category")
     
  For i = LBound(databaseName) To UBound(databaseName)
    Debug.Print databaseName(i), cmdText(i), Range("A" & Rows.Count).End(xlUp).Offset(1).Address
      InsertTableWithStoredSQL "c:\myfiles\edrive\excel\ado\NWind2003.mdb", _
        CStr(databaseName(i)), CStr(cmdText(i)), Range("A" & Rows.Count).End(xlUp).Offset(1).Address, _
        True
  Next i
End Sub

Sub InsertTableWithStoredSQL(mdbPath As String, dbName As String, _
  cmdText As String, rngDestination As String, _
  Optional bFieldNames = True)

    With ActiveSheet.QueryTables.Add(Connection:=Array( _
                                                "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & mdbPath & ";Mode=ReadWrite;Extended Properties=""" _
      , """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab" _
      , "ase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";J" _
      , "et OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Co" _
      , "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("" & rngDestination & ""))
        .CommandType = xlCmdTable
        .CommandText = Array(cmdText)
        .Name = dbName
        .FieldNames = bFieldNames
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = mdbPath
        .Refresh BackgroundQuery:=False
    End With
End Sub

---

Thank you for the reply! Great, It will take me a bit of deciphering, but that should be perfect. Thanks!

I've been trying your code, and still having some problems.

So I'm under the impression that an SQL statement with []'s won't be able to run inside Excel ADO statement because it thinks it's referring to a cell and not a command. Is this correct?

If their is a way to still use the brackets in my SQL statement I am not understanding very clearly.

[A1] means evaluate A1 as a range. "[A1]" is just a string. Your SQL server determines how it will work with []'s. The typical use for []'s in an SQL string is for a parameterized query.

You should be able to remove all [] from the query, those are only needed when field names contain characters like a space in them.

Edit: I got this statement working for the time being:

Code:

---

ReDim s(1 To 8)
            s(1) = "SELECT Sum(Data.PBO) As SumOfPBO"
            s(2) = "Sum(Data.AccruedInterest) As SumOfAccruedInterest"
            s(3) = "Sum(IIf([Status]='Claims' Or [Status]='Forbearance'"
            s(4) = "[AccruedInterest],IIf([Status]='Repayment',0"
            s(5) = "IIf([SubsidyIndicator]='N',[AccruedInterest],0)))) As ITBC"
            s(6) = "Sum(IIf([ABI]=0,0,[pbo]/[ABI])) As Borr, Count(Data.BorrowerUniqueID) As Loans"
            s(7) = "Sum([PBO]*[RemainingRepaymentTerm])/Sum([PBO]) As RepayTerm"
            s(8) = "Sum([pbo]*[InterestRate])/Sum([PBO]) As IntRate FROM Data LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID HAVING (((Data.PBO)<>0));"
           
            sSQL = Join(s(), ",")

---

Try posting a screen snip of the SQL string from Access. Is x the same value? Looking at your string, I can see this problem: IntRateFrom. Access SQL may have put a new line character so those two lines should be:

Code:

---

...snip
x = x & "*InterestRate)/Sum(PBO) AS IntRate"  & vbCRLF
x = x & "FROM Data LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl"
..snip

---

Edit: By working, you mean the string is correct or it executes?

Yes, sorry for the edit, and thank you so much for the help. That string above executes perfectly. Although I cannot get this string to execute:

Code:

---

sSQL = ""
   
            ReDim s(1 To 3)
            s(1) = "SELECT Data_All.* INTO Data"
            s(2) = "FROM Data_All"
            s(3) = "WHERE (((Data_All.BondIssue)='AEFC-Citi'));"
         
            sSQL = Join(s(), ",")
   
        ActiveSheet.Range("B12") = "Table Data created."




        'Create RecordSet
        Set cn = New ADODB.Connection
        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Open MyConn
            Set rs = .Execute(sSQL) '<============= This does not work
        End With

---

Error:
Query input must contain at least one table or query

Is this because I am just creating a table and not displaying anything? I'm not exactly sure what that error means. Could this also be the table 'Data' already exists and I need to delete it first?

I tried taking off the part that prints the recordsheet and tried deleting the table manually before running it. The error is still:

Query input must contain at least one table or query

Is their no way to run this type of statement?

I doubt that it should be delimited by commas. Try vbCRLF.

Code:

---

sSQL = Join(s(), vbCRLF)

---

Thanks! I didn't realize that was a delimiter and I definitely didn't realize I could use a vbCRLF as a delimiter.

Works perfectly, you're the best!

How about this one:

Code:

---

sSQL = ""
        'This statement is too long and must be split up below
       
            ReDim s(1 To 9)
            s(1) = "SELECT tbl_ALLStatus.Status, Sum(Data.PBO) AS SumOfPBO,"
            s(2) = " Sum([PBO]/[ABI]) AS Borrs, Sum([PBO])/Sum([pbo]/[abi]) AS Borr_ABI, Count(Data.BorrowerUniqueID) AS Loans,"
            s(3) = " tbl_ALLStatus.StatusOrder, Sum(IIf([LoanTypeName]='Consolidation,[PBO],0)) AS Cons_PBO,"
            s(4) = " Sum(IIf([LoanTypeName]<>'Consolidation',[PBO],0)) AS NonCons_PBO"
            s(5) = "FROM ((Data LEFT JOIN tbl_ALLStatus ON Data.Status = tbl_ALLStatus.Status) LEFT JOIN tbl_ABI"
            s(6) = " ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID) LEFT JOIN tbl_ALLLoanTypes ON (Data.LoanType = tbl_ALLLoanTypes.LoanType) AND (Data.LoanProgram = tbl_ALLLoanTypes.LoanProgram)"
            s(7) = "WHERE (((Data.PBO)<>0))"
            s(8) = "GROUP BY tbl_ALLStatus.Status, tbl_ALLStatus.StatusOrder"
            s(9) = "ORDER BY tbl_ALLStatus.StatusOrder;"


            sSQL = Join(s(), vbCrLf)
            'This statement will display qry_Report3

---

Getting this Error:
Syntax...

It's large so I put it in the attachment

I really need to figure out how this delimiter works because I have to do 10 of these and I'm only on #3.

I don't know your SQL string that works so I would not know. I am guessing the "< >" should be "<>".

Here is the full string:

Code:

---

SELECT tbl_ALLStatus.Status, Sum(Data.PBO) AS SumOfPBO, Sum([PBO]/[ABI]) AS Borrs, Sum([PBO])/Sum([pbo]/[abi]) AS Borr_ABI, Count(Data.BorrowerUniqueID) AS Loans, tbl_ALLStatus.StatusOrder, Sum(IIf([LoanTypeName]="Consolidation",[PBO],0)) AS Cons_PBO, Sum(IIf([LoanTypeName]<>"Consolidation",[PBO],0)) AS NonCons_PBO
FROM ((Data LEFT JOIN tbl_ALLStatus ON Data.Status = tbl_ALLStatus.Status) LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID) LEFT JOIN tbl_ALLLoanTypes ON (Data.LoanType = tbl_ALLLoanTypes.LoanType) AND (Data.LoanProgram = tbl_ALLLoanTypes.LoanProgram)
WHERE (((Data.PBO)<>0))
GROUP BY tbl_ALLStatus.Status, tbl_ALLStatus.StatusOrder
ORDER BY tbl_ALLStatus.StatusOrder;

---

It obviously works in Access. And I can't quite find the "<>" bracket spacing your talking about.

I would break that into 2 arrays. The first would be line one and use the comma delimiter. Break that first line of SQL into several small parts for each element where the comma is left off to be readded by a Join().

The second would poke the first Join() string from array one into the first element of array two. Add the other lines of SQL. Use the vbCRLF delimiter for the Join() of array 2.

Quote:

---

Originally Posted by Kenneth Hobs

The second would poke the first Join() string from array one int

---

I'm not sure what you mean by that. I'm sure I'm way off here, but what about this:

Code:

---

'So big it needs a two parter
        Dim twoparter As String
       
            ReDim s(1 To 9)
            s(1) = "SELECT tbl_ALLStatus.Status,"
            s(2) = " Sum(Data.PBO) As SumOfPBO,"
            s(3) = " Sum([PBO]/[ABI]) As Borrs,"
            s(4) = " Sum([PBO])/Sum([pbo]/[abi]) As Borr_ABI,"
            s(5) = " Count(Data.BorrowerUniqueID) As Loans, tbl_ALLStatus.StatusOrder,"
            s(6) = " Sum(IIf([LoanTypeName]='Consolidation',"
            s(7) = "[PBO],0)) As Cons_PBO,"
            s(8) = " Sum(IIf([LoanTypeName]<>'Consolidation',"
            s(9) = "[PBO],0)) As NonCons_PBO "
           
            twoparter = Join(s(), ",")
           
            ReDim s(1 To 5)
            s(1) = "FROM ((Data LEFT JOIN tbl_ALLStatus ON Data.Status = tbl_ALLStatus.Status)"
            s(2) = "LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID) "
            s(3) = "LEFT JOIN tbl_ALLLoanTypes ON (Data.LoanType = tbl_ALLLoanTypes.LoanType)"
            s(4) = " And (Data.LoanProgram = tbl_ALLLoanTypes.LoanProgram) "
            s(5) = "WHERE (((Data.PBO)<>0));"
           
            twoparter = twoparter + Join(t(), vbCrLf)
           
            sSQL = twoparter
            'This statement will display qry_Report3

---

You left the comma at the end and then added another with the first Join(). Use & for string concatenation. Use + to add numbers.

Code:

---

Sub ken()
  Dim sSQL As String, s() As String
  ReDim s(1 To 8)
  s(1) = "SELECT tbl_ALLStatus.Status"
  s(2) = "Sum(Data.PBO) As SumOfPBO"
  s(3) = "Sum([PBO]/[ABI]) As Borrs"
  s(4) = "Sum([PBO])/Sum([pbo]/[abi]) As Borr_ABI"
  s(5) = "Count(Data.BorrowerUniqueID) As Loans"
  s(6) = "tbl_ALLStatus.StatusOrder"
  s(7) = "Sum(IIf([LoanTypeName]='Consolidation',[PBO],0)) As Cons_PBO"
  s(8) = "Sum(IIf([LoanTypeName] <> 'Consolidation', [PBO], 0)) As NonCons_PBO"
  sSQL = Join(s(), ",")
  ReDim s(1 To 5)
  s(1) = sSQL
  s(2) = "FROM ((Data LEFT JOIN tbl_ALLStatus ON Data.Status = tbl_ALLStatus.Status) LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID) LEFT JOIN tbl_ALLLoanTypes ON (Data.LoanType = tbl_ALLLoanTypes.LoanType) And (Data.LoanProgram = tbl_ALLLoanTypes.LoanProgram)"
  s(3) = "WHERE (((Data.PBO) <> 0))"
  s(4) = "GROUP BY tbl_ALLStatus.Status, tbl_ALLStatus.StatusOrder"
  s(5) = "ORDER BY tbl_ALLStatus.StatusOrder;"
  sSQL = Join(s(), vbCrLf)
  MsgBox sSQL
  Debug.Print sSQL
End Sub

---