SQL Command in .CommandText

[Paella1]

I am using a procedure to generate a list/table using an ODBC link and an SQL query. I do this a lot, generally with no problem.

However in this instance my SQL query is very long, and generates a Type Mismatch error at the .CommandText = Array(sql) line below:

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DRIVER=SQL Server;SERVER=myServer;UID=myUserName;PWD=myPassword;APP=Microsoft Office 2010;WSID=myWSID" Destination:=Range("$B$1")).QueryTable
        .CommandText = Array(sql)
        .Refresh BackgroundQuery:=False
    End With

I have found that this method will work when the string is up to 255 characters long, but as soon as the string length hits 256 characters a Type mismatch error occurs.

Is there a way around this?

 

[Paella1]

I said it would be a few days and it was five months, but I just got back to it now and it works a treat. Thanks Colin.