SQL query in VBA, code maxing out at 32,767 characters and QueryTableBuilder throwing up a 1004

[Milbourn]

Hi all,

So I have a chunk of SQL code in a macro that updates a table from a database. This code includes a function that takes a bunch of data from a sheet down a column and splits it up into quotes & comma separated values to use on a 'WHERE' statement.

With a small amount of data down this column this code works perfectly fine, however when we reach higher numbers (1.7k rows), it throws up a 1004 error and shuts down as I believe it gets too long to handle. Offending part below:

Sub PopulateTable(PointsofData As String)
SQLSTRING = [B]UNIMPORTANT SQL CODE HERE[/B]
SQLSTRING = SQLSTRING + “ WHERE DATA IN (“ + PointsofData + “) “
Call QueryTableBuilder("Sheet1", CStr(SQLString))
End Sub

I've looked into it and it looks like there is a limit of 32,677 on Query builder and the problems match what I've encountered. I have tried splitting the main chunk of SQL from the PointsofData part but it still maxes out at the character limit. I have changed all my Int values to Long but no luck there either. Is there anything else I should be doing or will I need to split the PointsofData function into two parts as well (this would be difficult, only a novice here!)

Here is the Query table builder code as well, any advice is appreciated!

Sub QueryTableBuilder(SheetName As String, SQLString As String) ' build a query table object on a given sheet using given SQL


    Sheets(SheetName).Visible = True ' make the sheet visible so we can refresh the query
    Sheets(SheetName).Select


    If con = "" Then con = "ODBC;" & CreateDBConnection() 'Database information

    ' remove any existing Query Tables in the sheet
    For Each Cn In Sheets(SheetName).QueryTables
        Cn.Delete
    Next Cn
    ' clear any existing data
    Sheets(SheetName).UsedRange.Clear
    ' add a new query table using the SQL and connection created above
    With Sheets(SheetName).QueryTables.Add(Connection:=con, _
            Destination:=Range("A1"), Sql:=SQLString).Refresh(False)
    End With
    ' disconnect the query 
    For Each Cn In Sheets(SheetName).QueryTables
        Cn.Delete
    Next Cn
End Sub

 

[ParamRay]

One solution is to have a disconnected table and use ADO to populate it. Then you can break the WHERE clause into chunks and append each chunk to the table.

 

[Milbourn]

Thanks for your response! Is there a guide on how to use disconnectd tables/ADO? Just looking into it now and there's a lot to go through!

 

[Milbourn]

Sorry, I'm having real trouble finding out how to do this, does anyone have a link or any other work arounds I could use?