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?
- 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
- .[COLOR=#FF0000]Sql = sSql[/COLOR]
- .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.