PDA

View Full Version : Can Excel run this big SQL?



bobdole22
08-30-2013, 06:56 AM
Can Excel run this?


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:


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-excel-run-this-big-sql-1417/#post6658

raj85
08-30-2013, 07:09 AM
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

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

Kenneth Hobs
08-30-2013, 07:12 AM
Break your sSQL into parts. e.g.
sSQL = "Select * from Data"

When you build your SQL string, use:

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

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

bobdole22
08-30-2013, 07:21 AM
I have definitely been changing them to single quotes when I run it, but the debug idea is great! Thanks for the advice!

bobdole22
08-30-2013, 07:42 AM
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

Kenneth Hobs
08-30-2013, 07:55 AM
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.

bobdole22
08-30-2013, 08:02 AM
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.

Kenneth Hobs
08-30-2013, 08:38 AM
[]'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.

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


'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


'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

bobdole22
08-30-2013, 12:38 PM
Thank you for the reply! Great, It will take me a bit of deciphering, but that should be perfect. Thanks!

bobdole22
09-03-2013, 08:28 AM
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.

Kenneth Hobs
09-03-2013, 08:44 AM
[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.

Jan Karel Pieterse
09-03-2013, 08:58 AM
You should be able to remove all [] from the query, those are only needed when field names contain characters like a space in them.

bobdole22
09-03-2013, 09:25 AM
Edit: I got this statement working for the time being:


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(), ",")

Kenneth Hobs
09-03-2013, 09:33 AM
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:

...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?

bobdole22
09-03-2013, 09:41 AM
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:


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?

bobdole22
09-03-2013, 11:29 AM
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?

Kenneth Hobs
09-03-2013, 11:58 AM
I doubt that it should be delimited by commas. Try vbCRLF.

sSQL = Join(s(), vbCRLF)

bobdole22
09-03-2013, 12:16 PM
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!

bobdole22
09-03-2013, 01:46 PM
How about this one:


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.

Kenneth Hobs
09-03-2013, 02:08 PM
I don't know your SQL string that works so I would not know. I am guessing the "< >" should be "<>".

bobdole22
09-03-2013, 02:14 PM
Here is the full string:


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.

Kenneth Hobs
09-03-2013, 02:26 PM
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.

bobdole22
09-03-2013, 03:06 PM
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:


'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

Kenneth Hobs
09-03-2013, 04:01 PM
You left the comma at the end and then added another with the first Join(). Use & for string concatenation. Use + to add numbers.


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