Using the IN Sql statement with many parameters causing runtime errror in VBA

  • Hi,


    I am trying to run a SQL statement through VBA to refresh a query in my Excel workbook.


    I am getting a Runtime Error 1004: Application-defined or Object-defined error.


    I think it is the IN() statement that is causing the error since I have over 4,000 parameters to look against.


    If I have about 3,500 parameters, then it works.


    these parameters are taken from a column in my spreadsheet.


    Are there limits to the IN() statement in SQL? If so, is there a workaround you can suggest?



    I collect the parameters from the Excel sheet into the vIDs variable, then I create a transposed list in the MethodIDs variable, the use that list for the IN() parameters.


    The error occurs at the highlighted line above.


    Note: IF I do a Debug.Print MethodIDs and copy the list directly into my Excel msQuery then it works fine. Only from the vba it doesn't.


    Thanks.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • With that number of values in the IN clause you are certainly hitting a limit somewhere - no idea where, but neither does Microsoft: See the 'Remarks' section; https://docs.microsoft.com/en-us/sql...ql-server-2017).


    As you can run it in MS Query I guess the additional overhead of VBA with the ODBC driver is causing it to run out of resources/memory or something...


    You could try splitting the IN clause like ...Where (x in (1, 2, 3...) OR x in (1001, 1002, 1003...) but the usual suggestion is to create a temporary table containing the values and just use Where x in ("Select [field] from TempTbl") ...

  • Hi XenoCode,


    Thanks for the reply, after some more searching I came across the method described here: https://stackoverflow.com/questions/.../337817#337817


    I implemented and it works. It's not the fastest but it does the job.


    If you can suggest how to revise my code to add and use temp table, then I can give that a try to see if it speeds things up. Note that I will need to drop and re-create table each time I run the code since I will always have a new set of data in column A.


    Thanks again.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • You can create a local temporary table by naming it with a '#' as the first character


    Code
    1. SQL = "Select * into #TempTbl From...


    No way of checking code right now so this is untested, but a simple example of using SQL to return a recordset from an Excel.range


    Code
    1. strFile = ThisWorkbook.FullName
    2. strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    3. Set cn = CreateObject("ADODB.Connection")
    4. Set rs = CreateObject("ADODB.Recordset")
    5. cn.Open strCon
    6. strSQL = "SELECT * FROM [Sheet1$A7:A" & GrandTotal - 1)
    7. rs...


    The temp table will be dropped when the connection that created it is closed.


    Rough and ready - not meant as a solution just something to think about.