Long Web Query Timeout By Web Server

[kwanm]

Hello All,

I seem to be timing out the server I'm running a rather long web query on. My database requires the web query to the site be run a little over 7500 times, however, I believe the site locks out my connection at around 5700 because it assumes its under a flood attack. Is there a solution around this? Perhaps a delay function in the code? Or an alternate code that will continually retry the web query until connectivity is re-established?

I've only attached the pertinent part of my code. Certain declarations of variables are stated in other parts of the code and thereby not included.

Any help you can offer is greatly appreciated. Thanks.

    Dim Firstrow As Long
    
    Firstrow = Range("J" & Rows.Count).End(xlUp).Row
    
    For m = Firstrow To Lastrow
    SEC = Cells(m, "c").Value
    BLK = Cells(m, "d").Value
    LOT = Cells(m, "f").Value
    CA = Cells(m, "g").Value
    UNIT = Cells(m, "h").Value
    Name = "SEC " & SEC & "BLK " & BLK & "LOT " & LOT & "CA " & CA & "UNIT " & UNIT
        If Len(CA) = 0 Then
            URLString = "URL;http://www.nassaucountyny.gov/mynassauproperty/inventory.jsp?txtSection=" & SEC & "&txtBlock=" & BLK & "&txtLot=" & LOT & "&txtSuffix=0&txtBldg=&txtCondo=&txtYear=2012"
        ElseIf Len(CA) = 1 Then
            URLString = "URL;http://www.nassaucountyny.gov/mynassauproperty/inventory.jsp?txtSection=" & SEC & "&txtBlock=" & BLK & "&txtLot=" & LOT & "&txtSuffix=U&txtBldg=CA000" & CA & "&txtCondo=" & UNIT & "&txtYear=2012"
        ElseIf Len(CA) = 2 Then
            URLString = "URL;http://www.nassaucountyny.gov/mynassauproperty/inventory.jsp?txtSection=" & SEC & "&txtBlock=" & BLK & "&txtLot=" & LOT & "&txtSuffix=U&txtBldg=CA00" & CA & "&txtCondo=" & UNIT & "&txtYear=2012"
        ElseIf Len(CA) = 3 Then
            URLString = "URL;http://www.nassaucountyny.gov/mynassauproperty/inventory.jsp?txtSection=" & SEC & "&txtBlock=" & BLK & "&txtLot=" & LOT & "&txtSuffix=U&txtBldg=CA0" & CA & "&txtCondo=" & UNIT & "&txtYear=2012"
        Else
            URLString = "URL;http://www.nassaucountyny.gov/mynassauproperty/inventory.jsp?txtSection=" & SEC & "&txtBlock=" & BLK & "&txtLot=" & LOT & "&txtSuffix=U&txtBldg=CA" & CA & "&txtCondo=" & UNIT & "&txtYear=2012"
        End If
        
        ActiveWorkbook.Worksheets.Add.Name = "Temp"
        With ActiveSheet.QueryTables.Add(Connection:=URLString, Destination:=Range("$A$1"))
            .Name = Name
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = True
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = False
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "1"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    
        Range("B4").Copy
        Sheets("NCTRE2009txsale For Database").Range("J" & m).PasteSpecial
        Application.DisplayAlerts = False
        Sheets("Temp").Delete
    
    Next m

 

[kwanm]

If anyone has any ideas, I would appreciate it. Thanks.