Run SQL In MS-Access From Excel VBA

[bobdole22]

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: Can Excel run this big SQL? (http://www.vbaexpress.com/forum/showthread.php?47370-Can-Excel-run-this-big-SQL&p=296264#post296264)

[Excel Fox]

Post the database on a fileshare site, and only then can the SQL statement be validated. Generally speaking though, Excel can run even larger SQL queries.

By the way, use proper thread titles. Can excel run this big SQL is not a thread title that one would expect when posting a new thread. And it certainly isn't a search friendly title either. You have been using the forums for a few days now and should be aware of the guidelines. If you do not conform, your posting privileges may be temporarily or permanently revoked.

Thanks for understanding.

[bobdole22]

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

[ashu1990]

this is because you are using some of the reserved words of acess DB try checking the DB titles or the words which are used in the SQL statement

[alansidman]

Look at this listing and see if you need to change and field or table names

Access 2007 reserved words and symbols - Access - Office.com (http://office.microsoft.com/en-us/access-help/access-2007-reserved-words-and-symbols-HA010030643.aspx)