Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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?

    Code:
    With Sheets("Summary_Sales")
        GrandTotal = Application.WorksheetFunction.Match("Grand Total", .Range("A:A"), 0)
        vIDs = .Range("A7:A" & GrandTotal - 1).Value ' Get Method IDs to query from the Summary Sales sheet
    End With
    
     'Create string of Method ID to query
    MethodIDs = "'" & Join(Application.WorksheetFunction.Transpose(vIDs), "','") & "'"
    ' Refresh Routings query using only methods in Summary Sales sheet
    sConn = "ODBC;DRIVER=SQL Server;SERVER=ERP-M1;UID=myname;pwd=mypwd;Trusted_Connection=No"
    sSql = "SELECT PartOperations.imoMethodID, Parts.impShortDescription, PartOperations.imoMethodAssemblyID  "
    sSql = sSql & "FROM M1_HB.dbo.PartOperations PartOperations, M1_HB.dbo.Parts Parts "
    sSql = sSql & "WHERE PartOperations.imoMethodID = Parts.impPartID And PartOperations.imoMethodAssemblyID = 0 and RTrim(PartOperations.imoMethodID) In (" & MethodIDs & ") "
    sSql = sSql & "ORDER BY PartOperations.imoMethodID, PartOperations.imoMethodAssemblyID, PartOperations.imoMethodOperationID "
    With Sheets("Routings").Range("A1").ListObject.QueryTable
            .Connection = sConn
            .Sql = sSql
            .Refresh BackgroundQuery:=False
    End With
    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.
    Last edited by NBVC; June 22nd, 2018, 05:33.
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

  • #2
    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") ...

    Comment


    • #3
      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

      Comment


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

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

        Comment


        • #5


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

          MS Excel MVP 2010-2016

          Comment

          Working...
          X