Results 1 to 5 of 5

Thread: Run SQL In MS-Access From Excel VBA

  1. #1
    Junior Member bobdole22's Avatar
    Join Date
    Aug 2013
    Posts
    7
    Rep Power
    0

    Run SQL In MS-Access From Excel VBA

    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: Can Excel run this big SQL?
    "My common sense is tingling!" - Deadpool
    For more: http://marveldcforum.com

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    Last edited by Excel Fox; 08-30-2013 at 07:33 PM. Reason: Proper thread title required
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member bobdole22's Avatar
    Join Date
    Aug 2013
    Posts
    7
    Rep Power
    0
    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
    "My common sense is tingling!" - Deadpool
    For more: http://marveldcforum.com

  4. #4
    Junior Member
    Join Date
    Mar 2013
    Posts
    20
    Rep Power
    0
    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

  5. #5
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    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

Similar Threads

  1. Replies: 6
    Last Post: 09-03-2019, 10:26 AM
  2. Make Table SQL Query In Access That Adds A Primary Key
    By Portucale in forum Access Help
    Replies: 5
    Last Post: 08-08-2013, 10:02 PM
  3. Replies: 8
    Last Post: 08-08-2013, 03:52 AM
  4. Replies: 1
    Last Post: 06-18-2013, 07:46 AM
  5. Execute SQL From Excel (VBA)
    By Mechanic in forum Excel and VBA Tips and Tricks
    Replies: 8
    Last Post: 10-02-2011, 04:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •