VBA Type Mismatch Error on passing long String to SQL

[neb255]

Hi,

i have the below macro which takes a range of cells in a single column and concatenates the values into a single string. The string is then passed via an ODBC connection to SQL to be used as the variables the "Where" statement.

My problem seems to be related to how long the string i am trying to pass is. The set of values which i am trying to pass is: (These are the values referenced in the macro for Column A on Sheets("Accounts"))

Z00007528-USD

Z00007882-EUR

BOARDINTUSD

C&SWHOLESALEUSD

CGIINCUSD

CHOUETUSD

DOUBLEROCKUSD

DTELLCUSD

INCEPTYLTDZAR

MEADOWBRKUSD

NIGERIALNGUSD

ONEROCKCAPUSD

OPXBUSD

SITELUSD

STCLOUDCAPUSD

Z00001411-USD

Z00002486-USD

Z00002494-USD

Z00002661-USD

Z00003279-USD

<tbody>
</tbody>

however when i try to run the macro i get "Run Error 13':, Type Mismatch"

Without changing anything in the macro, if i cut the number of values down so that the total string length is 140 or below then it runs fine, ie this set:

Z00007528-USD

Z00007882-EUR

BOARDINTUSD

C&SWHOLESALEUSD

CGIINCUSD

CHOUETUSD

DOUBLEROCKUSD

DTELLCUSD

INCEPTYLTDZAR

<tbody>
</tbody>

I dont know if this is something in how i wrote my macro or if its a SQL limitation.

any help is greatly appreciated
Cheers
-Ben

Dim lasta As Long
Dim all As String
Dim cell As Range
    
lasta = Sheets("Accounts").Range("a" & Rows.Count).End(xlUp).Row
    

  For Each cell In Sheets("Accounts").Range("A1:A" & lasta)
        all = all & cell.Value & "','"
    Next cell

all = Left(all, Len(all) - 3)

Sheets("accounts").Range("c5").Value = all

    With Sheets("ARData").ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DRIVER=SQL Server;SERVER=NYCVMBUS007;UID=;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=Staging;Net" _
        ), Array("work=DBMSSOCN")), Destination:=Range("$A$10")).QueryTable
          .CommandText = Array( _
        "SELECT VW_AR_COLLECTIONS.""AR Rep"",VW_AR_COLLECTIONS.Category, VW_AR_COLLECTIONS.""GP ID"",", _
        "VW_AR_COLLECTIONS.""CUSTOMER NAME"",VW_AR_COLLECTIONS.""BILLING CONTACT"", VW_AR_COLLECTIONS.CCode,VW_AR_COLLECTIONS.""TOTAL DUE $"",", _
        " VW_AR_COLLECTIONS.INET1,VW_AR_COLLECTIONS.INET2, VW_AR_COLLECTIONS.CURRENCY,VW_AR_COLLECTIONS.""OPEN BALANCE"",", _
        "VW_AR_COLLECTIONS.INV" & Chr(13) & "" & Chr(10) & _
        "FROM Staging.dbo.VW_AR_COLLECTIONS VW_AR_COLLECTIONS" & Chr(13) & "" & Chr(10) & _
        "WHERE (VW_AR_COLLECTIONS.""GP ID"" in ('" & all & "'))")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Accounts"
        .Refresh BackgroundQuery:=False
    End With