Dealing with VBA error 1004 when refreshing all data connections due to connection only queries

[dougmarkham]

Hi Folks,

I'm using Microsoft 365, so a current version of excel.

I'm having issues with the following code to refresh all the queries I have in my workbook:

Sub Refresh_All_Data_Connections()

Call TurnOffStuff

Dim objConnection As Object
Dim bBackground As Boolean

    For Each objConnection In ThisWorkbook.Connections
        'Get current background-refresh value
        On Error GoTo Err_Control
        bBackground = objConnection.OLEDBConnection.BackgroundQuery

        'Temporarily disable background-refresh
        On Error GoTo Err_Control
        objConnection.OLEDBConnection.BackgroundQuery = False

        'Refresh this connection
        On Error GoTo Err_Control
        objConnection.Refresh

        'Set background-refresh value back to original value
        On Error GoTo Err_Control
        objConnection.OLEDBConnection.BackgroundQuery = bBackground

    Next

    'MsgBox "Finished refreshing all data connections"

Err_Control:
    If Err.Number <> 0 Then
        If Err.Number = 1004 Then
            Resume Next
        Else
            MsgBox Err.Number & " - " & Err.Description
        End If
    End If

Call TurnOnStuff

End Sub

I have 3 normal queries and 3 connection only queries. The code works fine when it's refreshing the normal connections; however, when it comes to refresh the queries with load status of 'connection only', then the VB editor returns the error message: Run-time error '1004': Application-defined or ojbect-defined error i.e., despite their being error handling in the code.

Weirdly, this has just started happening after I've returned from holiday---i.e., that excel / outlook is ignoring instructions to move to error control or to resume next etc., indicating something has changed with Excel.

Does anyone know what's happening here and how to fix this? I have already unticked "Enable background refresh" in the properties of each query.

Kind regards,

Doug.

 

[Rileys98]

Hi Folks,

I'm using Microsoft 365, so a current version of excel.

I'm having issues with the following code to refresh all the queries I have in my workbook:

Sub Refresh_All_Data_Connections()

Call TurnOffStuff

Dim objConnection As Object
Dim bBackground As Boolean

    For Each objConnection In ThisWorkbook.Connections
        'Get current background-refresh value
        On Error GoTo Err_Control
        bBackground = objConnection.OLEDBConnection.BackgroundQuery

        'Temporarily disable background-refresh
        On Error GoTo Err_Control
        objConnection.OLEDBConnection.BackgroundQuery = False

        'Refresh this connection
        On Error GoTo Err_Control
        objConnection.Refresh

        'Set background-refresh value back to original value
        On Error GoTo Err_Control
        objConnection.OLEDBConnection.BackgroundQuery = bBackground

    Next

    'MsgBox "Finished refreshing all data connections"

Err_Control:
    If Err.Number <> 0 Then
        If Err.Number = 1004 Then
            Resume Next
        Else
            MsgBox Err.Number & " - " & Err.Description
        End If
    End If

Call TurnOnStuff

End Sub

I have 3 normal queries and 3 connection only queries. The code works fine when it's refreshing the normal connections; however, when it comes to refresh the queries with load status of 'connection only', then the VB editor returns the error message: Run-time error '1004': Application-defined or ojbect-defined error i.e., despite their being error handling in the code.

Weirdly, this has just started happening after I've returned from holiday---i.e., that excel / outlook is ignoring instructions to move to error control or to resume next etc., indicating something has changed with Excel.

Does anyone know what's happening here and how to fix this? I have already unticked "Enable background refresh" in the properties of each query.

Kind regards,

Doug.

Greetings, What I do for those error codes I don't want to see and have no impact:
On Error GoTo 100

Then put 100 at bottom of your code just before end

 

[dougmarkham]

Greetings, What I do for those error codes I don't want to see and have no impact:
On Error GoTo 100

Then put 100 at bottom of your code just before end

Hi Rileys98,

Yeah, I sometimes use a similar thing, and I get the same issue using that method too.

To clarify what's occurring here: the For loop goes around fine for 3 queries that are writing the data to a worksheet table; however, when the For loop gets to the connection only queries, no matter which error code is used (On Error Resume Next, On Error GoTo 100, etc.), the 1004 error is coming up. Also, I think that the error code works for the first connection only query, then subsequently fails on the second and third connection only query! That would seem to be a key issue.

I'm looking for a way to overcome this issue where the second occurence of an error within a loop causes the error regardless.

I assume it has something to do with the fact that the first instance of "On Error....do this", the code works once, but on the next For loop cycle, it's not able to refer to previous instruction.
The honest truth is, I'm not that knowledeable using error code. I tend to try and code to get around errors rather than handle them.

The only reason I'm asking this time is because I've had a number of recent examples of this where error code in an 'While-' or 'Do-' or 'For-loop' stop working after the first pass in which an error is generated.

Kind regards,

Doug.

 

[dougmarkham]

Hi Rileys98,

I think I've found out what my problem has been caused by: something I'd never come across before which I found using a different Google search term.

Here, in the Tools>>Options-->Error Trapping, my IT dept. had set the default to Break on All Errors, when it should be set to Break on Unhandled Errors.

This answer was by shahkalpesh found here: VBA Error Handling not working in Excel

 

[Fluff]

when it should be set to Break on Unhandled Errors.

I would recommend using Break in Class Module, especially if you use userforms

 

[dougmarkham]

I would recommend using Break in Class Module, especially if you use userforms

Hi Fluff,

Thanks for the suggestion. Why is that a better choice?

 

[Fluff]

If an unhandled error occurs when loading a userform it will show you where the error occurs when you click debug. If you set it on Unhandled errors it will just take you the line of code that launches the userform.

 

[dougmarkham]

If an unhandled error occurs when loading a userform it will show you where the error occurs when you click debug. If you set it on Unhandled errors it will just take you the line of code that launches the userform.

Thanks for explaining, much appreciated!

 

[Fluff]

You're welcome.