none
SQL macro with a String of IDs and get Run-Time error '1004' RRS feed

  • Question

  • I am trying to pull data from SQL for a list of ID and I get Run-Time error '1004': Application-defined or object-defined error. 

    I know it is because the list of IDs along with the SQL surpass the number of characters.

    I think the best way to get around this is to loop the SQL,  but I can't figure out the best way to do that?

    There really isn't a way for me to use SQL to define the population since I am being provided a list. I copied a sample of my SQL here, but removed proprietary field names. 

    I am brand new to VBA and have no formal training so any help would be appreciated.

    Sub Refresh_ptp_data()

    Dim SQL As String
      
    Dim rngusers As Range: Set rngusers = Sheets("IDs").Range("SysID_list")
    Dim sqlUsers As Variant: sqlUsers = ConcatenateRange(rngusers)

        Application.ScreenUpdating = False

    SQL = "select ID, stat,  type, action, effectivedate from table_X where ID in ( " & sqlUsers & " )"

        With Worksheets("All ").QueryTables("His")
            .SQL = SQL
            .AdjustColumnWidth = False
            .BackgroundQuery = False
            .RefreshStyle = xlOverwriteCells
            .Refresh
            If .FetchedRowOverflow Then
                MsgBox "There are too many person data records."
            End If
        End With


    Wednesday, April 26, 2017 6:35 PM