Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2006
    Posts
    5

    Unanswered: How to run SQL statements longer than 32k Characters from Access VB?

    I'm using a couple loop statements to create dynamic nested SQL queries within a VB module of my Access database. However, when I have tried to run one of these SQL statements larger than ~32k (2^15) characters using DoCmd.RunSQL, I receive run-time error '2498':

    "An expression you entered is the wrong data type for one of the arguements."

    Is there another way to run longer SQL statements (of about half a million characters) out of an Access VB module's code?

  2. #2
    Join Date
    Apr 2004
    Posts
    50
    Why would you need a SQL statement that long? I've seen some long SQL statments, but nothing that big. Are you sure that you can't optimize your query?

  3. #3
    Join Date
    Sep 2006
    Posts
    5
    It includes about 700 nested SQL statements, trying to search for duplicates from a holistic view (in order for the duplicate to exist, all records from a particular keyword group must match on certain fields in a different keyword group; there are about 300 different groups, and the biggest groups have about 700 records).

    I could reduce the size some, but could not get it under 32k.

    Otherwise I'll have to create another solution, but I was hoping I could reuse this existing nested query solution with minor modifications.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That sounds ridiculous. I"m 99.9% sure you're trying to swat flies with a sledgehammer...

    If you're positive there's no way around it, why not roll it up in a view or sproc on sql server, that's what it's good at...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2004
    Posts
    50
    I agree. If you can't reduce the size of the query and in general optimize it, then move the whole thing to SQL Server. You'll get much better performance and get rid of the Access restrictions. I bet that it's taking you hours to execute your current query in Access.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would spew some venom here or ridicule this approach but someone here found out I know Access and VBA even though it is not on my resume anymore and the original version of our products were built out on that platform and we still have a few clients on that version.

    I have spent 2 1/2 weeks now diggging through some of the worst code you have ever seen and automating processes so we can get the last clients into the web app.
    Last edited by Thrasymachus; 10-18-06 at 17:11.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Apr 2004
    Posts
    50
    Most Access applications are terribly written and this is because the programmers who wrote them never bother to learn programming. I wouldn't touch MS Access application, unless my boss makes me do it.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While I'm not certain, I'd be willing to bet that I can write a query less than 1 Kb that can do it. I can't imagine a query so complex that I can't do it in inder 10 Kb.

    Can you provide a table definition (schema) and some sample data to demonstrate what you want to happen? If we can get away from "the way we do it now" and get to what you need to happen, I'm sure we can find a better way to help you get the job done.

    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    While I'm not certain, I'd be willing to bet that I can write a query less than 1 Kb that can do it. I can't imagine a query so complex that I can't do it in inder 10 Kb.
    Unless of course you are aiming for an NZDF solution...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Unless of course you are aiming for an NZDF solution...
    Oooooohhhhhh.... I hadn't thought of that! Great zot, opportunity awaits us! An NZDF compounded by steganography, what a concept!

    Thanks for the idea!

    -PatP

Posting Permissions

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