Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Building Query, SQL windows

    From what I understand, the Access Query designer has some issues showing the designed query in SQL if the query is too complex? Longer than 1000 chars.

    Is there any way to get around this issue?

    I'm bug-hunting a query, and frankly, bughunting in GUI mode makes me dizzy, I prefer going through the code line by line.. but since I only get half the query in the SQL mode, I can't really do it.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What version are you using? I just pulled the SQL on one of mine and it's over 1100 characters long. The limit per Access Help is:

    Number of characters in an SQL statement approximately 64,000
    I don't do a lot of direct SQL stuff but some people here do. Perhaps if you could post what you have someone can help you debug it.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    I'm using 2003. But I just now noticed that the format of the mdb is 2000, so perhaps all I have to do is convert it, in order to get hold of the SQL code.... and then perhaps post it here for debugging if I can't find the error.

    Thanks for help so far..
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you have much in the way of derived tables, corrolated subqueries and the like? I have found those to be the source of problems in the past (though others on here don't and we never followed it through).

    Personally in MS SQL I prefer to view the SQL but in Access I prefer the design view (simply because of the limitations of the Access SQL view).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sometimes SQL-view chokes to death on messy queries at only a few hundred characters.

    a possible fix for some queries:

    if your query is anything more than SELECT and JOIN, and if you used
    tblX.fldA, tblX.fldB, tblX.fldC
    or
    [tblX].[fldA], [tblX].[fldB]
    and you get the "blah blah blah or too long" error.

    consider trying
    fldA, fldB, fldC (only if your query still makes sense, of course).

    i have one query with three UNIONs totalling 345 chars being "too long" with table.field and working fine without the table.(293 chars). swapping in/out the table. triggers/not the error message 100% reliably.

    no similar effect with simple SELECTs and JOINS - these seem to be OK for many thousands of chars (i just checked several examples over 2000 chars)

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Oct 2005
    Posts
    183
    Quote Originally Posted by pootle flump
    Do you have much in the way of derived tables, corrolated subqueries and the like? I have found those to be the source of problems in the past (though others on here don't and we never followed it through).

    Personally in MS SQL I prefer to view the SQL but in Access I prefer the design view (simply because of the limitations of the Access SQL view).
    Bingo! You're on to the issues. There are in fact Corrolated subqueries, and these are in fact complete when viewed in GUI mode, but in SQL view they look incomplete.

    Thanks Poot. Guess I'll have to do it the hard way!

    Cheers, Flix
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

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