0

I am using the following function to perform a Teradata query from EXCEL 2007:

    Function Get_Query_Results(Rng As Range, Location As String, var As String, UID As String, PWD As String) As Long
    On Error GoTo TroubleWithTeradata
        Rng.Select
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=Server12;UID=" & UID & ";PWD=" & PWD & ";", Destination:=Rng).QueryTable
            .CommandText = var
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
        End With
        Get_Query_Results = LastInCol(Columns(Location)) - 1
        Exit Function
    TroubleWithTeradata:
        Get_Query_Results = -1
    End Function

This query puts its results in a region whose "North West" corner is some cell specified by Rng, and determines how many records were returned by getting the row number of the last record in column number Location, which is what the function LastInCol (not listed here) returns. If the query doesn't fail but returns no records (i.e., only a header row), the number of records returned is 0. If the function does indeed fail, the number of records returned is -1.

The text of the Teradata query itself is contained in string var. And herein lies the problem. I have been using this function successfully for years. But now I need to build a new query that makes the variable var exceed the EXCEL VBA limit of 32767 characters.

I really am not sure what limits are being exceeded here. Certainly that of the length of the variable var, but there's also what the QueryTable parameter .CommandText can contain.

How can I get around these limits?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Bruno Repetto
  • 11
  • 5
  • Where/how are you getting `var`? There's no VBA (string) limit of 32k characters, but there is I think a limit to the number of characters you can place in a cell. – Tim Williams Feb 22 '19 at 23:22
  • I build it elsewhere and pass it to the function as argument. – Bruno Repetto Feb 22 '19 at 23:23
  • Previously: https://stackoverflow.com/questions/49254681/is-there-a-workaround-for-the-maximum-length-of-an-odbcconnection-commandtext-st or https://stackoverflow.com/questions/26360651/odbc-connection-command-text – Tim Williams Feb 22 '19 at 23:26
  • You are right. I just verified that the size of the variable var is not being exceed. Its current length is 43432. That strongly indicates that .CommandText has a limit that is being exceeded. – Bruno Repetto Feb 22 '19 at 23:28
  • Yes - seems like its ~32k – Tim Williams Feb 22 '19 at 23:28
  • Jeez... I did search for this and somehow didn't find it. Thanks for the links to similar questions. I'll study them. – Bruno Repetto Feb 22 '19 at 23:29

0 Answers0