none
How can I check for an ODBC error after a ....Refresh statement RRS feed

  • Question

  • I have some [simple] VBA code in Excel which attempts to refresh the data in a worksheet using an ODBC connection and SQL statement.

    The VBA code constructs the Connection string and also the SQL statement to run based on parameters entered by the user.

    This much is working perfectly:   and provided the file name which has been input by the user actually exists on the server, then the VBA refresh statement runs without any error.

    The problem is that when it fails (for example:  the file name input by the user does NOT exist on the server) then an ODBC error dialogue box appears.

    I read elsewhere the the dialogue boxes can be suppressed by the statement {Application.DisplayAlerts = False}.

    But then the error disappears completely and the ErrorHandler: is not triggered.

    What should I code following the Refresh statement in order to trap any error?

    Many thanks in advance.

    On Error GoTo ErrorHandler:
    
    ' Run the SQL statements as now established
    '
    Dim Conn As Variant
    Dim ConnString As String
    
    
    For Each Conn In ThisWorkbook.Connections
        
        ConnString = Conn
        
        Application.StatusBar = ("Please wait while the system imports data into tab " & ConnString & "  ...")
        
      
        Application.DisplayAlerts = False
        ThisWorkbook.Connections(ConnString).Refresh
    
    ' when the above statement fails after {Application.DisplayAlerts = False} then if the refresh is cancelled after an error the VBA code is unaware of this and continues on to the next statement - despite the {On Error....}.
    
    ' without the {Application.DisplayAlerts = False} then an error in the Refresh is signalled in a pop-up widnow.

    Wednesday, February 15, 2017 1:08 PM

All replies

  • Why not use the Dir function to check if the file exists before executing the Refresh method?

    https://www.techonthenet.com/excel/formulas/dir.php


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 15, 2017 1:58 PM
  • in this line 

    ThisWorkbook.Connections(ConnString).Refresh

    your ConnString should be a number. So loop by ActiveWorkbook.Connections.Count

    ThisWorkbook.Connections(1).Refresh

    So try this one

    Dim x%
    with ActiveWorkbook.Connections
        For x = 1 To .Count
            .Item(x).refresh
        Next
    end with


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Thursday, February 16, 2017 1:32 PM
    Answerer