none
Create Query table with long SQL syntax RRS feed

  • Question

  • Hello,

    I have a code that creates large SQL syntax. I get error 1004 when the SQL syntax is too long.

    How can I solve it??

    The code builds the SQL syntax into variable named SQLCommand (I tried both string and variant with the same results)

    Like this:

    SQLCommand = "WITH" & vbCrLf
    SQLCommand = SQLCommand & "    MASTER" & vbCrLf
    SQLCommand = SQLCommand & "    AS" & vbNewLine
    SQLCommand = SQLCommand & "    (" & vbNewLine ...............

    and so on then I call the following procedure:

    Sub BuildQuery(TheSQLCommand As String)

    With Sheet2
        .Cells.Clear
        .Activate
    End With


    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=QGPL;", _
        Destination:=Range("$A$1")).QueryTable
        .CommandText = TheSQLCommand
        .Refresh BackgroundQuery:=False
    End With

    End Sub

    when I am pasting manually the code into the command text window it's working.

    The problem is how to pass the variable to the command text.


    Guy Zommer

    Wednesday, December 3, 2014 10:18 PM

Answers

All replies

  • Hi Guy Zommer,

    Which line of code is causing this error?

    From the description, it seem the text of SQL command is incorrect.

    >>The problem is how to pass the variable to the command text.<<

    The QueryTable.CommandText is a string, you can set its value via pass any string variable.

    I suggest that you use debug.print to print the command text and paste the code into command text window to see whether the command text is incorrect.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 5, 2014 2:12 AM
    Moderator
  • Thanks for the answer

    The debug.print is limited and present only part of the SQLCommand string. Anyway the problem is not in the syntax because when I tried to transfer the SQLCommand string to text file and then manually put it in the command text it worked!!!

    What i did is:

    Call ExportToText(SQLCommand)

           

    Sub ExportToText(SQLCommand As String)

    Dim myFile As String

    myFile = "C:\Users\guyz\Desktop\test.txt"
    Open myFile For Output As #1

    Write #1, SQLCommand


    Close #1

    End Sud

    Maybe there a Programmable way to transfer from text file to the command text??


    Guy Zommer

    Friday, December 5, 2014 7:35 AM
  • Hi,

    The debug error is run time error 1004 on the .CommandText = TheSQLCommand and it happens only when the SQL syntax is too long


    Guy Zommer

    Saturday, December 6, 2014 5:48 PM
  • Hi Guy Zommer,

    Hi,

    The debug error is run time error 1004 on the .CommandText = TheSQLCommand and it happens only when the SQL syntax is too long


    Guy Zommer

    Thanks for the detail information about this issue. What's version of Excel are you using? I am not able to find the document about this limitation.

    I found a knowledge base which my be relative this issue:

    http://support.microsoft.com/kb/213841

    And I suggest that you try to split the string into muiple cells and combine the SQL like code bleow:

    SQLCommand =SQLCommand & Range("A1")
    SQLCommand =SQLCommand & Range("A2")
    ...

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, December 8, 2014 11:35 AM
    Moderator
  • Just remove the vbcrlf and vbnewline from the string creation process.

    Those are not needed, nor appropriate.

    Tip: Use SQL Pretty Printer if you ever need to format your string SQL.

    Monday, December 8, 2014 9:51 PM