ADO SQL query in VB6 (sp6) too long! Truncated while executed.

Posted on 2005-04-20
Last Modified: 2008-06-18
Hi experts!
I am in a middle of a project where I access through ADO to a MS Access database (later after testing phase to MS SQL server).
Untill now everything was just fine, but now I experienced problems in on of my procedures, where I exectute quite a large, complex SELECT command which returns then a simple recordset with just two fields. The query has heaps of joins and tables connected.

The query is not over 64 k long which I thought is the limit. It is some 700 - 1000 chars long.

Now when I execute it, I get nothing in the recordset as the query somehow is truncated within this procedure. (when I debug the mySQL which is the argument for the execute it is not complete!!!!)

I tried to delete some WHERE conditions, shortened the query a bit and it worked again. But I need all those conditions.

I do not want to go to the database designer and rename all tables and fields so that they have just 2-3 characters as it will loose its logic for later programmer. Is there some other way how to get rid of this misbehaviour or am I missing some important declaration issue?

Or is it just MS access issue? Shall I switch directly to MS SQL? I am missing the wysiwyg query analyser there... :o)

Please help!

Thanks Petr
Question by:keson
    LVL 10

    Accepted Solution

    One thing you could do is to put the SQL into a query (Stored Procedure/View) in the database, or combine several of your joined tables in views and then make use of the views.

    Author Comment

    Hi PSSUser!
    So do I read you well that IT IS A STANDART BEHAVIOUR?
    I am now thinking about renaming all the tables and fields to something short to test it.
    The thing with stored procedures (views) is that I need to set a number of paramaters and the way of passing those parameters to the stored procedure is somehow odd... Anyway if there is some REAL sollution I will appreciate it!

    LVL 12

    Assisted Solution

    not sure if this directly addresses your problem but if you are moving to sql anyway, why not start with MSDE? It's free and this would allow you to code exactly as you will need to for sql and allow you to create views and stored procedures where you need to.
    You can still create queries in Access and paste them into sql with little changes. Also long select commands like you describe can be done in several parts of a stored procedure and combined as a batch file.

    check it out at

    LVL 10

    Expert Comment

    I don't know about a standard behaviour, I've never tried to use very long query strings. I prefer to use stored procedures, since the are optimised and also can make debugging problems easier - it helps if you can run the exact query outside of the software. If you need help with using ADO Command objects to run stored procedures, let me know.

    By your comment about using sored procedures being "somehow odd" do you mean the problem a lot of people have with parameters having to be passed in order? If so the trick is to set the NamedParameters of your command object to true. This allows you to pass them by name instead.(provided you are using ADO 2.6 or later)
    LVL 6

    Assisted Solution

    how do you know your sql is being truncated? i've run query strings with vb/access that are over 2kb and no problems.. can you post the code that you use to:

    build the sql
    print it when you are debugging.. i.e. how you know it is truncated?

    Author Comment


    thanks for all your posts. I have found out, that there is a certain 255 chars limit for ADODB.Recordset source. Don't ask me why. I talked to our VB developers and they told me that it somehow corresponds with command line limit of 255 characters.

    So I did use stored procedures (or views) and now the thing works. But I HAD TO MAKE MY TABLES and FIELDS names shorter anyway as I wanted to pass some 8-10 parameters to the stored procedure and they are heading to 200 characters with all necesary formating etc.

    Anyway, I know now, that I have to transmit as little code as possible and I will work further.

    Thank you all. P.S. CJARD: I see the truncated sql query in debuger where it shows only 255 chars. And also the query results do come with errors as the query was truncated just in the middle of the name or so.
    LVL 10

    Expert Comment


    you could change the way you are calling your stored procedure.

    If you make use of a command object you can add parameters that you set the values to, instead of putting the full call to the stored procedure in an SQL string.

    For Example set up the command object:
    With AdoCmd
      .ActiveConnection = AdoCn
      .CommandType = adCmdStoredProc
      .CommandText = "[Stored Proc Name]"
      .NamedParameters = True 'Make sure you don't have pass the parameters in order

      .Parameters.Append .CreateParameter("[@Param1]", adVarChar, adParamInput, 30)'VarChar length=30
      .Parameters.Append .CreateParameter("[@Param2]", adInteger, adParamInput)
    End With

    Then open the recordset using:
    AdoCmd.Parameters("[@Param1]").Value="Some String"

    Set AdoRs=New ADODB.RecordSet
    AdoRs.Open AdoCmd, ,adOpenForwardOnly,adLockOptimistic

    This way you won't have an issue with the SQL string length, so won't need to rename your tables/columns

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now